excel - Why can't I save file to Share Drive using macros? - Stack Overflow

admin管理员组

文章数量:1351116

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

Share Improve this question Follow edited Apr 1 at 17:49 Tim Williams 167k88 gold badges100100 silver badges141141 bronze badges asked Apr 1 at 12:20 Danny O'SullivanDanny O'Sullivan 111 bronze badge New contributor Danny O'Sullivan is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 3

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default -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.

本文标签： excelWhy can39t I save file to Share Drive using macrosStack Overflow