Automate exporting SharePoint list 2010 items to Excel 2010

Question

I found this Import Sharepoint List into Excel Using VBA Only that people have been successfully using to automate the exporting SharePoint 2010 list items to Excel 2010. Currently I m also using web interface 'export to excel' in SharePoint. But I want to avoid point-click and want to run a script that automates this process. Below is my code. I m getting run time error: Cannot connect to the server at this time.Changes to your data cannot be saved. Since I m new to vba, it will be great if anybody could point me to right instructions to make it work. Any suggestions/help is much appreciated. Thanks.

Sub TestMacro()
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = "http://pwd-gsops5/"
Const LISTNAME As String = "{A486016E-80B2-44C3-8B4A-8394574B9430}"

Const VIEWNAME As String = ""

   ' 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), True, , Range("a2"))

    Set objMyList = Nothing
    Set objWksheet = Nothing

End Sub

Answer 1

the Const Server As String should not include http:// as it will be provided when strSPServer variable is built.