使用VBA获取OneDrive文件夹中的Excel文件列表
使用VBA获取OneDrive文件夹中的Excel文件列表
提问于 2021-06-09 21:57:24
在中，我记录了一个宏来打开OneDrive上的一个文件，它生成的代码在下面工作得很好：
Workbooks.Open Filename:= "https://mycopmanymy.sharepoint.com/personal/john/Documents/John/Shared/Support/SDM%20Rebates%20v30.xlsm"问题是，要使其正常工作，程序必须准确地知道文件名。我希望VBA能够扫描这个特定的文件夹并打开每个文件，所以我只是删除了文件名，使用了相同的URL，并使用了以下代码：
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("https://mycopmanymy.sharepoint.com/personal/john/Documents/John/Shared/Support/")
For Each oFile In oFolder.Files
Debug.print(oFile.Name)
Next这给了我路径找不到错误。请注意，我不想使用本地C: path，因为它的思想是用户将文件放入共享文件夹并在其端运行宏(即我的本地路径可能与他们的本地路径不同)。
谢谢!
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-07-17 17:05:39
我在这个链接中找到了解决方案：
它可能需要稍加调整，但它列出了指定sharepoint文件夹中的所有文件。
注意，您必须更改这一行代码以适应您的公司的url：
`SharepointAddress = "https://abc.onmicrosoft.com/TargetFolder/"`
Sub DownloadListFromSharepoint()
Dim SharepointAddress As String
Dim LocalAddress As String
Dim objFolder As Object
Dim objNet As Object
Dim objFile As Object
Dim FS As Object
Dim rng As Range
SharepointAddress = "https://abc.onmicrosoft.com/TargetFolder/"
Set objNet = CreateObject("WScript.Network")
Set FS = CreateObject("Scripting.FileSystemObject")
objNet.MapNetworkDrive "A:", SharepointAddress
Set objFolder = FS.getfolder("A:")
Set rng = ThisWorkbook.Worksheets(1).Range("a1")
rng.Value = "File Name"
rng.Offset(0, 1).Value = "Folder/File"
rng.Offset(0, 2).Value = "Path"
GetAllFilesFolders rng, objFolder, "" & strSharepointAddress
objNet.RemoveNetworkDrive "A:"
Set objNet = Nothing
Set FS = Nothing
End Sub
Public Sub GetAllFilesFolders(rng As Range, ObjSubFolder As Object, strSharepointAddress As String)
Dim objFolder As Object
Dim objFile As Object
For Each objFile In ObjSubFolder.Files
rng.Offset(1, 0) = objFile.Name
rng.Offset(1, 1) = "File"
rng.Offset(1, 2) = Replace(objFile.Path, "A:\", SharepointAddress)
Set rng = rng.Offset(1, 0)
Next
For Each objFolder In ObjSubFolder.subfolders
rng.Offset(1, 0) = objFolder.Name
rng.Offset(1, 1) = "Folder"
rng.Offset(1, 2) = Replace(objFolder.Path, "A:\", SharepointAddress)
Set rng = rng.Offset(1, 0)
GetAllFilesFolders rng, objFolder, strSharepointAddress
Next
End SubStack Overflow用户
发布于 2021-06-10 00:59:33
从上面的链接引用SharePointURLtoUNC，您可以尝试如下：
Sub TT()
Dim f As String, oFSO, oFolder, oFile
f = "https://mycopmanymy.sharepoint.com/personal/john/Documents/John/Shared/Support/"
Debug.Print "URL", f
f = SharePointURLtoUNC(f)
Debug.Print "UNC", f
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(f)
For Each oFile In oFolder.Files
Debug.Print oFile.Name
Next
End Sub
Public Function SharePointURLtoUNC(sURL As String) As String
Dim bIsSSL As Boolean
bIsSSL = InStr(1, sURL, "https:") > 0
sURL = Replace(Replace(sURL, "/", "\"), "%20", " ")
sURL = Replace(Replace(sURL, "https:", vbNullString), "http:", vbNullString)
sURL = Replace(sURL, Split(sURL, "\")(2), Split(sURL, "\")(2) & "@SSL\DavWWWRoot")
If Not bIsSSL Then sURL = Replace(sURL, "@SSL\", vbNullString)
SharePointURLtoUNC = sURL
End Function页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67912197
复制相关文章
相似问题
腾讯云开发者
