I've been trying to save files on to my Share Drive but I'm getting a run-time 1004 error.

For context, I'm working freelance with an anization using my personal Outlook account and part of my role involves saving and interacting with other files within the company's Share Drive using macros in a workbook within this Share Drive. However, having tried multiple strategies, I'm still unable to perform simple macros. An example below was taken directly from the Microsoft Community website which should save the current file into the current folder. However, it triggers a Run-Time 1004 error instead:

My 'Home' string is formatted like: /:f:/r/sites/GroupDrive/AdSales/Pricing%20Calculator?csf=1&web=1&e=YLfIPX......

Sub MakeBackup()
Dim Home As String
Dim HomePath As String
Dim BackupPath As String
Home = ThisWorkbook.Path
HomePath = Home & "/myfile.xlsm"
On Error GoTo ErrorHandler
'Save this file to the current folder and the new file to a new location
ActiveWorkbook.SaveAs fileName:=HomePath, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Save Error"
End
End Sub

Any support / suggestions would be greatly appreciated.

I've been trying to save files on to my Share Drive but I'm getting a run-time 1004 error.

For context, I'm working freelance with an anization using my personal Outlook account and part of my role involves saving and interacting with other files within the company's Share Drive using macros in a workbook within this Share Drive. However, having tried multiple strategies, I'm still unable to perform simple macros. An example below was taken directly from the Microsoft Community website which should save the current file into the current folder. However, it triggers a Run-Time 1004 error instead:

My 'Home' string is formatted like: https://skynz.sharepoint/:f:/r/sites/GroupDrive/AdSales/Pricing%20Calculator?csf=1&web=1&e=YLfIPX......

Sub MakeBackup()
Dim Home As String
Dim HomePath As String
Dim BackupPath As String
Home = ThisWorkbook.Path
HomePath = Home & "/myfile.xlsm"
On Error GoTo ErrorHandler
'Save this file to the current folder and the new file to a new location
ActiveWorkbook.SaveAs fileName:=HomePath, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Save Error"
End
End Sub

Any support / suggestions would be greatly appreciated.

• excel
• vba

• 2 The path you might get from "copy link" is not always suitable for saving a file from VBA - you need to clean out "flags" like :f/r/ for example and remove the querystring part (everything after ?) So more like https://skynz.sharepoint/sites/GroupDrive/AdSales/Pricing%20Calculator/myFile.xlsm – Tim Williams Commented Apr 1 at 17:48
• When multiple users work with files on a shared drive, consider adding version control mechanisms to prevent data loss or overwriting changes. – user80346 Commented Apr 1 at 20:05
• Expanding on my earlier comment: see for example - stackoverflow/a/68776113/478884 and linked from there is techcommunity.microsoft/discussions/sharepoint_general/… – Tim Williams Commented Apr 1 at 23:53

1 Answer 1

I haven't learned much about this because I am still learning this, but it's probably due to file permissions, network connectivity, security settings, or Excel add-ins.

As said in the "Excel Macro-Enabled Workbook won't Save to a Network Folder" question the Microsoft Forums, it could be a Windows side issue.

Based on the network error messages you mentioned, it may be a Windows side issue as well. I suggest you try the following to see if you can save the file successfully.

1. Map the shared folder as a network drive and drag the affected macro-enabled file.

2. Run the Windows robocopy commands to transfer and save the file.

If none works, then try creating a new workbook and copy your macro code to it and then try saving it to the shared drive.