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