VBA使用excel VBA FileSystemObject访问Sharepoint

Question

我正在尝试访问Sharepoint中的一个安全文件夹(https(。我在excel中使用一个小VBA宏，它使用文件系统对象。请参阅下面在类似线程中找到的代码。宏将打开文件夹中的.csv文件并扫描某个字符串。

有时它会起作用，但有时它会说"找不到路径"。在这种情况下，我会用Microsoft Explorer(浏览器(打开共享点网站，然后一切都会起作用。现在探险家已经退役了，我再也不能在探险家中打开那条路了。

有什么建议吗？我知道映射/同步共享点是另一种选择，但我希望我的工具能被不同的用户使用，我想映射/同步会使新路径依赖于用户。

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(//companyname.sharepoint.com@ssl/sites/Invoices/Shared Documents/Tre/daily)
For each oFile in oFolder.Files
...etc etc.

谢谢Marthomas

Answer 1

虽然IE在用户界面中不可用，但您仍然可以在宏中使用它，因此可以使用IE登录SharePoint并使用SharePoint。

Answer 2

我尝试通过filesystemobject访问sharepoint文件。找不到办法。我认为用filesystemobject访问sharepoint是不可能的。

我将使用其他excel功能访问我的文件。其他excel方法可能对sharepoint文件有效。

Sub SharePoint()
Dim strTestFolder As String, strTestBook As String, MyBook As Workbook
strTestFolder = "https://XXXXXXXX.sharepoint.com/sites/msteams_YYYYYYYY/Shared%20Documents/General/TEST/"
strTestBook = "NewBook.xlsx"
'This works well.
Set MyBook = Workbooks.Open(Filename:=strTestFolder & strTestBook)
MyBook.Sheets(1).Cells(1, 1) = Date
MyBook.Sheets(1).Cells(2, 1) = Time
MyBook.Save
MyBook.Close
'My Excel can open workbooks in sharepoint folder
Dim FSO As Object, objMyFolder As Object, objFile As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
'Check my workbook with FSO
If FSO.FileExists(strTestFolder & strTestBook) Then 'Same name as Workbooks.Open
MsgBox "File Exists"
Else
MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If

'Convert path Patern1
strTestFolder = "//XXXXXXXX.sharepoint.com/sites/msteams_YYYYYYYY/Shared%20Documents/General/TEST/"
If FSO.FileExists(strTestFolder & strTestBook) Then
MsgBox "File Exists"
Else
MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If
'Convert path Patern2
strTestFolder = "\XXXXXXXX.sharepoint.comsitesmsteams_YYYYYYYYShared%20DocumentsGeneralTEST"
If FSO.FileExists(strTestFolder & strTestBook) Then
MsgBox "File Exists"
Else
MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If
'Convert path Patern3
strTestFolder = "https://XXXXXXXX.sharepoint.com/sites/msteams_YYYYYYYY/Shared Documents/General/TEST/"
If FSO.FileExists(strTestFolder & strTestBook) Then 'Same name as Workbooks.Open
MsgBox "File Exists"
Else
MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If
'Convert path Patern4
strTestFolder = "//XXXXXXXX.sharepoint.com/sites/msteams_YYYYYYYY/Shared Documents/General/TEST/"
If FSO.FileExists(strTestFolder & strTestBook) Then
MsgBox "File Exists"
Else
MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If
'Convert path Patern5
strTestFolder = "\XXXXXXXX.sharepoint.comsitesmsteams_YYYYYYYYShared DocumentsGeneralTEST"
If FSO.FileExists(strTestFolder & strTestBook) Then
MsgBox "File Exists"
Else
MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If

结束子