title: Excel VBA Up-/Download from multiple SharePoint folders
body: <p>I found sample code on the Internet for downloading files from a SharePoint folder using VBA (open in Explorer, map to drive letter, etc.)</p>
  
  <p>Therefore, I wrote the following code:</p>
  
  <pre><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:\\" &amp; "." &amp; "\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 &gt; j Then
              driveLetter = Chr(i) &amp; ":"
              Exit For
          End If
      Next i
      'If a Drive-Letter is found exit the loop
      If driveLetter &lt;&gt; "" 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)
  </code></pre>
  
  <p>At this stage I'm able to upload files to the folder:</p>
  
  <pre><code>https://spFolder/Sector Reports/
  </code></pre>
  
  <p>However, I also want to upload files to the folder e.g.:</p>
  
  <pre><code>https://spFolder/Documents/
  </code></pre>
  
  <p>and I also removed the previous drive letter using the function:</p>
  
  <pre><code>removeDriveLetter "Letter" 
  </code></pre>
  
  <p>Now I have the problem, that if I map a new folder to a letter:</p>
  
  <pre><code>mapDriveLetter "A:\", sharepointFolder
  </code></pre>
  
  <p>and want to save something on this letter, it will always be saved on the previous path. For example:</p>
  
  <pre><code>mapDriveLetter "A:\", sharePointFolder1
  removeDriveLetter "A:\"
  mapDriveLetter "A:\", sharePointFolder2
  workbook.saveas "A:\" &amp; workbookName
  </code></pre>
  
  <p>In this case the workbook is always saved to the path given in "sharePointFolder1" and not this in "sharePointFolder2".</p>