Essential SharePoint

Programming SharePoint in VBA

Many Excel users write macros in VBA. If you're not one of those power users, please skip the rest of this chapter. If you know VBA, you'll find the following tasks helpful when working with SharePoint.

Creating Workspaces

Use the Workbook object's SharedWorkspace property to work with shared workspaces in Excel. The SharedWorkspace property returns a SharedWorkspace object that you use to share the workbook, update the workbook, and navigate among other elements in the shared workspace. For example, this code uses the SharedWorkspace object's CreateNew method to create a new shared workspace and add the current workbook to it:

    ThisWorkbook.Save
    ThisWorkbook.SharedWorkspace.CreateNew "http://wombat1", _
        "Team Wombat"

You must save the workbook before adding it to a shared workspace, otherwise the CreateNew method will fail. The preceding code adds the current workbook to the SharePoint site on the Wombat1 server. If you click on Open site in browser from the Shared Workspace task pane, Excel displays the new workspace site created at http://wombat1/Team%20Wombat.

If you call CreateNew again, Excel will create another new SharePoint site and increment the site name to http://wombat1/Team%20Wombat(1). To add a workbook to an existing SharePoint site instead of creating a new site, follow these steps:

1. Open an existing document from the SharePoint site.

2. Get a reference to that document's SharedWorkspace object.

3. Add your workbook to the SharedWorkspace object's ...