Hi guys, seen a few posts on here about a similar issue but none of the fixes worked for me.
I have some existing code that takes the open file, copies the sheet(s) to a new book and saves under a new name (and some other code that performs similar tasks). All working fine until I had to move from Dropbox to OneDrive for business last week.
I synced the OD documents with my OD account and it now appears as a local folder, so updated the folder path in the module. This appeared to work fine at first.
Soon I started to receive 1004 errors - Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed.
I changed all the paths to the https address as found when recording the steps with the macro recorder. Same outcome.
Oddly the open workbook updates with the new save name, the file saves locally and appears in 365 online, but I still get the error and when exiting the sheet it asks me to save changes. If I do it overwrites the original sheet.
As a fix I have added On Error Resume Next and this sits for a bit showing the upload to OD dialog box, then exits the sub as normal. My theory here is that there is a delay wile the workbook uploads resulting in the 1004 error. I'm a bit of a novice with VBA so cannot be sure.
Is there a known issue, am I doing something wrong, and can anyone help get round this? I think maybe a wait command or check if uploaded/saved could work but not sure of the code to use.
Maybe a better use of the on error resume code with some checks in?
Would greatly appreciate some help on this.
Thanks,
Barney
Here is some example code
I have some existing code that takes the open file, copies the sheet(s) to a new book and saves under a new name (and some other code that performs similar tasks). All working fine until I had to move from Dropbox to OneDrive for business last week.
I synced the OD documents with my OD account and it now appears as a local folder, so updated the folder path in the module. This appeared to work fine at first.
Soon I started to receive 1004 errors - Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed.
I changed all the paths to the https address as found when recording the steps with the macro recorder. Same outcome.
Oddly the open workbook updates with the new save name, the file saves locally and appears in 365 online, but I still get the error and when exiting the sheet it asks me to save changes. If I do it overwrites the original sheet.
As a fix I have added On Error Resume Next and this sits for a bit showing the upload to OD dialog box, then exits the sub as normal. My theory here is that there is a delay wile the workbook uploads resulting in the 1004 error. I'm a bit of a novice with VBA so cannot be sure.
Is there a known issue, am I doing something wrong, and can anyone help get round this? I think maybe a wait command or check if uploaded/saved could work but not sure of the code to use.
Maybe a better use of the on error resume code with some checks in?
Would greatly appreciate some help on this.
Thanks,
Barney
Here is some example code
VBA Code:
Sub Save_Sheets()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
'copy to new sheet, save
Sheets(Array("list1 (2)", "list2 (2)", "list3 (2)", "list4 (2)", "list5 (2)", "list6 (2)")).Copy
Sheets("list1 (2)").Name = "Stock1"
Sheets("list2 (2)").Name = "Stock2"
Sheets("list3 (2)").Name = "Stock3"
Sheets("list4 (2)").Name = "Stock4"
Sheets("list5 (2)").Name = "Stock5"
Sheets("list6 (2)").Name = "Stock6"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
'save
On Error Resume Next
ActiveWorkbook.SaveAs FileName:= _
"sharepoint url here/ "stock list Main & Format(Date, "DDMMYY") & "" _
, FileFormat:=xlExcel12, CreateBackup:=False
ActiveWorkbook.AutoSaveOn = False
ActiveWorkbook.Save
' old code local path
'ActiveWorkbook.SaveAs FileName:= _
"C:\Users\Me\Desktop\stock list Main " & Format(Date, "DDMMYY") & "" _
, FileFormat:=50
End Sub
