Import SharePoint List into Excel only using VBA

Macro that will import a SharePoint list directly into Excel.  Below VBA will pull Sharepoint List into Excel as a table.  Required inputs for the VBA is the Sharepoint’s URL, GUID and decoded GUID.


You can get the GUID from the Library Settings page: (will look something like

Then you need to decode the GUID, by google searching for URL Decoder.

GUID: (%7BF7B36223%2D487D%2D4550%2D8186%2DB286F1D4698E%7D)

Decoded GUID: {F7B36223-487D-4550-8186-B286F1D4698E}

Use the decoded GUID in the VBA macro.

src(1) = "F7B36223-487D-4550-8186-B286F1D4698E"

Update src(0) with your sharepoint list URL + _vti_bin at the end of the URL.

src(0) = ""
Sub ImportSharepointList_toExcel()
Dim ws As Worksheet
Dim objListObj As ListObject
Set ws = ThisWorkbook.Worksheets(1)
Dim src(1) As Variant

'GUID = %7BF7B36223%2D487D%2D4550%2D8186%2DB286F1D4698E%7D
'URL Decode conversion:  {F7B36223-487D-4550-8186-B286F1D4698E}

src(0) = ""
src(1) = "F7B36223-487D-4550-8186-B286F1D4698E"
ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")

End Sub