CODEWITHSUNDEEP
vbaexcel
Adije
Adije

Reputation: 15

Method 'SaveAs' of object '_Workbook' failed VBA

I have a workbook that is connected to a database and when you refresh the workbook, it refreshes your data in the workbook. Afterwards a macro is ran to save the new (raw) data in two different locations, then the macro will do analysis on that raw data, and save the same workbook later on in 4 other locations. When I am trying to do the first save of the raw data, I am able to save to the first location, but when it moves to the second location I am getting the error: Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed. Both of the locations are valid locations.

Here is one sub in the macro that does the first file save:

Sub saveraw()

Dim rdate As String
Dim rawfilename As String, rawfilename1 As String
Dim mywb As Workbook

Set mywb = ActiveWorkbook

rdate = Format(Now(), "mm-dd-yy")

rawfilename = "\\sw\mes\PS\SC\SCM_Supply_Execution\Spares\This Weeks Number Database\This Weeks Numbers(raw) " & rdate & ".xlsm"
rawfilename1 = "\\sw\mes\PS\SC\SCM_Shared\Spares Reports\This Weeks Numbers(raw) " & rdate & ".xlsm"

mywb.Save

Worksheets("Sheet2").Range("A2") = Null

Application.DisplayAlerts = False

mywb.SaveAs Filename:=rawfilename
mywb.SaveAs Filename:=rawfilename1

Application.DisplayAlerts = True

End Sub

I have also tried using the method of

ActiveWorkbook.SaveAs

in place of both of the mywb.SaveAs, but that did not work either.

Upvotes: 0

Views: 6515

Answers (2)

J3NKO
J3NKO

Reputation: 76

As a test, run the code against file locations only YOU have access to. I'm noticing a reference to shared drive on the second command so maybe someone is already in it and it can't be saved over.

Set display alerts to true for now.

Also, When you run the test macro, make sure you don't have any windows explorer windows open and feed back here with results and I'll assist if it debugs again.

On the face of it, the code looks good to me.

Upvotes: 1

Raunak Thomas
Raunak Thomas

Reputation: 1403

Try this

ActiveWorkbook.SaveAs Filename:="\\sw\mes\PS\SC\SCM_Supply_Execution\Spares\This Weeks Number Database\This Weeks Numbers(raw) " & rdate & ".xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled

This might have issues if the file already exists because then a pop-up will ask you if you want to overwrite it or not

Though I think the error is due to an issue with privileges in that specific location or connection issues

Upvotes: 0

Related Questions