VBA error with saving and then opening .XLSM

[DouglasWicker]

Morning all,

I have this code below written to save my spreadsheet

Dim Path As String
Dim filename As String

Path = "Private File Path"
filename = Range("Y2") + Range("Y1")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", FileFormat:=xlNormal


The issue I have is that the above code works fine to save an xls file but when I change it to xlsm, it seems to save fine but when I open it I get this error message?

Excel cannot open the file 'Filename.xlsm' because the file format or file extension is not valid. Verify that the file has not been corrupted and the file extension matches the format of the file.

It will open if I save it as an .xlsm file through excel's Save As function.

Any ideas?

 

[mrhstn]

Guessing it's to do with you saving a .xlsm in file formal xlNormal, try FileFormat:=52 or xlOpenXMLWorkbookMacroEnabled

 

[gallen]

@mrhstn is correct. Also you don't need the & ".xlsm" part, this is apended automatically when setting the fileformat

Dim Path As String
Dim filename As String


Path = "C:\Test\"
filename = "SaveAsTest2"
ActiveWorkbook.SaveAs filename:=Path & filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[DouglasWicker]

Cheers guys. Works well

 

[RunnerIE]

I'm having the problem saving to FileFormat:=xlOpenXMLWorkbookMacroEnabled
I'm running Excel 2019
I can save a blank workbook as .XLSM and open it, but not the VBA saved XLSM.

Anyone else encounter the same issue and what was the fix?
Thanks!