Excel VBA从/多个SharePoint文件夹下载 [英] Excel VBA Up-/Download from multiple SharePoint folders

问题描述

我在Internet上发现了使用VBA从SharePoint文件夹下载文件的示例代码（在资源管理器中打开，映射到驱动器盘符等）。

因此，我写了以下代码：

  Dim sharepointFolder As String 
 Dim colDisks As Variant 
 Dim objWMIService As Object 
 Dim objDisk As Variant 
 Dim driveLetter As String 
 
'创建FSO和网络对象
设置objNet = CreateObject（WScript.Network）
设置fs = CreateObject（Scripting.FileSystemObject）
 
'获取所有使用的Drive-Letters 
设置objWMIService = GetObject（winmgmts：\\&。&\\ 
设置colDisks = objWMIService.ExecQuery（从Win32_LogicalDisk中选择*
 
'循环使用的Drive-Letters 
对于每个objDisk在colDisks 
对于i = 65到90 
'如果信使用退出循环并记住信。 
如果i = Asc（objDisk.DeviceID）然后
 j = i 
退出对于
'不检查的字母可能只有
 ElseIf i> j然后
 driveLetter = Chr（i）& ：
退出
结束如果
下一个i 
'如果发现驱动器盘符退出循环
如果driveLetter<> 然后
退出
结束如果
下一个
 
'定义到SharePoint的路径
 sharepointFolder =https：// spFolder / Sector Reports / 
'将sharePoint文件夹映射到可用的Drive-Letter 
 objNet.MapNetworkDrive driveLetter，sharepointFolder 
'将文件夹设置为映射的SharePoint-Path 
设置文件夹= fs.GetFolder（ driveLetter）
  

在这个阶段，我可以将文件上传到文件夹：

  https：// spFolder / Sector Reports / 
  

但是，我也想将文件上传到文件夹，例如：

  https：// spFolder /文件/ 
  

我也使用以下功能删除了以前的驱动器号：

  removeDriveLetterLetter
  

现在我有问题，如果我将一个新的文件夹映射到一个字母：

  mapDriveLetterA：\，sharepointFolder 
  

并且想要保存在这封信上的东西，它将始终保存在上一个路径上。例如：

  mapDriveLetterA：\，sharePointFolder1 
 removeDriveLetterA：\
 mapDriveLetterA：\，sharePointFolder2 
 workbook.saveasA：\& workbookName 
  

在这种情况下，工作簿始终保存到sharePointFolder1中给出的路径，而不是在sharePointFolder2。

解决方案

我解决了以下问题：

如果将包含请求的文件的每个文件夹连接到网络驱动器盘符，我将所有文件夹都放入同一个库，并将库的根文件夹连接到网络驱动器号。

如果连接
https：// spFolder / Sector Reports / 分别 https：// spFolder / Documents /
我将只连接 https：// spFolder / ，并使用文件系统对象浏览子目录。 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.)

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".

解决方案

i solved this issue as followed:

In case of connecting every folder, which contains the requested files, to a network drive letter, I put all folders into the same library and connected the root folder of the library to a network drive letter only.

In case of connection https://spFolder/Sector Reports/ respectively https://spFolder/Documents/ I will connect https://spFolder/ only and browse through the subdirectory using the File System Object.

这篇关于Excel VBA从/多个SharePoint文件夹下载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！