Section 2.6. Remove Sharing

2.6. 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.

2.6.1. 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 Then        For Each file In ThisWorkbook.SharedWorkspace.Files            If InStr(1, file.urlThisWorkbook, "security.xls") Then _                file.Delete        Next    End If

2.6.2. How it works

In the preceding code, you need to locate the file to remove using the Instr functionthe Files collection doesn't provide an indexer to locate the file by name. Once you've located the file you want to remove, you can use the File object's Delete method to remove it.

After a shared workbook is removed from the server, any user who had a linked copy of the workbook is warned when he opens his local copy (Figure 2-11). The warning is only displayed the first time the file is opened.

Figure 2-11. Users with linked copies see this warning when they open a file that has been removed

To avoid confusion, you may want to send email to team members before deleting files, as shown here:

    Dim usr As SharedWorkspaceMember, tolist As String    ' Get the email names of all members.    For Each usr In ThisWorkbook.SharedWorkspace.Members        tolist = usr.Email & ";" & tolist    Next    ' Send a notice.    ThisWorkbook.FollowHyperlink "mailto:" & tolist & _      "?SUBJECT=Deleting " & ThisWorkbook.Name

The preceding code builds a list of email addresses from the workbook's Members collection, then starts the user's mail system by using the mailto protocol in a hyperlink. This is a quick-and-dirty way to send mail messages from inside Excel code. The mailto protocol has this form:

 mailto:addresses?SUBJECT=subject;BODY=message 

2.6.3. What about...

To learn about	Look here
The mailto protocol	msdn.microsoft.com/workshop/networking/predefined/mailto.asp
The ShareWorkspace objects	C:\Program Files\Microsoft Office\OFFICE11\1033\VBAOF11.CHM