I found sample code on the Internet for downloading files from a SharePoint folder using VBA (open in Explorer, map to drive letter, etc.)
Therefore, 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 stage I'm able to upload files to the folder:
https://spFolder/Sector Reports/
However, I also want to upload files to the folder e.g.:
https://spFolder/Documents/
and I also removed the previous drive letter using the function:
removeDriveLetter "Letter"
Now I have the problem, that if I map a new folder to a letter:
mapDriveLetter "A:\", sharepointFolder
and 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 workbook is always saved to the path given in "sharePointFolder1" and not this in "sharePointFolder2".
