Excel 2003 Programming: A Developer's Notebook

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!

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.

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.

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”. ...