Excel VBA

Hello Arne_Rulander,

You can set a Range from a table located on a SharePoint folder on a remote server.

Sub ImportListFromSP()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(2)
    Dim src(1) As Variant
    src(0) = "http://SharePointSiteAddress/your_link"
    src(1) = "89F90972-FD90-4B04-BCEB-81840A82DA5E"
    ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")
End Sub

Just replace "http://SharePointSiteAddress/your_link " and "89F90972-FD90-4B04-BCEB-81840A82DA5E" with your SharePoint site address and list GUID respectively.

reference: Update List from Excel using VBA - SharePoint Stack Exchange

You can also import a list from SharePoint to Excel as a Range with a fixed table name.

Here's an example:

Dim tbl As ListObject
Set tbl = RData.ListObjects(1)
Dim tblRng As Range
Set tblRng = tbl.Range ' Convert table to range
tbl.Unlist ' Comment this in case you do not want to remove the formatting
With tblRng
    .Interior.ColorIndex = xlColorIndexNone
    .Font.ColorIndex = xlColorIndexAutomatic
    .Borders.LineStyle = xlLineStyleNone
End With

RData is the name of the worksheet where the SharePoint list data is imported.

reference: vba - Importing a list from SharePoint to Excel as a Range (or with a fixed Table name) - Stack Overflow