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.

http://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,

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

Extra resources:

http://sharepoint.stackexchange.com/questions/29021/import-sharepoint-list-into-excel-using-vba-only
https://msdn.microsoft.com/en-us/library/aa203719(office.11).aspx#odc_xlov_listom