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.
https://www.vba-market.com/ImportSharepointList_toExcel.xlsm
You can get the GUID from the Library Settings page: (will look something like http://sp.web.com/Lists/ListName/_layouts/listedit.aspx?List=%7BF7B36223%2D487D%2D4550%2D8186%2DB286F1D4698E%7D)
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) = "https://sp.com/sites/myList/_vti_bin"
Sub ImportSharepointList_toExcel()
Dim ws As Worksheet
Dim objListObj As ListObject
Set ws = ThisWorkbook.Worksheets(1)
Dim src(1) As Variant
'http://sp.web.com/Lists/ListName/_layouts/listedit.aspx?List=%7BF7B36223%2D487D%2D4550%2D8186%2DB286F1D4698E%7D
'GUID = %7BF7B36223%2D487D%2D4550%2D8186%2DB286F1D4698E%7D
'URL Decode conversion: {F7B36223-487D-4550-8186-B286F1D4698E}
src(0) = "https://sp.com/sites/myList/_vti_bin"
src(1) = "F7B36223-487D-4550-8186-B286F1D4698E"
ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")
End Sub