VBA Question - ActiveWorkbook.SaveCopyAs / What do I use for FileFormat

[a68tbird]

Hello all -
I am trying to save a copy of a multi-sheet workbook as an XLSX file format, to a shared OneDrive folder, using variables to build the directory path and file name. Here is my code:

Sub SaveWorkbook()
    Dim strXLS As String, strXLSName As String
    
strXLS = "C:\Users\" & ActiveSheet.Range("B50").Value & "\OneDrive\PurchaseOrders\PurchaseOrders_ExcelCopies\2023 POs" & "\"
strXLSName = "PO-" & ActiveSheet.Range("G4").Value & ActiveSheet.Range("H4") & ".xlsx"
 
       ActiveWorkbook.SaveCopyAs Filename:= _
       strXLS & strXLSName

End Sub

The file saves to the proper folder, as an XLSX, but I can't open it - the error message states the extension isn't correct for this file type. If I include "FileFormat:=51" where I build the path and file name, then the script gets a compile error, stating that the Named Argument not found.

I have this working when I only needed the active sheet saved, but my needs have changed, and I now require the entire workbook to be saved as a copy. Any help here would be much appreciated.

Thanks

 

[aaewalsh]

Try this

Sub SaveWorkbook()
    Dim strXLS As String, strXLSName As String
    
    strXLS = "C:\Users\" & ActiveSheet.Range("B50").Value & "\OneDrive\PurchaseOrders\PurchaseOrders_ExcelCopies\2023 POs" & "\"
    strXLSName = "PO-" & ActiveSheet.Range("G4").Value & ActiveSheet.Range("H4") & ".xlsx"

    ActiveWorkbook.SaveAs Filename:=strXLS & strXLSName, FileFormat:=xlOpenXMLWorkbook

End Sub

 

[a68tbird]

Try this

Sub SaveWorkbook()
    Dim strXLS As String, strXLSName As String
   
    strXLS = "C:\Users\" & ActiveSheet.Range("B50").Value & "\OneDrive\PurchaseOrders\PurchaseOrders_ExcelCopies\2023 POs" & "\"
    strXLSName = "PO-" & ActiveSheet.Range("G4").Value & ActiveSheet.Range("H4") & ".xlsx"

    ActiveWorkbook.SaveAs Filename:=strXLS & strXLSName, FileFormat:=xlOpenXMLWorkbook

End Sub

Thanks for the quick reply. I'm still getting a compile error. Highlights "FileFormat" as Named Argument not found.

 

[aaewalsh]

Try this

Option Explicit

Sub SaveWorkbook()
    Dim strXLS As String, strXLSName As String

    strXLS = "C:\Users\" & ActiveSheet.Range("B50").Value & "\OneDrive\PurchaseOrders\PurchaseOrders_ExcelCopies\2023 POs" & "\"
    strXLSName = "PO-" & ActiveSheet.Range("G4").Value & ActiveSheet.Range("H4") & ".xlsx"

    ActiveWorkbook.SaveAs Filename:=strXLS & strXLSName, FileFormat:=xlOpenXMLWorkbook
End Sub

 

[a68tbird]

Apologies - I didn't notice that you changed it from "ActiveWorkbook.SaveCopyAs" to "ActiveWorkbook.SaveAs". Is there anyway to make it work with the SaveCopyAs method? I don't want my users to accidentally overwrite or lose the template workbook.

 

[amthorpe2000]

@a68tbird
Did you find a solution ?

I have a similar issue that I have an XLSB file with macros to do various things, but I need to get the data tables into Power BI which it seems does not like xlsb or xlsm so need to convert to xlsx with latest updates. Easiest way is to put an on save procedure to SaveCopyAs xlsx and leave the xlsb file open if the user wants to continue working. the SaveAs method means that the xlsb closes, and the xlsx is left open. tried savecopyas with .xlsx but it still has the modules etc. so won't work with Power BI. As both are Microsoft you would have though they would have thought this through properly.

 

[MARK858]

Try putting an individual table into a separate sheet in the xlsb file and doing the Power BI query on that (I'm not guaranteeing this will work, but it has in the past)

 

[amthorpe2000]

Try putting an individual table into a separate sheet in the xlsb file and doing the Power BI query on that (I'm not guaranteeing this will work, but it has in the past)

power bi, won't open the navigator, to access anything while you have xlsm or xlsb

 

[MARK858]

Sorry I was thinking of Power Query rather than Power Bi. Are you using the online or desktop version of Power Bi for the xlsm files?

 

[dmt32]

Did you find a solution ?
I have a similar issue that I have an XLSB file with macros to do various things, but I need to get the data tables into Power BI which it seems does not like xlsb or xlsm so need to convert to xlsx with latest updates.

SaveCopyAs just makes a copy of your current workbook with your chosen file name - it does not have any of the attributes of that allows you to specify another file format type it does what it says, SavesCopyAs!

A solution I provided for others here with similar requirement was to create code that would:
- Save active workbook
- Use SaveAs to save a copy with the specified filename & file type
- Re-Open the saved workbook
- Close the copy workbook.

Give following a try & see if will help you.

Place in STANDARD module

Sub SaveCopy(ByVal FileName As String, ByVal FileFormat As XlFileFormat, Optional ByVal wb As Workbook, _
                        Optional ByVal OpenPassword As String, Optional ByVal WriteResPassword)
    'dmt32 jan 2018
    Dim ActiveFile  As String
    
    On Error GoTo myerror
    If wb Is Nothing Then Set wb = ActiveWorkbook
    If wb.Path = "" Then Err.Raise 76
    
    With Application
        .ScreenUpdating = False: .EnableEvents = False: .DisplayAlerts = False
    End With
    
    With wb
        .Save
        ActiveFile = .FullName
        .SaveAs FileName:=FileName, FileFormat:=FileFormat, _
                Password:=OpenPassword, WriteResPassword:=WriteResPassword
    End With
    
    Workbooks.Open ActiveFile
    
    wb.Close False
    
myerror:
    With Application
        .ScreenUpdating = True: .EnableEvents = True: .DisplayAlerts = True
    End With
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Code uses xlFileFormat enumeration allowing you to specify format type.

to use

SaveCopy FileName:="C:\My Documents\My Files\Test File", FileFormat:=xlOpenXMLWorkbook

Dave