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?


2 Answers 2


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
  • This solution throws "Application or object defined error"
    – Clem
    Commented Jun 26, 2012 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
    Commented Sep 11, 2012 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
    Commented Sep 13, 2013 at 18:31
  • 2
    I came back to this several years after upvoting and found it useful again, thanks!
    – enderland
    Commented May 13, 2015 at 13:51
  • Thanks! How do i pass username & password manually?
    – Dev
    Commented Jun 11, 2017 at 21:27

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.


  • 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
    Commented Feb 13, 2012 at 15:08

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