Can you use vba code to export SharePoint 2010 list to Excel 2010?

[dearnemo]

I know you can pretty easily export/import SharePoint 2010 list data to/from Excel using "Export to Excel" ribbon interface. If there's a need to do it for a large number of files, I need to automate this process using vba code in Excel 2010. I have seen this work according to the link
http://sharepoint.stackexchange.com/questions/29021/import-sharepoint-list-into-excel-using-vba-only

Just wondering what changes would it involve to do it with Sharepoint 2010 and Excel 2010. I did it like this :


Sub TestMacro()
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = "http://abcd/"
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
</PRE>

However I m getting a run time error "Cannot connect to the server...". And my objMyList value is empty. Any changes that I need to make in my code to make it run?

I would greatly appreciate your help/support. Thanks in adv!

 

[H8245]

Edit the value of SERVER to "abcd"
My suggestion would also be to use a value for the VIEWNAME string. Without a value it may use the default view which may produce unexpected results.

I know you can pretty easily export/import SharePoint 2010 list data to/from Excel using "Export to Excel" ribbon interface. If there's a need to do it for a large number of files, I need to automate this process using vba code in Excel 2010. I have seen this work according to the link
Import Sharepoint List into Excel Using VBA Only - SharePoint Stack Exchange

Just wondering what changes would it involve to do it with Sharepoint 2010 and Excel 2010. I did it like this :


Sub TestMacro()
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = "http://abcd/"
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

</PRE>

However I m getting a run time error "Cannot connect to the server...". And my objMyList value is empty. Any changes that I need to make in my code to make it run?

I would greatly appreciate your help/support. Thanks in adv!