Copy an xlsm to an xlsx

Copy an xlsm to an xlsx

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:

  1. 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.
  2. 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:

Sub CreateWorkbookCopy()
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.

11 thoughts on “Copy an xlsm to an xlsx

      • Jeff, here they are in all their glory:

        Function SubFolderExists(ByVal FolderFullName As String) As Boolean
        If Len(Dir(FolderFullName, vbDirectory)) > 0 Then
            SubFolderExists = True
        End If
        End Function

        Sub MakeSubFolder(ByVal FolderFullName As String)
        FileSystem.MkDir (FolderFullName)
        End Sub
  1. 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:

    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
            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.

  2. “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 :).

  3. 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:

    Sub CreateBlankTemplate()

    '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.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>