Copy SharePoint List to Excel using VBA

Excel VBA Leave a comment

This post is about copying a List from SharePoint (SP) to Excel using VBA.

To do this you will need 3 IDs from SP where your List resides.

  • the url of your List (Do take note that this url is not the one at the address bar. ) For me rthe easiest way to get this ID is via the html text. Right click on the SP List and click ‘view page source’. Refer to below pic.
  • the listID . Refer to below pic.
  • the viewID. Refer to below pic.

Using the url from the address bar might cause an Win error like below.

The macro as per below:

Sub ImportSharePointList()

    Dim objMyList As ListObject
    Dim ws As Worksheet
    Dim strSPServer As String
    
    'The below line, the address taken directly from the address bar will not work.
    'It will give "RunTime-error 1004,an unexpected error has occur. Changes to your data cannot be saved."
    'Const SERVER As String = "linaconfectionery.sharepoint.com/sites/Orders/Lists/Test%20for%20SP%20to%20Excel/AllItems.aspx"
    Const SERVER As String = "linaconfectionery.sharepoint.com/sites/Orders"
    Const LISTNAME As String = "{5f998b54-6519-4ce7-aca1-f22d7a7d3106}"  'listID from html
    Const VIEWNAME As String = "{0a8726f8-f4b0-4358-90d6-da3f19093c93}"  'viewID from html

    strSPServer = "http://" & SERVER & "/_vti_bin"
    copydate = Now
    copydate = Replace(copydate, "/", ".")
    copydate = Replace(copydate, ":", "_")
    
    ' Add a new worksheet to the active workbook.
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "FxRates1" & " " & copydate

    
    ' Add a list range to the newly created worksheet
    ' and populated it with the data from the SharePoint list.
    Set objMyList = ws.ListObjects.Add(xlSrcExternal, Array(strSPServer, LISTNAME, VIEWNAME), True, , Range("A1"))


End Sub

A short video to show the automation.