Save .xlsm file as .xlsx used to work but now gives 1004 error

[unleashed7]

Hello, it's my first time here!

I'm stuck on this problem, as they updated Excel recently and can't force save file as default xlsm while it was working before for years.

ActiveWorkbook.SaveAs "foo", 51

This throws a 1004 error because "VB projects and XLM Sheets cannot be saved in a macro-free workbook".
Disabling alerts also doesn't help, as this is a Run-time error not an alert.

Funny thing is it almost seems like a bug, as if I try to save the file after this error as .xlsx manually the "Save" button literally does nothing, no warning or anything, just doesn't work. I can only save as an xlsm, and if I do save it like that, close excel then REOPEN this saved fuke, suddenly the Save button works for xlsm and asks me if I want to "Save and erase features" which I do.

Is there any way to solve this?

 

[Logit]

One Method :

Option Explicit

Sub XLSMasXLSX()

Dim newFileFullName As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
newFileFullName = "C:\Users\gagli\OneDrive\Desktop\List.xlsx"

ActiveWorkbook.SaveAs Filename:=newFileFullName, FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Workbooks.Open Filename:=newFileFullName
Windows("List.xlsx").Activate


End Sub

 

[RoryA]

It's a change to the dialog involved (there are a few discussions about this going on in this and other forums currently, such as this one). We are still trying to narrow down which versions are affected.

 

[unleashed7]

One Method :

Option Explicit

Sub XLSMasXLSX()

Dim newFileFullName As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
newFileFullName = "C:\Users\gagli\OneDrive\Desktop\List.xlsx"

ActiveWorkbook.SaveAs Filename:=newFileFullName, FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Workbooks.Open Filename:=newFileFullName
Windows("List.xlsx").Activate


End Sub

That didn't work unfortunately.

It's a change to the dialog involved (there are a few discussions about this going on in this and other forums currently, such as this one). We are still trying to narrow down which versions are affected.

Thanks for the info! At least now I know why it's not working, that's something

 

[JeffGrant]

Hi Rory,
I am running O365 and this is most Definity an issue for me. Hence I have removes and intermediate file saving routines from my model. Hope this helps

 

[Skyybot]

Have you tried

Workbooks("WorkBook.xlsm").SaveCopyAs "C:\Users\UserName\Documents\FileName.xlsx"

 

[RoryA]

You cannot change file format using savecopyas

 

[Skyybot]

You cannot change file format using savecopyas

I thought to myself, after reading this, why would OP want to change formats if they want to save as default xlsx? Then I realized OP was trying to save with a Macro. Duh... My fault :D

 

[Alex Blakenburg]

I know very little about this but @Skyybot reminded me of something.
If you put the save as code in a seperate workbook will it then let you save the main workbook as xlsx
(ie is it complaining about saving itself without the macro that is actually running the save as)

If that is the case and you need a workaround this might work for you:

Save As Keeping Original Open

How an Excel VBA Macro can save copies of the workbook while keeping the workbook open

www.mrexcel.com

 

[soundtrackz]

I also reported about this error a while ago in another thread:

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

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 &...

www.mrexcel.com

Strangely enough, this code is again working without an error.
I have Version 2311 (17029.20068).

Sub Saving()
    Dim strFileName As String

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