CODEWITHSUNDEEP
excelvbaruntime-errorexcel-2010save-as
Stefan Ciobanu
Stefan Ciobanu

Reputation: 1

Saving excel xlsm file not working due to error

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

Upvotes: 0

Views: 1050

Answers (1)

Stefan Ciobanu
Stefan Ciobanu

Reputation: 1

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!

Upvotes: 0

Related Questions