Hi,

Whilst fixing another user's code, I wondered if there was a way to change the output of their script to .xlsb rather than .xlsx to save on file sizes.

The current script saves the opened file as .xlsx in another folder, which strips out the macros but retains the data. This is exactly what I want it to do but I would rather save it as .xlsb if possible because the difference in file size is ~55mb vs ~28mb and we are running this script multiple times each day (the smaller files would open quicker on other users' machines as well as saving on space).

My proposed solution was to output the .xlsx file first (as a temporary file) and then save the .xlsx file (which should be stripped of macros) as .xlsb... However, the .xlsb does still save with the macro, which is not what I expected...

Here's a reduced version of the code which I copied into a test file:

Private Sub Workbook_Open()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

    ThisWorkbook.SaveAs Filename:="C:\Users\me\Desktop\VBA Test\Test Save File 1.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Workbooks("Test Save File 1.xlsx").SaveAs Filename:="C:\Users\me\Desktop\VBA Test\Test Save File 2.xlsb", FileFormat:=xlExcel12, CreateBackup:=False
    
    ActiveWorkbook.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
Strangely, if I then open the .xlsx file and save it as .xlsb manually, I get the desired outcome i.e. no macros... Why doesn't that work within the code?

P.S. the same thing happens with the macros regardless of whether or not 'Application.DisplayAlerts = False' is present.