Document Management Task Pane

Removed in 2010
Displayed (Office > Publish > Create Document)


In Excel 2003 this was the Shared Workspace task pane
A shared workspace is a folder on a SharePoint server where you can store workbooks where colleagues can savem edit and comment on.
This contains a set of hyperlinks that let you create and manage shared workspaces to promote web based collaboration.


Before Excel 2003 a workspace referred to a group of workbooks arranged in a particular window
This functionality still exists.


In Excel 2007 this has become the Document Management task pane


Use the Workbook's object SharedWorkspace property to work with the shared workspaces in Excel.
This property returns a SharedWorkspace object that you can use to share the workbook, update the workbook and navigate among other elements in the shared workspace.


ThisWorkbook.SharedWorkspace.CreateNew "https://bettersolutions.com", "testing" 

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


Dim objSharedWorkspace As SharedWorkspace 
Dim objFileDialog As FileDialog
Dim sFile As String

If ThisWorkbook.SharedWorkspace.Connected = True Then
   Set objSharedWorkspace = ThisWorkbook.SharedWorkspace
   Set objFileDialog = Application.FileDialog(msoFileDialogOpen)
   objFileDialog.Title = "Add a file to shared workspace"
   objFileDialog.AllowMultiSelect = True
   objFileDialog.Show
   For Each sFile In objFileDialog.SelectedItems
      objSharedWorkspace.Files.Add sFile, , ,True
   Next sFile
End If


ThisWorkbook.FollowHyperlink ThisWorkbook.SharedWorkspace.URL 

Excel doesn't wait for FollowHyperlink to finish.



objWorkbook.CanCheckOut 
objWorkbook.CanCheckIn

You cannot check a workbook back in using its own code.


Breaking the Link

You can remove sharing from a workbook stored in a shared workspace at two levels:
1) Delete the file from the sharepoint server. This breaks the connected for all users


RemoveDocument Property
Use this property to delete the currend document from the shared workspace
This leaves local copies that users have downloaded from the shared workspace.


If ThisWorkbook.SharedWorkspace.Connected = True Then 
   ThisWorkbook.SharedWorkspace.RemoveDocument
End if

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


Disconnect Property
This leaves the workbook connected to the shared workspace but disconnects just the local copy
Now the local copy can no longer be updated from or send updates to the shared workspace

If ThisWorkbook.SharedWorkspace.Connected = True Then 
   ThisWorkbook.SharedWorkspace.Disconnect
End if

There is no way of reattaching an existing local workbook to the server copy.


After a shared workbook is removed from the server any user who has a linked copy of the workbook is warned when he opens his local copy.
The warning is only displayed the first time the file is opened.


Files Collection

You can also use the Files collection to remove workbooks from a shared workspace.
This works well if you want to remove a file that is not the current workbook


Dim objFile As Office.SharedWorkspaceFile 

If ThisWorkbook.SharedWorkspace.Connected = True Then
   For Each objFile In ThisWorkbook.SharedWorkspace.Files
      objFile.Delete
   Next objFile
End if


Members Collection


Dim objMember As Office.SharedWorkspaceMember 

If ThisWorkbook.SharedWorkspace.Connected = True Then
   For Each objMember In ThisWorkbook.SharedWorkspace.Members

'quick way of sending an email
   This workbook.FollowHyperlink "mailto" & "objMember.Email & "?Subject = Deleting File from server: " & ThisWorkbook.Name

   Next objMember
End if


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext