Hi,
I am struggling with Runtime 1004 Error "method 'saveas' of object '_workbook' failed" when trying to save a copy of my workbook. I looked through many of similar posts here, but any solution helped. What I am basically trying to do is to save a copy of my 'master' file every time it refreshes Power Query and connected Pivot Tables... The code stops on .SaveAs line. Please see full code:
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Set Sourcewb = ThisWorkbook
Dim rng As Range
Set rng = Sourcewb.Sheets("Parameters").ListObjects("TowersT").ListColumns(1).DataBodyRange
Dim Tower As Range
For Each Tower In rng
Tower.Copy
Range("B2").PasteSpecial xlPasteValues
'Refresh Power Query queries
Sourcewb.RefreshAll
'Refresh Pivot Tables
Sourcewb.Worksheets("ActivityReport").PivotTables("ActivityPT").PivotCache.Refresh
DoEvents
Application.CalculateUntilAsyncQueriesDone
Sourcewb.Worksheets("UtilizationReport").PivotTables("UtilizationPT").PivotCache.Refresh
DoEvents
Application.CalculateUntilAsyncQueriesDone
Dim sFilename As String
sFilename = "Activity_Report_" & Tower.Value & "_" & Year(Date) & "_" & Left(MonthName(Month(Date), False), 3)
Application.Calculate
Sourcewb.Sheets.Copy
Set Destwb = ActiveWorkbook
'Declare SharePoint path to SaveAs
Dim Spath As Range
Set Spath = Destwb.Sheets("Parameters").ListObjects("Parameters").DataBodyRange(3, 2)
Destwb.Worksheets("Admin").Visible = xlVeryHidden
Destwb.Worksheets("Parameters").Visible = xlVeryHidden
'Disconnect Power Query queries
Set parts = Destwb.CustomXMLParts.SelectByNamespace("http://schemas.microsoft.com/DataMashup")
For Each part In parts
part.Delete
Next part
'Save to the SharePoint
Application.DisplayAlerts = False
With Destwb
.SaveAs Spath & "/" & sFilename & ".xlsm", FileFormat:=52, CreateBackup:=False '<<< ERROR 1004 HERE
.Close SaveChanges:=False
End With
Application.DisplayAlerts = True
Next Tower
What is interesting, when I am pressing F8, the code actually saves the file into given location (SharePoint path staring with "https://sp. ..."), so the path seems to be ok.
Any clues what might be wrong here?
Bookmarks