This post features code I came up with to copy an xlsm to an xlsx. It has a few characteristics:
- The code lives in the “master” workbook, i.e., the one that’s copied. It’s not in an addin.
- The copy is an xlsx, stripped of any ribbon menus or VBA,
- Tables in the master workbook are disconnected from any external data sources.
- Any pivot tables pointing at tables in the master workbook are now pointing at their newly created copies in the copied workbook.
- The copied workbook and master workbook are both still open after the code runs.
I looked at a few options when designing this system.
Creating a Workbook Copy
The most attractive choice for saving a copy of a workbook would seem to be the nicely named SaveCopyAs function, which keeps the master workbook open while saving a copy where you specify. Unfortunately, it won’t let you save in another format, so can’t be used to save an xlsm as an xlsx.
The second choice would be the SaveAs function, which does allow you to save in different formats. However, when you do the master workbook closes and the VBA stops running. Not impossible to work around, but I don’t like it.
Probably the best choice, at least in theory, is to run the process from an addin. Such an addin has application-level code to check whether you open any master workbooks. When you do, the ribbon menu is activated, with a button for copying the master. Since all the code is in the addin, the master workbook can be an xlsx and you can use SaveCopyAs. I’ve done a number of projects like this and they lend themselves to better coding practices, such as separating the presentation (pivot tables) from the code and the data. However, my project had just one user and the data sources are all external, so it’s simpler and quite maintainable to give them a workbook with both code and pivot tables. I hope.
So, what I’m actually using is ThisWorkbook.Sheets.Copy, which copies all the sheets. It has a few advantages. Since it’s only copying sheets the only code that gets copied would be in the ThisWorkbook or worksheet modules. I don’t have any so it’s not an issue. (The code would also get deleted when the workbook is saved as an xlsx, but I’m not sure if the user would be prompted about that when they close it). Likewise the ribbon tab, which in included in its own folder in the zip file that constitutes an xlsx or xlsm doesn’t get transferred.
There is one big issue with this method: since we’re copying individual sheets, albeit all of them all at once, any references to other worksheets still point at those worksheets in the master workbook. They don’t automatically transfer over to the new copies. In my case the only references to other sheets are pivot table sources – all other data is external. So I needed a way to point the pivot tables at their respective tables in the new workbook.
Fixing Pivot Table Data Sources
Again the the most appealing method, the pivot table’s ChangeConnection property, won’t work. It’s only for external connections, such as to a SQL Server database or web page. It doesn’t work for pivots connected to tables in the workbook.
My next idea was to modify the SourceData property for each PivotCache in the new workbook. According to Excel 2010 help, this is a read/write property, so it seems pretty straightforward to alter. After several attempts and some web searching I discovered it only works for pivot caches used by only one pivot table. If more than one pivot table points at a cache, PivotCache.SourceData isn’t your friend.
Happily, pivot tables also have a SourceData property. But, of course, there’s a catch here too. if you set two pivot tables’ SourceProperty to the exact same range, two pivot caches will be created. I want as few pivot caches as possible in a workbook, one for each distinct range.
So I came up with code that loops through each pivot table in the new workbook. First it calculates the string for the corrected data source, i.e., the external one with the workbook part stripped away. For example, if we remove the workbook part, e.g., “Master.xlsm”, from “Master.xlsm!tblPivotSource”, we get “tblPivotSource” which we can use to point at the correct table in the copied workbook.
As the code loops through the pivot tables it does one of two things:
- It sets the pivot table’s SourceData to the newly calculated NewSourceData variable. It only does this for the first pivot table with that source. Setting the SourceData creates a new pivot cache that uses the same SourceData.
- In each loop it first checks if there’s already a pivot cache with that source, which will be true if step 1 has already happened. If that’s the case, I set the pivot’s CacheIndex property to the index of that cache.
(Note that steps 1 and 2 happen in reverse order in the code, it’s just easier to describe them in this order.)
One very nice thing is that if a pivot cache no longer has any pivot tables pointing at it, that cache is automatically deleted.
The end result is that the copied workbook now has the same number of pivot caches as it started out with, each pointing at a table within the copied workbook. As mentioned earlier the listobjecs are also unhooked from their external connections.
Without further ado:
Dim wbWorkbookCopy As Excel.Workbook
Dim WorkbookCopyName As String
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
Dim pvt As Excel.PivotTable
Dim pvtCache As Excel.PivotCache
Dim NewSourceData As String
Const SUBFOLDER_NAME As String = "Copied_Workbooks"
'Copies all worksheets, but not VBA or Ribbon
ThisWorkbook.Sheets.Copy
Set wbWorkbookCopy = ActiveWorkbook
With wbWorkbookCopy
For Each ws In .Worksheets
'Delete all listobject connections
For Each lo In ws.ListObjects
lo.Unlink
Next lo
'the pivot table caches are still pointing at ThisWorkbook, so
'point them at wbWorkbookCopy
For Each pvt In ws.PivotTables
'note that the "!" is the delimiter between a workbook and table
NewSourceData = Mid(pvt.SourceData, InStr(pvt.SourceData, "!") + 1)
'if we just set the SourceData property we get a new cache for each sheet
For Each pvtCache In wbWorkbookCopy.PivotCaches
'if a previous loop has already re-pointed a pivot table,
'then a new PivotCache with that SourceData has been created,
'so just set the pivot table's cache to that
If pvtCache.SourceData = NewSourceData Then
pvt.CacheIndex = pvtCache.Index
Else
pvt.SourceData = NewSourceData
End If
Next pvtCache
Next pvt
'apparently PivotCaches are automatically deleted if no pivot tables are pointing at them
Next ws
If Not SubFolderExists(ThisWorkbook.Path & Application.PathSeparator & SUBFOLDER_NAME) Then
MakeSubFolder ThisWorkbook.Path & Application.PathSeparator & SUBFOLDER_NAME
End If
WorkbookCopyName = Replace(ThisWorkbook.Name, ".xlsm", "") & "_copy_" & Format(Now(), "yyyy_mm_dd_hh_mm_ss") & ".xlsx"
.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & SUBFOLDER_NAME & _
Application.PathSeparator & WorkbookCopyName, FileFormat:=51
End With
End Sub
For many useful functions involving pivot caches, please visit this wonderful Contextures page.
Hi Doug. Can you post your SubFolderExists function too?
oh, and your MakeSubFolder funciton too?
Jeff, here they are in all their glory:
If Len(Dir(FolderFullName, vbDirectory)) > 0 Then
SubFolderExists = True
End If
End Function
Sub MakeSubFolder(ByVal FolderFullName As String)
FileSystem.MkDir (FolderFullName)
End Sub
The second choice would be the SaveAs function, which does allow you to save in different formats. However, when you do the master workbook closes and the VBA stops running. Not impossible to work around, but I don’t like it.
I wonder if you can save a copy in the same file format as the master, and then do the changes on the copy? Haven’t tried it.
Again the the most appealing method, the pivot table’s ChangePivotSource property, won’t work. It’s only for external connections, such as to a SQL Server database or web page. It doesn’t work for pivots connected to tables in the workbook..
I don’t get a .ChangePivotSource coming up in intellisense. I do get a .ChangePivotCache property, and when I recorded a macro and switched two pivots from source A to source B, I got just the one PivotCache as a result. Here’s what the macro recorder spat out:
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table1", Version _
:=xlPivotTableVersion15)
Range("P26").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ("PivotTable2")
Jeff, woops, I meant ChangeConnection. Thanks for catching that.
Re: the ChangePivotCache, I think I messed with that, but I can’t remember why I didn’t use it. I’ll have to look at it again.
Regarding your first thought of saving the copy in xlsm format, it seems like I’d still have to solve the same problems with the copy, but maybe not.
So many ways to skin these cats!
So many ways to skin these cats!
Don’t let the Society for the Protection of Metaphorical Cats hear you say that.
“There is one big issue with this method: since we’re copying individual sheets, albeit all of them all at once, any references to other worksheets still point at those worksheets in the master workbook. They don’t automatically transfer over to the new copies.”
Normally, I make all my changes to the master first, then save it. As a last step I move all the sheets to a new book. All internal links follow that way. The key is to not save the master after that point. Can you close the master and re-open it using code within the master?
Since most of my sheets have tables in them, I’m stuck with moving one sheet at a time. I’ll have to see whether doing it by code will allow bulk moves.
Your code is a big help to me. I like the idea of deleting the connections by code. I’ve been doing that manually, but that means it’s not a process I can pass on to someone else.
Thanks Omar!
Closing the master and re-opening it from code within the master is hard, to say the least. Once it’s closed the code stops running and you’d have to somehow first call code stored somewhere else, at which point you might as well use an addin.
I think :).
Doug: Back again.
Re The most attractive choice for saving a copy of a workbook would seem to be the nicely named SaveCopyAs function, which keeps the master workbook open while saving a copy where you specify. Unfortunately, it won’t let you save in another format, so can’t be used to save an xlsm as an xlsx.
So I just save the copy as an xlsm, reopen it, then save it as an xlsx. Seems to work fine.
Something like this:
'Saves a copy of this file as an .xlsx
Dim wbTemplate As Workbook
Dim strTemplateName As String
Dim strDisplaymode As String
Dim strMsg As String
Dim bScreenUpdating As Boolean
Dim bEnableEvents As Boolean
Dim lngCalculation As Long
On Error GoTo errHandler
With Application
bScreenUpdating = .ScreenUpdating
bEnableEvents = .EnableEvents
lngCalculation = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
strTemplateName = "Competency Assessment Template"
strDisplaymode = [app_DisplayMode]
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
ClearAndReset
DisplayMode "User" 'Hides all but the input sheets
ThisWorkbook.SaveCopyAs strTemplateName & ".xlsm"
DisplayMode strDisplaymode 'Restore the display mode the user was in
Set wbTemplate = Application.Workbooks.Open(Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "")) & strTemplateName & ".xlsm")
With wbTemplate
.Unprotect gsPassword
With wbTemplate.Worksheets(wksGroupView.Name)
.Visible = xlSheetVisible
Application.DisplayAlerts = False
.Delete
End With
.Protect Password:=gsPassword
.SaveAs Filename:=strTemplateName & ".xlsx", FileFormat:=51
.Close
End With
strMsg = "Great, I've saved a new template to " & Application.DefaultFilePath
MsgBox Prompt:=strMsg, Title:="New Template Saved"
errHandler:
If Err.Number > 0 Then
Select Case Err.Number
'Handled errors go here
Case Else:
If gbDebug Then
Stop: Resume
Else:
MsgBox "Whoops, there was an error: Error#" & Err.Number & vbCrLf & Err.Description _
, vbCritical, "Error", Err.HelpFile, Err.HelpContext
End If
End Select
End If
With Application
.ScreenUpdating = bScreenUpdating
.EnableEvents = bEnableEvents
.Calculation = lngCalculation
End With
End Sub
Jeff, that’s definitely an option. One issue in my case was that the workbooks were big and saving them took a long time, so I didn’t want to force the user to sit through it twice.
WHoops, I probably should put some code in that to delete the redundant .xlsm copy.