Saving excel xlsm file not working due to error

Question

I have this macro which is updating some tables and it has been working fine for the past two weeks, however it suddenly started giving me 1004 runtime error - initially it was runtime error 1004 method saveas of object _workbook failedub SaveAs(), now it is simply Run Time Error 1004 “Application-Defined or Object-Defined Error.

I suspect is has something to do with saving the file as that's the point it gives me error, everything else works fine. I have separated the saving code in a different macro and its still giving this error.

I have tried to make the path string into one piece (Mth var is a variable we change each month of reporting and is fed off a sheet value but have had to add it manually for testing purpose). Initially Path and Filename missed the ("S") at the end as I thought the names might be conflicting with something in the library.

Please note, before the code was using ActiveWorkbook.Saveas and was working fine I am not sure it's a problem of Excel knowing which sheet or workbook to look at.

How do I fix this? Please see code below:

ThisWorkbook.Activate
Dim Mth As Integer
Mth = 4
Dim FileNames As String
Dim Paths As String
Dim Fullstring As String
Application.DisplayAlerts = False
Paths = "\\RL1VMFIL02\Finance$\Financial Management\SITES & SERVICES\Corporate\2020-21\C - Statements & Trends" & "\M" & Mth & "\"
FileNames = Format(Now(), "dd.mm.yy") & " Budget Statement & Trend M" & Mth & " - " & Format(Now(), "hh.mm") & ".xlsm"
Fullstring = Paths & FileNames
ThisWorkbook.Activate
ThisWorkbook.SaveAs Fullstring
Application.DisplayAlerts = True

End Sub

something is worng with the path. I would try to save the document in a very simple path to check everything is working, and then try to build dynamically the path yoy want, checking all the characters are OK and that the path exists! — rustyBucketBay
If your folder & "\M" & Mth & "\" does not exist you will get an error. First, I would change Paths to sPath and FileNames to fName less chance of an issue. Second test if your path exist using mkDir or FSO, and if it doesn't, create the directory before saving the file. — GMalc
I can't upvote your comments for some reason but this has fixed it, the Month path included a 0 so it should've been either Mth var = '04 rather than 4 or path "\M0" & Mth & "\". Either way, this is resolved many thanks for the support! — Stefan Ciobanu

Stefan Ciobanu Stefan Ciobanu · Accepted Answer · 2020-08-06T11:53:35

This has been fixed by making sure the path folder exists as adviced by @RustyBucketBay & @GMalc.

Explicitly, the Month path included a 0 so it should've been either Mth var = '04 rather than 4 or path "\M0" & Mth & ""

Many thanks guys for the help!

Saving excel xlsm file not working due to error

1 Answers