function Now() in ActiveWorkbook.SaveAs

Question

I can't figure it out why macro everytime save file with filename .xl&somedigits like filename.xl105 with this code:

ActiveWorkbook.SaveAs ("C:\Users\username\Desktop\" & "Fname " & Format(Now(), "DD.MM.YYYY" & ".xlsm")), _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

like (from ".xlsm") sm was some part of a function. The resulting filenames looks like Fname.xl105, Fname.xl35, Fname.xl254 etc... Some thougts?

Darren Bartrup-Cook · Accepted Answer · 2021-05-06T08:45:06.607

Your ".xlsm" is inside the Format.

Format(Now(), "DD.MM.YYYY" & ".xlsm") returns 06.05.2021.xl105
Format(now(),"xlsm") returns xl355

I'm guessing it's using xlsm as symbols - the m represents the month (so it's returning 5 in May).

Use

ActiveWorkbook.SaveAs ("C:\Users\username\Desktop\" & "Fname " & Format(Now(), "DD.MM.YYYY") & ".xlsm"), _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Also, Fname looks like a variable but you're using it as a string. Maybe use:

ActiveWorkbook.SaveAs ("C:\Users\username\Desktop\" & Fname  & Format(Now(), "DD.MM.YYYY") & ".xlsm"), _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

function Now() in ActiveWorkbook.SaveAs

1 Answers1