如何将SharePoint列表导入Excel VBA？

Question

使用下面的代码导入使用Excel VBA从SharePoint数据，以下错误出现：

运行时错误“1004”：
发生意外错误。更改您的数据无法保存

Sub ImportSharePointList() 

    Dim objMyList As ListObject 
    Dim objWksheet As Worksheet 
    Dim strSPServer As String 


    Const SERVER As String = "<server>:<Port>/Location/Lists" 
    Const LISTNAME As String = "{4e78c371-9ff0-410d-86a5-b78499091369}" 
    Const VIEWNAME As String = "{C3BD0F82-6BCD-45EF-B43E-DCFEA78067B0}" 

    strSPServer = "http://" & SERVER & "/_vti_bin" 

    Set objWksheet = Worksheets.Add 

    Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _ 
     Array(strSPServer, LISTNAME, VIEWNAME), False, xlYes, Range("A1")) 

End Sub 

Answer 1

像这样的东西应该为你工作。

Sub ImportSharePointList() 

    Dim objMyList As ListObject 
    Dim objWksheet As Worksheet 
    Dim strSPServer As String 
    Const SERVER As String = "mysite:8003/sites/the-sharepoint-thing/" 
    Const LISTNAME As String = "{XXXXXXXXXXXX}" 
    Const VIEWNAME As String = "{XXXXXXXXXXXX}" 

    ' The SharePoint server URL pointing to 
    ' the SharePoint list to import into Excel. 
    strSPServer = "http://" & SERVER & "/_vti_bin" 
    ' Add a new worksheet to the active workbook. 
    Set objWksheet = Worksheets.Add 
    ' Add a list range to the newly created worksheet 
    ' and populated it with the data from the SharePoint list. 
    Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _ 
     Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A1")) 

End Sub