CODEWITHSUNDEEP
excelvba
LaZoR_Bear
LaZoR_Bear

Reputation: 97

How to fix error 1004 on a wb.SaveAs function

This is my first big VBA project for my work. I am automating the filing of multiple reports from multiple sources. Last week I had finished working on implementing two of the reports I need to use and the wb.SaveAs function was working. Now that I have added a third source, the wb.SaveAs doesn't work anymore.

I have tried to reference the mapped network drive, I've tried direct reference and I've tried to put the path in a variable. None of these worked. I searched this site and others, but the answer I found to a similar problem didn't apply.

'This is only the code for the loading and saving part
Dim wbTime As Workbook
Dim wsTime As Worksheet
Dim wbRP As Workbook
Dim wsRPDic As Worksheet
Dim wbTdB As Workbook
Dim wsTdB As Worksheet
Dim wbMots As Workbook
Dim wsMots As Worksheet


'Setting all the workbooks and worksheets to be used
Set wbTime = Workbooks("Timesheets.xls")
Set wsTime = wbTime.Worksheets("TimeSheets")
Set wbRP = Workbooks.Open("\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Rapport Productivit? Prod 2019.xlsx")
Set wsRPDic = wbRP.Worksheets("Dictionary")
Set wbTdB = Workbooks.Open("\\BUR-SERV\Data\xxx\xxx\xxx xxx\Suivis, Rapport et TdB\Tableau de Bord.xlsx")
Set wsTdB = wbTdB.Worksheets("Tableau de Bord Complet - 2019")
Set wbMots = Workbooks.Open("\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Raw Reports\Rapport de productivit?.xls")
Set wsMots = wbMots.Worksheets("Sheet2")


'Save and close section. This is at the very end of the code and both wb.SaveAs don't work now
wbTime.SaveAs Filename:="\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Raw Reports\Done\Heures_" & Replace(curWeek, "/", "-") & ".xls", FileFormat:="xlExcel8"
wbMots.SaveAs Filename:="\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Raw Reports\Done\Rapport de Productivit?_" & Replace(curWeek, "/", "-") & ".xls", FileFormat:="xlExcel8"
wbRP.Save
wbTdB.Save
wbTime.Close
wbMots.Close
wbRP.Close
wbTdB.Close

I am trying to save in a completed folder two reports and rename them with the week of the report. Instead I get the error code : 1004 - Error defined by the application or by the object

Upvotes: 1

Views: 2298

Answers (2)

LaZoR_Bear
LaZoR_Bear

Reputation: 97

I have solved the issue by removing the fileformat declaration in the wb.saveAs. Having the .xls gave the correct fileformat on its own.

Removing .xls and keeping the fileformat didn't work though.

I also removed a redundancy with my curWeek string when saving the file. The string format was already using ''-'' instead of ''/'' since an update to the formating. However that bit didn't show any interference with the saveAs function.

Thank you for your help

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71237

A failing Workbook.SaveAs call usually means there's a problem with the path or file name.

Separate the SaveAs member call from the gathering of the pieces you need to make that member call.

Namely, pull the Filename argument expression into a new local variable:

Dim newFilename As String
newFilename = "\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Raw Reports\Done\Rapport de Productivit?_" & Replace(curWeek, "/", "-") & ".xls"
Debug.Print newFilename
Stop

Now run this, and when execution stops you should be seeing the actual string value you were passing for a Filename argument (Ctrl+G to bring up the debug/immediate pane). Does that look like a valid path & filename? Copy it to the clipboard, go back to Excel and hit F12/SaveAs and try to save a file with that name.

You should be getting a detailed error message telling you exactly what's wrong with it.

The ? character is highly suspicious (it's illegal in filenames), and the \xxx\xxx\xxx placeholders look like the actual path might be rather deep - verify that the total length is under 255 characters.

Upvotes: 2

Related Questions