Skip to Main Content
Excel 2003 Programming: A Developer's Notebook
book

Excel 2003 Programming: A Developer's Notebook

by Jeff Webb
August 2004
Intermediate to advanced content levelIntermediate to advanced
312 pages
8h 30m
English
O'Reilly Media, Inc.
Content preview from 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:

ElementData

0

List address. This is the SharePoint address plus the folder name “/_vti_bin”. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham
Excel® 2007 VBA Programmer's Reference

Excel® 2007 VBA Programmer's Reference

John Green, Stephen Bullen, Rob Bovey, Michael Alexander
Office 2003 XML

Office 2003 XML

Simon St. Laurent, Mary McRae, Evan Lenz

Publisher Resources

ISBN: 0596007671Supplemental ContentCatalog PageErrata