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
