Excel VBA Up- / Download from multiple SharePoint folders

Question

I found sample code on the Internet for downloading files from a SharePoint folder using VBA (open in Explorer, map drive letter, etc.).

So I wrote the following code:

Dim sharepointFolder As String Dim colDisks As Variant Dim objWMIService As Object Dim objDisk As Variant Dim driveLetter As String 'Create FSO and network object Set objNet = CreateObject("WScript.Network") Set fs = CreateObject("Scripting.FileSystemObject") 'Get all used Drive-Letters Set objWMIService = GetObject("winmgmts:\\" & "." & "\root\cimv2") Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk") 'Loop through used Drive-Letters For Each objDisk In colDisks For i = 65 To 90 'If letter is in use exit loop and remember letter. If i = Asc(objDisk.DeviceID) Then j = i Exit For 'letters which are not checked yet are possible only ElseIf i > j Then driveLetter = Chr(i) & ":" Exit For End If Next i 'If a Drive-Letter is found exit the loop If driveLetter <> "" Then Exit For End If Next 'define path to SharePoint sharepointFolder = "https://spFolder/Sector Reports/" 'Map the sharePoint folder to the free Drive-Letter objNet.MapNetworkDrive driveLetter, sharepointFolder 'set the folder to the mapped SharePoint-Path Set folder = fs.GetFolder(driveLetter) 

At this point, I can upload files to a folder:

 https://spFolder/Sector Reports/ 

However, I also want to upload files to a folder, for example:

 https://spFolder/Documents/ 

and I also deleted the previous drive letter using the function:

 removeDriveLetter "Letter" 

Now I have a problem that if I map the new folder to the letter:

 mapDriveLetter "A:\", sharepointFolder 

and you want to save something on this letter, it will always be saved on the previous path. For example:

 mapDriveLetter "A:\", sharePointFolder1 removeDriveLetter "A:\" mapDriveLetter "A:\", sharePointFolder2 workbook.saveas "A:\" & workbookName 

In this case, the book is always saved in the path specified in "sharePointFolder1", and not in "sharePointFolder2".

Answer 1

I solved this problem as follows:

If each folder containing the requested files is connected to the letter of the network drive, I put all the folders in one library and connect the root folder of the library only to the letter of the network drive.

In the case of a connection https://spFolder/Sector Reports/ respectively https://spFolder/Documents/ I will only connect https://spFolder/ and view the subdirectory using the file system object.