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