Programming Excel with VBA and .NET

Insert a shared list

When inserting an existing list onto a worksheet from a SharePoint server, use the Source array argument to specify the location of the list and the name or GUID of the list on the server. Although it is easier to know the name of a list, that name can be changed by editing the list’s General Settings in SharePoint. It is more reliable to use the GUID, since that unique identifier doesn’t change over the life of the list.

To find the GUID of a list, view the list on the SharePoint server and choose Modify Columns and Settings on the list’s web page. SharePoint displays the GUID for the list in the browser’s Address text box after the List= query string.

The following code demonstrates using a GUID to insert a shared list on a new worksheet:

Sub InsertListFromGUID(  )
    Dim ws As Worksheet, src(1) As Variant
    Set ws = ThisWorkbook.Worksheets.Add(, ActiveSheet)
    ws.Name = "Insert List GUID"
    src(0) = "http://www.excelworkshop.com/_vti_bin"
    src(1) = "{4B929DF0-F6C1-4230-A0E6-6AA18D668B15}"
    ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")
End Sub