Section 2.3.Share a Workbook

2.3. Share a Workbook

Anything you can do through the task pane, you can also do in codeactually, you can do more.

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.

2.3.1. How to do it

Use the SharedWorkspace object's CreateNew method to create a new shared workspace and add a workbook to it:

    ThisWorkbook.Save    ThisWorkbook.SharedWorkspace.CreateNew _    "http://excelnotebook.sharepointsite.com", "ch02"

You must save the workbook before adding it to a shared workspace, otherwise the CreateNew method fails. The preceding code adds the current workbook to the SharePoint site on the excelnotebook.sharepointsite.com server. If you click on Open site in browser in the Excel Shared Workspace pane, Excel displays the new workspace created at excelnotebook.sharepointsite.com/ch02 (Figure 2-7).

---

Note: You can do a lot with shared workspaces through the SharePoint site and the Excel task pane, but by understanding the object model you can customize sharing (almost) any way you like.

---

If you call CreateNew again, Excel will create another, new workspace and increment the site name to excelnotebook.sharepointsite.com/ch02(1) . Remember, Excel creates one workspace folder per document.

In some cases, you may want to add other documents to an existing workspace. To do that, 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 Files collection.

4. Close the document you opened in Step 1.

5. Close the workbook you just added and reopen it from the SharePoint site.

So if you're starting with a workbook that has already been shared, you can use that workbook's SharedWorkspace property to add other files to the same workspace:

    Dim sw As SharedWorkspace, dlg As FileDialog, fil as String    If ThisWorkbook.SharedWorkspace.Connected Then        Set sw = ThisWorkbook.SharedWorkspace        ' Get a file to add.        Set dlg = Application.FileDialog(msoFileDialogOpen)        dlg.Title = "Add file to shared workspace."        dlg.AllowMultiSelect = True        dlg.Show        For Each fil In dlg.SelectedItems            sw.Files.Add fil, , , True        Next    End If

2.3.2. How it works

The preceding code checks if the current workbook is shared ( ThisWorkbook.Connected ) and if it is, it gets a reference to the workbook's shared workspace.

Next, it displays Excel's File Open dialog to get the list of files to add to the workspace. If the user selects one or more files, the For Each loop adds each file to the SharedWorkspace object's File collection. The last argument of Add links the local file to the workspace.

---

Note: Use the Connected property to check if a workbook is part of a workspace. The SharedWorkspace property always returns an object, even if the workbook has not been shared.

---