Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

method saveas of object workbook failed

0

I want to save my workbook in one location file and my workbook is in xlsm, i would to save it in new path as xlsm too which is xlOpenXMLWorkbookMacroEnabled but then face an error run time error '1004' where the method save as is failed. When im debugging it highlight on ActiveWorkbook.SaveAs "\\Data\2022\FFTM\021.FFTM Declaration Data\1. Classes\" & Sheets("Marks").Range("G10") & "\" & Sheets("Marks").Range("C2") & "\" & Sheets("Marks").Range("F12") & "_" & Sheets("Marks").Range("K12") & "_" & Sheets("Marks").Range("E2") & "_" & Sheets("Marks").Range("L7") & ".xls", FileFormat:=xlOpenXMLWorkbookMacroEnabled

Sub SaveInvoice()
Dim MyFile As String
MyFile = "\\Data\2022\FFTM\021.FFTM Declaration Data\1. Classes\" & Sheets("Marks").Range("G10") & "\" & Sheets("Marks").Range("C2") & "\" & Sheets("Marks").Range("F12") & "_" & Sheets("Marks").Range("K12") & "_" & Sheets("Marks").Range("E2") & "_" & Sheets("Marks").Range("L7") & ".xls"

'create invoice in XLSX format
ActiveWorkbook.SaveAs "\\Data\2022\FFTM\021.FFTM Declaration Data\1. Classes\" & Sheets("Marks").Range("G10") & "\" & Sheets("Marks").Range("C2") & "\" & Sheets("Marks").Range("F12") & "_" & Sheets("Marks").Range("K12") & "_" & Sheets("Marks").Range("E2") & "_" & Sheets("Marks").Range("L7") & ".xls", FileFormat:=xlOpenXMLWorkbookMacroEnabled

'ActiveWorkbook.Close
Application.DisplayAlerts = True

Cells(83, 17) = Application.UserName

MsgBox "Saving Complete! Have a nice day~"

End Sub
excel
MS Office Version: 2019
posted by: kimurios (rep: 2) on Fri Mar 31, 2023 at 3:08 am
Answer
Discuss

Discussion

Hi Kimurios. 

Please edit your question to attach a representative Excel file using the Add Files button below the text. We should then be able to provide you with an answer.

Also you didn't reply to my Answer to 
Dropdown list in cell vba. Did that help? 
John_Ru (rep: 6142) Mar 31, '23 at 4:31 am
Did that work? 
John_Ru (rep: 6142) Apr 3, '23 at 10:24 am
Add to Discussion

Answers

0

Kimurios

You didn't attach a file to your original question but your line:

'create invoice in XLSX format
ActiveWorkbook.SaveAs "\\Data\2022\FFTM\021.FFTM Declaration Data\1. Classes\" & Sheets("Marks").Range("G10") & "\" & Sheets("Marks").Range("C2") & "\" & Sheets("Marks").Range("F12") & "_" & Sheets("Marks").Range("K12") & "_" & Sheets("Marks").Range("E2") & "_" & Sheets("Marks").Range("L7") & ".xls", FileFormat:=xlOpenXMLWorkbookMacroEnabled

combines values from several cells. if any of those contain a period mark (.), as part of a decimal number say, the resultant path/filename will be invalid (since the period cannot appear in a path and can be used only to separate the filename from the file extension). Remember too that files fomatted as times will actually contain decimal numbers, 

If that's not the case, please look for other characters which are "illegal" in Windows filenames- see the Microsoft guidance here: Naming Files, Paths, and Namespaces

Hope this helps. If so, please remember to mark this Answer as Selected. If not, please attach a file as I suggested earlier.

posted by: John_Ru (rep: 6142) on Fri Mar 31, 2023 at 1:37 pm
Discuss

Discussion

Kimorios. Please respond.
John_Ru (rep: 6142) Apr 5, '23 at 10:24 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login