Copy a document from a SharePoint location to another SharePoint location using Excel VBA

[willow1985]

I am having a very difficult time finding a way to copy an excel document from one location in SharePoint to Another location in SharePoint. I hope someone can help me with this. I currently have a code that can copy a file from a network drive to another network drive but no idea how to do this within SharePoint.

Here is the information:

Source file location on SharePoint: https://mygroup.sharepoint.com/sites/MyStorageTestGroup1/Shared Documents/Marcos Sheet/OPS Forms
Destination location on SharePoint: https://mygroup.sharepoint.com/site... Documents/Marcos Sheet/Quality Records/Q4169
Source File Name: OPS 097 Rev 6 Non Conformance Report.xlsx

Below is my code but I honestly have no idea if I am even close or if I am even missing a "/" or something simple.

Any help would be appreciated

Private Sub Transfer_NCR()



Dim sDocPath As String

Dim sFileName As String

Dim sTargetPath As String

Dim sSourcePath As String

Dim sDriveLetter As String

Dim network As Object

Dim folderPath As String

Dim folderName As String

Dim driveLetter As String

Dim fs As Object



' Create instance of WScript.Network object

Set network = CreateObject("WScript.Network")

' Specify the SharePoint site URL

Dim sharepointURL As String

sharepointURL = Workbooks("Quality Log").Sheets("List").Range("C29").Value

' drive letter should be available (not mapped to a share already, to avoid error '80070055 - The local device is already in use')

sDriveLetter = "R:"

' Map the SharePoint document library to a network drive

On Error Resume Next

network.MapNetworkDrive driveLetter, sharepointURL

If Err.Number <> 0 Then

MsgBox "Failed to map network drive. Error: " & Err.Description, vbCritical

GoTo UnmapDrive

End If

On Error GoTo 0

sFileName = Workbooks("Quality Log.xlsm").Sheets("List").Range("C32").Value

Set fs = CreateObject("Scripting.FileSystemObject")

sDocPath = ThisWorkbook.path

network.MapNetworkDrive sDriveLetter, sDocPath

sSourcePath = sDriveLetter & Workbooks("Quality Log.xlsm").Sheets("List").Range("C30").Value & "/" & sFileName

Debug.Print Workbooks("Quality Log.xlsm").Sheets("List").Range("C30").Value & sSourcePath



sTargetPath = sDriveLetter & Workbooks("Quality Log").Sheets("List").Range("C34").Value & "/" & sFileName

Debug.Print Workbooks("Quality Log").Sheets("List").Range("C31").Value & sTargetPath



fs.CopyFile sSourcePath, sTargetPath, True

network.RemoveNetworkDrive sDriveLetter



UnmapDrive:

'Unmap the network drive

network.RemoveNetworkDrive sDriveLetter, True, True

Set network = Nothing

Set fs = Nothing

End Sub

Quality Log.xlsm
	C
29	https://mygroup.sharepoint.com/sites/MyStorageTestGroup1/Shared%20Documents/Marcos%20Sheet
30	https://mygroup.sharepoint.com/sites/MyStorageTestGroup1/Shared%20Documents/Marcos%20Sheet/OPS%20Forms
31	https://mygroup.sharepoint.com/sites/MyStorageTestGroup1/Shared%20Documents/Marcos%20Sheet/Quality%20Records/Q4169
32	OPS 097 Rev 6 Non Conformance Report.xlsx
33	Q4169
34	/Quality%20Records/Q4169
List

 

[PeteWright]

Hi @willow1985
Since you're dealing with cloud storage (SharePoint) you may run into many difficulties.
I managed to modify and adapt your code to suit my needs, but got stuck very early at the drive mapping with an error telling me that the user is not authenticated.
After a short research on this error I found an MS article where editing registry entries seems to be the solution, but I don't want to go this way.
Therefore I did another research on "Excel SharePoint file path" and found something interesting yet little more complex:
EXCEL-VBA: Proper formatting of the SharePoint URL/network file copy destination
Maybe that's worth a try.

Meanwhile, just a question: Do you insist on using Excel VBA for this task? There may be special tools out there to handle this task and if it's only synchronization-related I can give you advice.
But first let me know how we should go on.

 

[willow1985]

Hi @willow1985
Since you're dealing with cloud storage (SharePoint) you may run into many difficulties.
I managed to modify and adapt your code to suit my needs, but got stuck very early at the drive mapping with an error telling me that the user is not authenticated.
After a short research on this error I found an MS article where editing registry entries seems to be the solution, but I don't want to go this way.
Therefore I did another research on "Excel SharePoint file path" and found something interesting yet little more complex:
EXCEL-VBA: Proper formatting of the SharePoint URL/network file copy destination
Maybe that's worth a try.

Meanwhile, just a question: Do you insist on using Excel VBA for this task? There may be special tools out there to handle this task and if it's only synchronization-related I can give you advice.
But first let me know how we should go on.

I do not seem to have any problems with mapping/have not received any errors on that part in relation to users, but I do have problems with the code not finding the document to copy and cannot figure out why. I do have to use Excel VBA for this task because my employer wants it done this way. The excel document runs a macro that creates a file but then needs to copy a template from one SharePoint folder location to the file that was just created. Then it opens that document and fills it out and so on. I have figured out how to create the file, open documents and fill them out but I am stuck on the document copy portion where it is not finding the document and I am not sure where I am going wrong or even if I am missing a "/". A second set of eyes would be much appreciated.

 

[PeteWright]

hi @willow1985
I think I have figured it out now. Try this code:

Private Sub Transfer_NCR()
   Dim nw As Object: Set nw = CreateObject("WScript.Network")
   Dim fs As Object: Set fs = CreateObject("Scripting.FileSystemObject")
   Dim ws As Worksheet: Set ws = Workbooks("Quality Log.xlsm").Sheets("List")
   Dim sharepointURL As String: sharepointURL = ws.Range("C29").Value
   Dim driveLetter As String: driveLetter = "R:"
   Dim sourceFolder As String: sourceFolder = ws.Range("C30").Value
   Dim targetFolder As String: targetFolder = ws.Range("C31").Value
   Dim fileNameExt As String: fileNameExt = ws.Range("C32").Value
   Dim sourcePath As String: sourcePath = driveLetter & "\" & sourceFolder & "\" & fileNameExt
   Dim targetPath As String: targetPath = driveLetter & "\" & targetFolder & "\" & fileNameExt

   Debug.Print "SharePoint URL: " & vbCrLf & sharepointURL

   On Error Resume Next
   nw.MapNetworkDrive driveLetter, sharepointURL
   If Err.Number <> 0 Then
      MsgBox "Failed to map network drive. Error: " & Err.Description, vbCritical
      GoTo UnmapDrive
   End If
   On Error GoTo 0
   If Not fs.FolderExists(driveLetter & "\" & sourceFolder) Then
      MsgBox "Specified source folder doesn't exist. Exiting."
      GoTo UnmapDrive
   End If
   If Not fs.FolderExists(driveLetter & "\" & targetFolder) Then
      MsgBox "Specified target folder doesn't exist. Exiting."
      GoTo UnmapDrive
   End If
   If Not fs.FileExists(driveLetter & "\" & sourceFolder & "\" & fileNameExt) Then
      MsgBox "Specified source file doesn't exist. Exiting."
      GoTo UnmapDrive
   End If
 
   Debug.Print "Trying to copy from: " & vbCrLf & sourcePath & vbCrLf _
   & "To: " & vbCrLf & targetPath
 
   fs.CopyFile sourcePath, targetPath, True
   nw.RemoveNetworkDrive driveLetter
   GoTo EndOfSub
 
UnmapDrive:
   nw.RemoveNetworkDrive sDriveLetter, True, True
   Set nw = Nothing
   Set fs = Nothing
EndOfSub:
End Sub

I cleaned up the code from unused variables and cell references, removed comments and shortened or renamed some variables

Please let me know if it worked.

 

[willow1985]

hi @willow1985
I think I have figured it out now. Try this code:

Private Sub Transfer_NCR()
   Dim nw As Object: Set nw = CreateObject("WScript.Network")
   Dim fs As Object: Set fs = CreateObject("Scripting.FileSystemObject")
   Dim ws As Worksheet: Set ws = Workbooks("Quality Log.xlsm").Sheets("List")
   Dim sharepointURL As String: sharepointURL = ws.Range("C29").Value
   Dim driveLetter As String: driveLetter = "R:"
   Dim sourceFolder As String: sourceFolder = ws.Range("C30").Value
   Dim targetFolder As String: targetFolder = ws.Range("C31").Value
   Dim fileNameExt As String: fileNameExt = ws.Range("C32").Value
   Dim sourcePath As String: sourcePath = driveLetter & "\" & sourceFolder & "\" & fileNameExt
   Dim targetPath As String: targetPath = driveLetter & "\" & targetFolder & "\" & fileNameExt

   Debug.Print "SharePoint URL: " & vbCrLf & sharepointURL

   On Error Resume Next
   nw.MapNetworkDrive driveLetter, sharepointURL
   If Err.Number <> 0 Then
      MsgBox "Failed to map network drive. Error: " & Err.Description, vbCritical
      GoTo UnmapDrive
   End If
   On Error GoTo 0
   If Not fs.FolderExists(driveLetter & "\" & sourceFolder) Then
      MsgBox "Specified source folder doesn't exist. Exiting."
      GoTo UnmapDrive
   End If
   If Not fs.FolderExists(driveLetter & "\" & targetFolder) Then
      MsgBox "Specified target folder doesn't exist. Exiting."
      GoTo UnmapDrive
   End If
   If Not fs.FileExists(driveLetter & "\" & sourceFolder & "\" & fileNameExt) Then
      MsgBox "Specified source file doesn't exist. Exiting."
      GoTo UnmapDrive
   End If
 
   Debug.Print "Trying to copy from: " & vbCrLf & sourcePath & vbCrLf _
   & "To: " & vbCrLf & targetPath
 
   fs.CopyFile sourcePath, targetPath, True
   nw.RemoveNetworkDrive driveLetter
   GoTo EndOfSub
 
UnmapDrive:
   nw.RemoveNetworkDrive sDriveLetter, True, True
   Set nw = Nothing
   Set fs = Nothing
EndOfSub:
End Sub

I cleaned up the code from unused variables and cell references, removed comments and shortened or renamed some variables

Please let me know if it worked.

I keep getting the below error "Specified source folder doesn't exist"

I do not know how to get VBA to work with SharePoint or if it is even possible.
I am obtaining the paths from Teams/SharePoint by copying the Path/direct link (as shown in the below pictures). Those paths are in Cells C29 to C34 as listed at the beginning of this post.

Is this a security issue and if so is there a work around? My IT department does not know how and I have no knowledge on how SharePoint works/how VBA can communicate with it to copy the OPS file.

Any further help figuring this out would be greatly appreciated. Many are stumped on this and if it cannot be done I would like to know that as well.

Thank you again for all of your help.

 

[PeteWright]

hi @willow1985
I'm pretty sure that the issue is the SharePoint path
Would you mind posting all your copied paths? Please exclude sensitive parts like company, names, etc.

For example I have created an Excel file to test the code on my company OneDrive (which is also accessible through company SharePoint)
The file is named "SharePointCopy.xlsm" and I saved it locally within my OneDrive Folder in the subfolder named "TEST" and so the path (excluding the company and my name) is as follows

https://*******.sharepoint.com/personal/*******/Documents/TEST/SharePointCopy.xlsm

The first set of hidden characters is the company's SharePoint Name and
the second set is my SharePoint user name.

Do your paths look similar? If not, that may be the point.

Please let me know.