Programming Excel with VBA and .NET

Insert a Shared List

Once a list is published on a SharePoint site, you can insert that list into other worksheets using the ListObject’s Add method and the SourceType argument xlSrcExternal:

Sub InsertSharedList(  )
    Dim ws As Worksheet, src(1) As Variant
    Set ws = ThisWorkbook.Worksheets.Add(, ActiveSheet)
    ws.Name = "Insert List"
    src(0) = "http://www.excelworkshop.com/_vti_bin"
    src(1) = "Test List"
    ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")
End Sub

When SourceType is xlSrcExternal, the Source argument is a two-element array containing this information:

Element	Data
0	List address. This is the SharePoint address plus the folder name /_vti_bin.
1	The name or GUID of the list. A GUID is a 32-digit numeric string that identifies the list on the server.

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 as shown in Figure 14-6.

Warning

Inserting a list manually from a SharePoint site into an existing workbook deletes all of the Visual Basic code contained in the workbook. Inserting a list from code does not delete a workbook’s code, however.