VBA & OneDrive: SaveAs to OneDrive results in 1004 error

BarneyLTD

New Member
Joined
Sep 27, 2017
Messages
27
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

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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
All I can see when I compared your code to VBA I use to open a sharepoint file using VBA is that %20 is used instead of spaces.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top