file save as dialogbox date - error

[lillypop]

Hello

VBA beginner here.

I am trying to make a Macro command, that will open an old file, then use a dialog box to ask user to enter a date and use this date to partially re-name the file and save into a new file. (Then I move some data from another file into this renamed file). But excel tells me the "extension cannot be used for the selected file type, error 1004". Does anyone know what the issue is?



Workbooks.Open Filename:= _
"C:\Users\my\Desktop\20170630.xlsx"

Dim fname As String
Dim fpath As String


fname1 = InputBox("enter date YYYYMMDD")
fname = fname1 & " _country.xlsm"
fpath = "C:\Users\my\Desktop"

ActiveWorkbook.SaveAs Filename:=fpath & fname
Application.DisplayAlerts = False
Sheets("dates").Delete



Workbooks.Open Filename:= _
"C:\Users\my\Desktop\folder\20160630_data.xlsx"
ActiveWindow.Visible = True


For Each Sheet In Workbooks("20160630_data.xlsx").Sheets


Sheet.Copy After:=Workbooks("fname.xlsm").Sheets(1)


Next Sheet


Windows("20160630_data.xlsx").Activate
ActiveWorkbook.Close savechanges:=False

Windows("fname.xlsm").Activate
ActiveWorkbook.Close savechanges:=True

 

[footoo]

Try :
fpath = "C:\Users\my\Desktop\"

 

[lillypop]

thanks, I've tried, still the same error..

 

[lillypop]

The issue is that it does not accept xlsm, only xlsx. And I had to change all the format to xlsx for it to work. Does anyone know what code I should use to make it compatible with xlsm?

 

[lillypop]

Solution found: just have to define the file format as xlOpenXMLWorkbookMacroEnabled.

 

[footoo]

Alternatively :

FileFormat:=52