Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

I'm not sure if it could work, but could this help: http://www.microsoft.com/en-us/download/details.aspx?id=9345

share|improve this answer
up vote 3 down vote accepted

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
share|improve this answer
    
This solution throws "Application or object defined error" –  Clem Jun 26 '12 at 16:40
    
You may have a type in the LISTNAME or VIEWNAME or SERVER variables; I am using this on Excel 2007 (and 2003) without an issue. –  thornomad Sep 11 '12 at 17:08
    
Have to ask: The very last line of the sub - the objWksheet.ListObjects.Add command - is there some way for me to work with the data that is going to be added to the new worksheet without actually adding it to a new worksheet? I'd like to load it directly into an array and skip manipulating it in a worksheet. Hoping there is an obvious answer!! :) –  Shrout1 Sep 13 '13 at 18:31

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

share|improve this answer
    
Thanks for the suggestion - I finally found a way via Google. Took a while to track it down and get it to work. Am not that savvy in VBA. Thanks –  thornomad Feb 13 '12 at 15:08

protected by Community Jun 18 '13 at 14:15

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.