Run-time error '1004' on importing SharePoint list into Excel

yukorin · August 16th, 2010, 02:17 PM

I am trying to import data from SharePoint list into the existing Excel workbook. I am taking this approach since "Exporting into Excel" function of SharePoint list deletes all of hte VBA code and ActiveX controls containd in the workbook.
When I executed the following code, I received Run-time error '1004': Application-defined or object-defined error. Any idea to make it work?

Code:

Sub ImportSharePointList()

    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Dim strSPServer As String
    Const SERVER As String = "www.tc2.mycompany.com/ts/EESE/EDSCore"
    Const LISTNAME As String = "{5D21CF4A-30CC-4E35-826F-A12A71A425CB}"
    Const VIEWNAME As String = "{B270C78D-C089-4BF8-B7A8-63000FCAB8F0}"
    
   ' The SharePoint server URL pointing to
   ' the SharePoint list to import into Excel.
    strSPServer = "https://" & 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(stServer, LISTNAME, VIEWNAME), True, , Range("A1"))
    
    Set objMyList = Nothing
    Set objWksheet = Nothing
    
End Sub

yukorin · August 16th, 2010, 03:19 PM

I have found that parameter name for server was spelled wrong in the sample. The corrected code worked fine. Thank you.

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

Ragnaros · December 10th, 2012, 03:17 PM

I am trying to do the same thing that you're doing but I am getting an error on the "Set objMyList = ..." line. Any idea why?

Code:

Sub ImportSharePointList()

    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Dim strSPServer As String
    
    Const SERVER As String = "REMOVED.com/REMOVED/REMOVEDEscalations/Lists/Escalations%20to%20the%20Desk/AllItems.aspx"
    Const LISTNAME As String = "Escalation Team issues"
    Const VIEWGUID As String = "All Items"
    
    strSPServer = "https://" & SERVER & "/_vti_bin"
    
    Set objWksheet = Worksheets.Add

    Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, Array(stSPServer, LISTNAME, VIEWGUID), True, , Range("A1"))
    
    Set objMyList = Nothing
    Set objWksheet = Nothing
    
End Sub

Edit: I only have Read access to this Sharepoint site. Could that have something to do with it?