Section 2.12.Insert a Shared List

2.12. Insert a Shared List

Once a list is published on a SharePoint site, team members can insert that list into existing worksheets or create new workbooks from the list. If they link their worksheet copy of the list to the SharePoint list, changes to their local copy can be synchronized across the team.

---

Note: This is the trickiest part about programming with lists, in my opinion. You can't record this code to see how Excel does it and the Add method takes an array argument. It's hard to figure out on your own!

---

2.12.1. How to do it

To insert a shared list manually, navigate to the list on the SharePoint site, click Task Pane and then click Export and Link to Excel. SharePoint starts Excel and displays dialogs that let you create the list in a new workbook or insert the list into an existing one.

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.

To insert a shared list from code, use the ListObjects Add method and the SourceType argument xlSrcExternal :

    Set ws = Workbooks.Add.Worksheets(1)    Dim src(1) As Variant    src(0) = SPSITE & "/_vti_bin"    src(1) = "Excel Objects"    ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")

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. The GUID is a 32-digit numeric string that identifies the list on the server.

2.12.2. How it works

Notice that the first element of the Source array argument is the server address plus the folder name _vti_bin. SharePoint stores the Lists web service in that folder and Excel uses that web service to retrieve the list.

When you insert the list manually, SharePoint saves the target workbook as an XML spreadsheet as an intermediate step while importing the list data. That step removes any Visual Basic code from the workbook. As a rule, it is a good idea to create a new workbook when inserting a shared list manually.