excel 2010 getting data from a sharepoint list

Question

I need to get data from a SharePoint list. I came across the below code and I have tried many different combination of the urls etc. But I keep getting

“Run-Time error 1004” “Application-define or object-define error”

I have full admin rights to the site I’m using SharePoint 2010 with Excel 2010

Here the code I have found:

Sub ImportSharePointList()
    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Dim strSPServer As String
    Const SERVER As String = "xxx.xxx.net/sites/tss_PP/Lists"
    Const LISTNAME As String = "{F915A40C-0394-418C-AB90-xxxxxxxxxxxxx}"
    Const VIEWNAME As String = ""
   ' 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 = ActiveSheet


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

    Set objMyList = Nothing
    Set objWksheet = Nothing
End Sub

Any help would be great, just a note I have tried this with and without a view name

Answer 1

Your code works well here (Excel 2010, SHP-2007) if I set the variables as following:

Const SERVER As String = "shp.myserver.myorg/sites/[mainsite]/[subsite]"
Const LISTNAME As String = "Country" ' real List name
Const VIEWNAME As String = ""

server: I do not include /List or any other suffix, only down to the subsite level