Import SharePoint List into Excel only using VBA

Macro that will import a SharePoint list directly into Excel.  Excel VBA pulls in the SharePoint List based on 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,

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

Interested in learning new skills?