VBA - Can't save .xlsm file to .xlsx - runtime error 1004

[soundtrackz]

I have many macros with this and it suddenly stopped working - displaying runtime error 1004 - can't save from xlsm file to macro-free file.
Any idea, what happened?

Sub Saving()
    Dim strName As String

    Application.DisplayAlerts = False
    
    strName = ThisWorkbook.Path & "\SomeName.xlsx"
    ThisWorkbook.SaveAs Filename:=strName, FileFormat:=xlOpenXMLWorkbook
    
    Application.DisplayAlerts = True
End Sub

 

[Joe4]

Welcome to the Board!

You cannot save any file that contains Macros/VBA to an "xlsx" format, as that format does NOT allow Macros/VBA.
You will need to save it to a format that DOES allow Macros/VBA, like "xlsm" or "xlsb", and change the "FileFormat" argument.

Here is a listing of the different options. Make sure that the FileFormat and extension coincide:

XlFileFormat enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

For example, if electing to use "xlsm", it should look like:

Sub Saving()
    Dim strName As String

    Application.DisplayAlerts = False
    
    strName = ThisWorkbook.Path & "\SomeName.xlsm"
    ThisWorkbook.SaveAs Filename:=strName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    Application.DisplayAlerts = True
End Sub

 

[RoryA]

That code should work fine as long as the workbook with the code in it has been saved.

 

[soundtrackz]

You cannot save any file that contains Macros/VBA to an "xlsx" format, as that format does NOT allow Macros/VBA.

Well, I could until about 2 days ago, when I suddenly couldn’t.
Was there some change in policies or what?

 

[soundtrackz]

That code should work fine as long as the workbook with the code in it has been saved.

Of course, the workbook is saved.
It's driving me mad, because now I have to split everything into 2 files - one file with the code (.xlsm) and another .xlsx template.

 

[RoryA]

If you don't have any code in the worksheets, you could try just copying them and then saving the newly-created workbook.

 

[Joe4]

Of course, the workbook is saved.
It's driving me mad, because now I have to split everything into 2 files - one file with the code (.xlsm) and another .xlsx template.

I would double-check to make sure that there is no code in the "xlsx" one.
If you turned on the Macro Recorder at any point, you may have accidentally recorded some code in there.
Also note, not only do you need to remove all VBA code from the "xlsx" file, but also any VBA modules that were created (i.e Module1). Even empty VBA modules will cause Excel to think there is VBA code in there.

 

[RoryA]

It appears that the dialog box that Excel puts up in response to doing this has changed. Before, the default response (which is what is selected if you use DisplayAlerts = False) was Yes, which would allow saving. With the new dialog, the default response is now Go Back, which prevents the save.

 

[Fluff]

Interestingly I have no problem saving an xlsm file with code to an xlsx file, even though I've seen a number of posts about this.
So not sure why it only seems to affect some people.

 

[RoryA]

Which version/build are you on?