Excel VBA 从多个 SharePoint 文件夹上传/下载 [英] Excel VBA Up-/Download from multiple SharePoint folders
问题描述
我在 Internet 上找到了使用 VBA 从 SharePoint 文件夹下载文件的示例代码(在资源管理器中打开、映射到驱动器号等)
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:\" & "." & "
ootcimv2")
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
在这种情况下,工作簿总是保存到sharePointFolder1"中给出的路径,而不是sharePointFolder2"中的路径.
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.
在连接的情况下https://spFolder/Sector Reports/
分别 https://spFolder/Documents/
我将仅连接 https://spFolder/
并使用文件系统对象浏览子目录.
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屋!