Programming Excel with VBA and .NET

Remove Sharing

There are two levels of removing sharing from a workbook stored in a shared workspace. You can:

• Delete the file from the workspace. This breaks the connection that other users share.

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

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

Sub TestRemove(  )
    If ThisWorkbook.SharedWorkspace.Connected Then _
      ThisWorkbook.SharedWorkspace.RemoveDocument
End Sub

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. Alternately, you can leave the workbook in the shared workspace, but disconnect your local copy with this code:

Sub TestDisconnect(  )
    If ThisWorkbook.SharedWorkspace.Connected Then _
      ThisWorkbook.SharedWorkspace.Disconnect
End Sub

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.

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 ch08.xls from the current workbook’s shared workspace:

Sub TestRemoveFile( ) Dim file As Office.SharedWorkspaceFile If ThisWorkbook.SharedWorkspace.Connected ...