Exporting Sharepoint report to excel using VBA

[dhanabharathi2]

Hi, Greetings for the day!

I am trying to pull report from sharepoint2007 using vba and I am using excel 2010.

I got the below given code from internet. When I try testing this, I getting error "Cannot connect to the server at this time. Changes to your data cannot be saved".

Can anyone help me out to fix this error and run the code successfully.

Dim objMyList As ListObjectDim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = "xx.xx.net/sites/hsd/Communities/O/Lists/Currency%20Value/AllItems.aspx"
Const LISTNAME As String = "{01CB6C4A-CF5F-4E0E-B4DE-2E28D40ED71F}"
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("A1"))
Set objMyList = Nothing
Set objWksheet = Nothing