Programming Excel with VBA and .NET

Open Workbooks from a Shared Workspace

If you double-click on a workbook in the workspace, Excel opens the workbook as read-only. To open the workbook for editing, select Edit in Microsoft Office Excel from the pop-up menu on the site as shown in Figure 8-15.

Figure 8-15. Opening a workbook from a shared workspace

To open a workbook from a shared workspace in code, simply use the Open method with the address of the workbook from the workspace. For example, the following code opens a workbook from http://wombat2/TeamWombat:

Workbooks.Open "http//wombat2/Team Wombat/Shared Documents/ch08.xls"

If you want exclusive access to a file, you can choose Check Out from the pop-up menu before opening the workbook for editing--checking out doesn’t open the file; it just reserves it so other users can’t make changes. You won’t be able to check the workbook out if other users have the file open, however.

To check a file out from code, use the Workbook object’s CanCheckOut property and the CheckOut method. For example, the following code attempts to check out a file, and if it is successful, it opens the file in Excel:

Sub CheckOut(  )
    fil = " http//wombat2/Team Wombat/Shared Documents/ch08.xls"
    If Application.Workbooks.CanCheckOut(fil) Then
         Application.Workbooks.CheckOut fil
         Set wb = Application.Workbooks.Open(fil)
         MsgBox wb.Name & " is check out to you."
    End If
End Sub

The CheckOut method doesn’t open ...