Import Sharepoint List into Excel Using VBA Only

Question

In Excel 2007, I have a VBA script that consolidates various data sources into a single Excel file for monthly archiving and reporting purposes.

How can I import data directly from a Sharepoint List into Excel using VBA?

Currently, I am manually exporting to an Excel file via the web interface and then the VBA script is picking it up from there. I would like to eliminate that manual step, however, and grab the data directly off the server via VBA.

I've searched online but am not coming up with any good results. Any ideas where I can start?

Thanks

Answer 1

I found a way to do it finally via "The Internet". Here is what worked for me.

Sub ImportSharePointList()

    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Dim strSPServer As String
    Const SERVER As String = "mysite:8003/sites/the-sharepoint-thing/"
    Const LISTNAME As String = "{D1F1C2ED-81BA-41CC-A698-XXXXXXXXXXXX}"
    Const VIEWNAME As String = "{27C9CA20-3293-4BD5-9271-XXXXXXXXXXXX}"

   ' 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), False, , Range("A1"))

End Sub

Answer 2

I'm not too familiar with VBA, but you can use the web services to extract the data from SharePoint. I don't know about a direct link query like SP will do for you when you export a list to Excel.

HTH