Debug: Run time error 1004 - "Method Save_AS Object' _Workbook' Failed

[Ananthak275]

Can someone help me understand why my code is erroring out?

Workbooks.Add
Set wbk_syst = ActiveWorkbook
ActiveWorkbook.SaveAs "\\system " & Date & ".xlsx"

 

[Joe4]

Verify that you can manually save a file to those other directories (perhaps you do not have permission to do so).

Also, do you mean to have a "y" before the word "Extract" in your file directory, or is that a typo?
If so, that is probably your issue.

 

[Ananthak275]

Verify that you can manually save a file to those other directories (perhaps you do not have permission to do so).

Also, do you mean to have a "y" before the word "Extract" in your file directory, or is that a typo?
If so, that is probably your issue.

Yep, i can manually. For some reason only the first SAVE.AS will run. When i moved the lines around so that Document was run before System, Document was saved but System wasn't.

 

[Joe4]

Can you post your entire code, as you currently have it?

 

[Ananthak275]

This is the entire code;

Workbooks.Add
Set wbk_syst = ActiveWorkbook
ActiveWorkbook.SaveAs "\\yExtract\system " & Format(Date, "yyyy-mm-dd") & ".xlsx"

Workbooks.Add
Set wbk_doc = ActiveWorkbook
ActiveWorkbook.SaveAs "\\yExtract\documents " & Format(Date, "yyyy-mm-dd") & ".xlsx"

Workbooks.Add
Set wbk_doc = ActiveWorkbook
ActiveWorkbook.SaveAs "\\yExtract\optimization " & Format(Date, "yyyy-mm-dd") & ".xlsx"

 

[Joe4]

There is nothing wrong with the structure of the code.

I tested it on my system like this:

Sub MyTest()

Workbooks.Add
Set wbk_syst = ActiveWorkbook
ActiveWorkbook.SaveAs "C:\Temp\system " & Format(Date, "yyyy-mm-dd") & ".xlsx"

Workbooks.Add
Set wbk_doc = ActiveWorkbook
ActiveWorkbook.SaveAs "C:\Temp\documents " & Format(Date, "yyyy-mm-dd") & ".xlsx"

Workbooks.Add
Set wbk_doc = ActiveWorkbook
ActiveWorkbook.SaveAs "C:\Temp\optimization " & Format(Date, "yyyy-mm-dd") & ".xlsx"

End Sub

and I ran it, and it created all three files, like this:

The two most likely causes of issues are:
1. You are not setting up the file path and name the way you think, and are actually trying to reference folders that do not exist.
2. You do not have access to save to the folders you are trying to save to.

I would recommend building the file paths/names as variables, and use a MsgBox to return each one to make sure that you are actually created the path/file name that you think you are:

Workbooks.Add
Set wbk_syst = ActiveWorkbook
fname1 = "\\yExtract\system " & Format(Date, "yyyy-mm-dd") & ".xlsx"
MsgBox fname1
ActiveWorkbook.SaveAs fname1

Workbooks.Add
Set wbk_doc = ActiveWorkbook
fname2 = "\\yExtract\documents " & Format(Date, "yyyy-mm-dd") & ".xlsx"
MsgBox fname2
ActiveWorkbook.SaveAs fname2

Workbooks.Add
Set wbk_opt = ActiveWorkbook
fname3 = "\\yExtract\optimization " & Format(Date, "yyyy-mm-dd") & ".xlsx"
MsgBox fname3
ActiveWorkbook.SaveAs fname3

 

[Joe4]

In addition to what I said in the previous both, I just noticed something. You are using the same worksheet variable name for the 2nd and 3rd files:

Workbooks.Add
Set wbk_syst = ActiveWorkbook
ActiveWorkbook.SaveAs "\\yExtract\system " & Format(Date, "yyyy-mm-dd") & ".xlsx"

Workbooks.Add
Set wbk_doc = ActiveWorkbook
ActiveWorkbook.SaveAs "\\yExtract\documents " & Format(Date, "yyyy-mm-dd") & ".xlsx"

Workbooks.Add
Set wbk_doc = ActiveWorkbook
ActiveWorkbook.SaveAs "\\yExtract\optimization " & Format(Date, "yyyy-mm-dd") & ".xlsx"

While I don't think it should affect the "SaveAs" for these three files, it could impact you later in your code, for whatever purpose you are using these worksheet variables for (you don't seem to be using them at all for anything in the creation/save steps we are discussing here - so if you really have no other VBA code in this procedure, they really serve no purpose and can be removed completely).

 

[Ananthak275]

There is nothing wrong with the structure of the code.

I tested it on my system like this:

Sub MyTest()

Workbooks.Add
Set wbk_syst = ActiveWorkbook
ActiveWorkbook.SaveAs "C:\Temp\system " & Format(Date, "yyyy-mm-dd") & ".xlsx"

Workbooks.Add
Set wbk_doc = ActiveWorkbook
ActiveWorkbook.SaveAs "C:\Temp\documents " & Format(Date, "yyyy-mm-dd") & ".xlsx"

Workbooks.Add
Set wbk_doc = ActiveWorkbook
ActiveWorkbook.SaveAs "C:\Temp\optimization " & Format(Date, "yyyy-mm-dd") & ".xlsx"

End Sub

and I ran it, and it created all three files, like this:

View attachment 64495

The two most likely causes of issues are:
1. You are not setting up the file path and name the way you think, and are actually trying to reference folders that do not exist.
2. You do not have access to save to the folders you are trying to save to.

I would recommend building the file paths/names as variables, and use a MsgBox to return each one to make sure that you are actually created the path/file name that you think you are:

Workbooks.Add
Set wbk_syst = ActiveWorkbook
fname1 = "\\yExtract\system " & Format(Date, "yyyy-mm-dd") & ".xlsx"
MsgBox fname1
ActiveWorkbook.SaveAs fname1

Workbooks.Add
Set wbk_doc = ActiveWorkbook
fname2 = "\\yExtract\documents " & Format(Date, "yyyy-mm-dd") & ".xlsx"
MsgBox fname2
ActiveWorkbook.SaveAs fname2

Workbooks.Add
Set wbk_opt = ActiveWorkbook
fname3 = "\\yExtract\optimization " & Format(Date, "yyyy-mm-dd") & ".xlsx"
MsgBox fname3
ActiveWorkbook.SaveAs fname3

this worked! thank you very much