Excel 2003 Programming: A Developer's Notebook

Remove Sharing

You can remove sharing from a workbook stored in a shared workspace at two levels:

• Delete the file from the SharePoint server. This breaks the connection for all users.

• Disconnect the local workbook from the shared workspace. This breaks the connection between the local copy of the workbook and the server.

How to do it

Use the RemoveDocument method to delete the current document from the shared workspace, as shown by the following code:

   If ThisWorkbook.SharedWorkspace.Connected Then _
       ThisWorkbook.SharedWorkspace.RemoveDocument

The preceding code leaves local copies that users have downloaded from the shared workspace, but they become disconnected since the shared workbook no longer exists. Alternatively, you can leave the workbook in the shared workspace, but disconnect your local copy with this code:

   If ThisWorkbook.SharedWorkspace.Connected Then _
       ThisWorkbook.SharedWorkspace.Disconnect

Now, the local copy can no longer be updated from, or send updates to, the shared workbook. If you want an updatable copy, you must reopen the workbook from the shared workspace. There’s no way to reattach an existing local workbook to the server copy.

You can also use the Files collection to remove workbooks from a shared workspace. This technique works well if you want to remove a file other than the current workbook. For example, the following code removes Security.xls from the current workbook’s shared workspace:

 Dim file As Office.SharedWorkspaceFile If ThisWorkbook.SharedWorkspace.Connected ...