excel - 使用 vba 从 Sharepoint 文件夹和子文件夹中获取 excel 文件路径

Question

大家好，我想问一个问题：我怎样才能从文件夹sharepoint中获取所有的excel文件（也可以从该文件夹的子文件夹中获取excel文件）？我已经使用了这段代码，它非常适合本地路径，但是当我输入共享点 URL 时它不起作用我感谢你的帮助：

Sub extraerRuta()
Dim xIsSubfolders As Boolean
xIsSubfolders = True
'Specify File Path
spath = "https://integralsa-my.sharepoint.com/:f:/r/personal/jalastra_integral_com_co/Documents/Archivos%20de%20chat%20de%20Microsoft%20Teams?csf=1&web=1&e=DJ8lSz"
spath2 = "C:\Users\1017249739\Desktop\JALG\JALGINT\Instrumentación\DOCUMENTACION"
xFolderName = spath


Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim xFile As Object
Dim xFile2 As Object
Dim rowIndex As Long
Dim pathf()
Dim X As Single
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.getfolder(xFolderName)


X = 0
ReDim Preserve pathf(X)
For Each xFile In xFolder.Files
    If xFile.Name Like "*.xlsx*" Then
        Debug.Print xFile.Path
        ReDim Preserve pathf(0 To X)
        pathf(X) = xFile.Path
        X = X + 1
    Else

    End If

        Next xFile
  For Each xSubFolder In xFolder.subfolders
    Debug.Print xSubFolder
        Set xSubFolder2 = xFileSystemObject.getfolder(xSubFolder)

        For Each xFile2 In xSubFolder2.Files
            If xFile2.Name Like "*.xlsx*" Then
                Debug.Print xFile2.Path
                ReDim Preserve pathf(0 To X)
                pathf(X) = xFile2.Path
                X = X + 1
            Else

                End If
        Next xFile2
  Next xSubFolder



Set xFile = Nothing
Set xFolder = Nothing
Set xFileSystemObject = Nothing
End Sub

spath 无效，但 spath2 有效。

标签： excelvbaarraylistsharepointpath