Excel VBA: SaveCopyAs with different file extensti

Question

I have an Excel file with .xlsb extenstion and use its macros to generate several other Excel sheets based on the contents. The macros work in a way that they change the original Excel file and then use the SaveCopyAs method to save the generated Excel sheets.

The generated Excel sheets should be saved with .xlsx extension and format.

Using the ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLSX" method is not working for me because while it does change the extension it does NOT change the file format so when a user opens a generated Excel file he receives a warning message (something like "the file extension and format does not match"). The SaveCopyAs method does not have any other arguments.

How can I save the copies of my original .xlsb file with both the extension and format to be changed to .xlsx?

Note: the Workbook.SaveAs method does have a fileformat option, not sure if that helps / relevant.

Answer 1

Based on the hint by Zac, in my case its a better solution to copy the tab with the relevant changes into a freshly created excel file and then save it with the new filename.

ThisWorkbook.Sheets("myTab").Copy
ActiveWorkbook.SaveAs Filename:="c:\temp\xyz.xlsx", FileFormat:=51
ActiveWorkbook.Close

This is actually a lot better solution for me as the end user really need the generated tab only and not the macros or any other data in the orginal excel file.