Dutch Gemini's Weblog Pages

October 22, 2009

Excel’s SaveCopyAs turbocharged

Filed under: Excel,Programming,VBA — dutchgemini @ 4:30 pm

Unlike the workbook’s .SaveAs method that allows specifying which destination File Format you want, the .SaveCopyAs method is only capable of saving with the same File Format.

If you develop cross-version applications in VBA for Excel you may want to save copies of the workbook to a different version of Excel (upwards or downwards, provided you have the necessary compatibility packs installed) without making saves to the current workbook, then .SaveCopyAs is not the solution.

To overcome this limitation, I have developed a small function that allows specifying the destination format as well. The function returns a True in case of success, False otherwise.

If the source and destination File Format is the same, it just uses SaveCopyAs, otherwise it uses a temporary file which is created using the same method but then opened and “SavedAs” to the final format. The function makes the assumption that the source workbook is not password protected, which could provide some problems when opening the temporary workbook.

The Scripting.FileScriptingObject is used for obtaining a temporary file name and requires the Microsoft Scripting Runtime to be referenced in the VBA Project. You can also create a temporary name by your self.

The code is of course not totally fool proof, there is no check that the File Type (of type Long) passed as parameter is one of the accepted enums. You could also try to see if the file type selected matches the extension, and so on. With Excel 2007+ you can also include the test for a new workbook property named .HasVBProject in your routine and conduct towards macro-enabled formats.

I have called it SaveWorkbookAs and it returns a Boolean result (True success, False otherwise).

Public Function SaveWorkbookAs(pWorkbook As Workbook, _
                               pFileName As String, _
                               pFileFormat As XlFileFormat) As Boolean
'
' This function works like a wrapper for the internal '.SaveCopyAs()' function that makes a
' copy of the workbook current in memory but that is unable to save in a format different from
' the source workbook. This inability makes it difficult to handle the new Excel file formats in
' a pre-Excel 2007 environment or when saving pre-2007 macro-enabled formats from Excel 2007.
' As the main issue here is not altering in any form the workbook in memory, it is saved using
' the internal '.SaveCopyAs()' function to a temporary file. This temporary file is then opened
' and saved in the desired destination format. The temporary file is deleted.
'
On Error Resume Next
    Dim wScreenUpdating As Boolean
    Dim wEnableEvents   As Boolean
    Dim wDisplayAlerts  As Boolean
    Dim wFSO            As Scripting.FileSystemObject
    Dim wWorkbook       As Workbook
    Dim wTempName       As String

    ' Set default.
    '
    SaveWorkbookAs = False

    ' Get access to the file system.
    '
    Set wFSO = New Scripting.FileSystemObject

    ' No book no party.
    '
    If pWorkbook Is Nothing Then GoTo EndFunction
    If (pFileName = vbNullString) Then Goto EndFunction

    ' Same format requested, can use native Excel function.
    '
    If (pWorkbook.FileFormat = pFileFormat) _
    Then
        Err.Clear
        pWorkbook.SaveCopyAs pFileName
        SaveWorkbookAs = (Err.Number = 0)
        GoTo EndFunction
    End If

    ' We need to save in a different format. It goes via creating a new workbook,
    ' We make a copy of the book currently in memory to a different file. Since our work is restricted to
    ' this function and we are not opening it for editing, we do not want any visual feedback. Also, we do
    ' not want to trigger events. Also avoid any warnings about loosing stuff, if we need to destroy, it
    ' is a 'Yes' anyway.
    '
    With Application
        wScreenUpdating = .ScreenUpdating:  .ScreenUpdating = False
        wEnableEvents = .EnableEvents:      .EnableEvents = False
        wDisplayAlerts = .DisplayAlerts:    .DisplayAlerts = False
    End With

    ' Save this workbook to the temporary file.
    '
    Err.Clear
    wTempName = wFSO.GetTempName
    pWorkbook.SaveCopyAs wTempName
    If (Err.Number > 0) Then GoTo EndFunction

    ' Open the temporary file without too much questioning.
    '
    Err.Clear
    Set wWorkbook = Application.Workbooks.Open(wTempName, xlUpdateLinksNever)
    If (Err.Number > 0) Then GoTo EndFunction

    ' Now save the file in the new format. Cannot jump to EndFunction because the file is open and
    ' must be closed before quitting this function.
    '
    wWorkbook.SaveAs Filename:=pFileName, FileFormat:=pFileFormat
    SaveWorkbookAs = (Err.Number = 0)

    ' Close the file. Changes already saved.
    '
    wWorkbook.Close SaveChanges:=False

EndFunction:
    ' Done. Remove the garbage.
    '
    If (VBA.LenB(wTempName) > 0) _
    Then If wFSO.FileExists(wTempName) Then wFSO.DeleteFile wTempName, True

    With Application
        .ScreenUpdating = wScreenUpdating
        .EnableEvents = wEnableEvents
        .DisplayAlerts = wDisplayAlerts
    End With
    Set wWorkbook = Nothing
    Set wFSO = Nothing
End Function

When using this code on Excel pre-2007 and want to save to Excel 2007 formats and you like using enums, I recommend that you import the Excel 2007 File Format XlFileFormat Enum into your project.

' Copy of 'Excel.XlFileFormat'
'
Public Enum XlFileFormat
    xlAddIn = 18                        ' Microsoft Excel 97-2003 Add-In
    xlAddIn8 = 18                       ' Microsoft Excel 97-2003 Add-In
    xlCSV = 6                           ' CSV
    xlCSVMac = 22                       ' Macintosh CSV
    xlCSVMSDOS = 24                     ' MSDOS CSV
    xlCSVWindows = 23                   ' Windows CSV
    xlCurrentPlatformText = -4158       ' Current Platform Text
    xlDBF2 = 7                          ' DBF2
    xlDBF3 = 8                          ' DBF3
    xlDBF4 = 11                         ' DBF4
    xlDIF = 9                           ' DIF
    xlExcel12 = 50                      ' Excel12
    xlExcel2 = 16                       ' Excel2
    xlExcel2FarEast = 27                ' Excel2 FarEast
    xlExcel3 = 29                       ' Excel3
    xlExcel4 = 33                       ' Excel4
    xlExcel4Workbook = 35               ' Excel4 Workbook
    xlExcel5 = 39                       ' Excel5
    xlExcel7 = 39                       ' Excel7
    xlExcel8 = 56                       ' Excel8
    xlExcel9795 = 43                    ' Excel9795
    xlHtml = 44                         ' HTML format
    xlIntlAddIn = 26                    ' International Add-In
    xlIntlMacro = 25                    ' International Macro
    xlOpenDocumentSpreadsheet = 60      ' OpenDocument Spreadsheet
    xlOpenXMLAddIn = 55                 ' Open XML Add-In
    xlOpenXMLTemplate = 54              ' Open XML Template
    xlOpenXMLTemplateMacroEnabled = 53  ' Open XML Template Macro Enabled
    xlOpenXMLWorkbook = 51              ' Open XML Workbook
    xlOpenXMLWorkbookMacroEnabled = 52  ' Open XML Workbook Macro Enabled
    xlSYLK = 2                          ' SYLK
    xlTemplate = 17                     ' Template
    xlTemplate8 = 17                    ' Template 8
    xlTextMac = 19                      ' Macintosh Text
    xlTextMSDOS = 21                    ' MSDOS Text
    xlTextPrinter = 36                  ' Printer Text
    xlTextWindows = 20                  ' Windows Text
    xlUnicodeText = 42                  ' Unicode Text
    xlWebArchive = 45                   ' Web Archive
    xlWJ2WD1 = 14                       ' WJ2WD1
    xlWJ3 = 40                          ' WJ3
    xlWJ3FJ3 = 41                       ' WJ3FJ3
    xlWK1 = 5                           ' WK1
    xlWK1ALL = 31                       ' WK1ALL
    xlWK1FMT = 30                       ' WK1FMT
    xlWK3 = 15                          ' WK3
    xlWK3FM3 = 32                       ' WK3FM3
    xlWK4 = 38                          ' WK4
    xlWKS = 4                           ' Worksheet
    xlWorkbookDefault = 51              ' Workbook default
    xlWorkbookNormal = -4143            ' Workbook normal
    xlWorks2FarEast = 28                ' Works2 FarEast
    xlWQ1 = 34                          ' WQ1
    xlXMLSpreadsheet = 46               ' XML Spreadsheet
End Enum

Otherwise, you should only use numbers (of type Long).

Happy copying.

Advertisements

10 Comments »

  1. Dutch Gemini,

    I just wanted to say great piece of code. This is exactly what I needed as i wanted to save a file but not in excel format and I didn’t want to ‘deactivate’ the current excel workbook.

    Thanks a lot 🙂

    Comment by Jon — November 10, 2010 @ 7:02 am | Reply

  2. I am wanting to save a copy as a .CSV and this looks like an good example to start with. I am learning VBA, and I understand that this function should go into a module. My problem seems to be calling this public function from my the private sub on my userform.

    Private Sub CmdSav_Click()
    Function SaveWorkbookAs (), FileFormat=6

    Comment by Chris — March 2, 2011 @ 7:58 pm | Reply

    • Hi Chris,

      Indeed you can -and this is often the recommended practice- put the function in a separate module. If you include the copy of Excel’s Enums as well (they go on top, in the module’s declaration section) then you can protect yourself from spurious compiler errors that may arise if your version of Excel does not know the one(s) you are using.

      This piece of code is designed primarily with the objective to cope with differences between the workbook formats available in pre-2007 and 2007+ Excel, but you can use it without problems also to generate CSV files from your workbook.

      Somewhere in your code:

      Private Sub CmdSav_Click()
      ' Const instead of Enums
      '
      Const xlCSV = 6
      Const xlCSVMSDOS = 24
      Const xlCSVWindows = 23
      '
      ' Save a copy of the Active Workbook as CSV using the original name:
      '
      SaveWorkbookAs ActiveWorkbook, "C:\" & ActiveWorkbook.Name & ".csv", xlCSV
      End Sub

      Enjoy.

      Dutch

      Comment by dutchgemini — March 3, 2011 @ 12:25 pm | Reply

  3. Watch out when calling this function using a 2007+ workbook with VBA code that was never saved.

    Excel’s default FileFormat is XLSX (51-xlOpenXMLWorkbook) and this stays until you save it to a macro-enabled format (eg. XLSM). Only at that point Excel recognises it as a workbook with macros.

    So if you use “SaveWorkbookAs” before ever saving a workbook, Excel continues to consider your VBA-packed workbook as a non-macro workbook and will create a file having XLSX format, with all the macro code removed and without warning.

    I will post an update to the above function dealing with this particular issue.

    Comment by dutchgemini — May 12, 2011 @ 4:50 pm | Reply

  4. […] is the current file format? Apparently (as found here) SaveCopyAs can only save the same format as the original file. I learned something new If that […]

    Pingback by Quicky — March 12, 2014 @ 1:38 pm | Reply

  5. You Genuis! Thank-you!

    Comment by Dave — March 21, 2014 @ 8:08 pm | Reply

  6. Hi, I think this the exact code I need to solve my problem , so I copied and called the SaveWorkbooksAs function . this is how called it . but it is throwing an error code 424 Object required , Can anyone please help. I am completely new to VBA. Bewlow the code I am using after I copied the exact SaveWorkbooksAs . Not able to figure out what is the problem , Any help ?

    Below is the code
    ======================================================================

    Rem Delete all items with Quantity = 0 for CPO items only

    Rem Create separate documents for different customer locations
    Rem Cut & paste CPO above CFC in one document for each

    fileName = curfolder + “\” + Format(Date, “mmddyy”) + “_” + PathName(UBound(PathName))

    Call exportToXls(fileName, header)

    End If ‘skip if no file in the folder

    Next
    Call aveWorkbookAs(Workbook.Add, fileName + “.xls”, 18)

    End Sub

    Comment by Sourav — August 27, 2014 @ 6:51 pm | Reply

    • Call SaveWorkbookAs(newBook, fileName + “.xls”, 18)

      Comment by Sourav — August 27, 2014 @ 6:52 pm | Reply

      • Actually, since you are creating an XLS of one in memory, the call could also receive the “ActiveWorkbook”, or any workbook of which you want to make a copy.

        Comment by dutchgemini — August 28, 2014 @ 7:57 am

    • Object required means that you are not providing an “Object” to the code, but something else. An “object” is any type of element based on classes. Examples of objects in Excel are workbooks, worksheets, collections, the Excel application itself.

      In your example, Workbooks.Add (with an “s” appended to “Workbook”) creates an object of type “Workbook” that you *must* allocate in memory, by assigning it to a memory variable otherwise Excel does not know where to store the data. The function receives the workbook “ByRef” and therefore needs a memory pointer to the workbook created, not to the “Workbooks.Add” method of Excel.

      To correct the code, create a variable of type “Excel.Workbook” (or “Object”, that’s fine, too), assign it the object returned by “Workbooks.Add” and pass this to the function.

      Dim wWorkbook As Excel.Workbook
      Set wWorkbook = Application.Workbooks.Add ' "Application." is not necessary but is used explicit and consistent programming
      SaveWorkbooksAs(wWorkbook, "filename.xls", 18)

      Comment by dutchgemini — August 28, 2014 @ 7:55 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: