file save as dialogbox date - error

lillypop

New Member
Joined
Jul 20, 2018
Messages
14
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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?
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top