Forum Discussion

Arne_Rulander's avatar
Arne_Rulander
Copper Contributor
Dec 20, 2023

Excel VBA

How do I set a Range in Excel via VBA from a Table located on a Sharepoint folder on a remote server?

This works fine:

Dim rtblSalesperson As Range
Dim rtblSalespersonMail As Range

sSalesperson = "Joe Doe"

Set rtblSalesperson = Range("tblSalesman[Name]")

Set rtblSalespersonMail = Range("tblSalesman[Email]")

sSalesPersonMail = WorksheetFunction.XLookup(sSäljare, rtblSalesperson , rtblSalespersonMail , "X")

 

This works fine when tblSalesman is in the same Excelfile but I want to have tblSalesman on a remote server.

Can I use some cind of Data connection since I want to connect to several tables, or do I have to set a connection for each Range?

Rgds

Arne

Macros and VBA
  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor
    Dec 21, 2023

    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

     

     

Resources