Section 2.4.Open a Shared Workbook

2.4. Open a Shared Workbook

If the local file is linked to a shared workspace, opening that file automatically connects to the SharePoint site and updates the local file. The user may have to sign on to the SharePoint site, and Excel displays the update status (Figure 2-9).

If the local workbook is not linked, or if the workbook is not stored locally, you can open the workbook from the SharePoint site. If you double-click on a workbook in the SharePoint site, Excel opens the workbook as Read-Only. To open the workbook for editing, select Edit from the pop-up menu on the site (Figure 2-10).

---

Note: Use sharepointobj. Files. Add to add files to an existing workspace. CreateNew always creates a new workspace (which isn't always what you want).

---

2.4.1. How to do it

To open a workbook from a shared workspace in code, simply use the Workbooks collection's Open method. For linked workbooks, use the local address of the file:

    Application.Workbooks.Open "ch02.xls"

Figure 2-9. Opening a workbook linked to a shared workspace

Figure 2-10. Opening a shared workbook from the SharePoint site

For workbooks that aren't stored locally, use the address of the workbook from the SharePoint site:

    Application.Workbooks.Open "http://excelnotebook.sharepointsite.com/" & _      "ch02/Shared Documents/ch02.xls"

Multiple users can have the same file open, and changes to the file are synchronized among users. If you want exclusive access to a file, you can choose Check Out in Figure 2-10 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 out the workbook if other users have the file open, however.

To check out a file from code, use the Workbooks collection'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:

    fil = SPSITE & "/Shared Documents/ch02.xls"    If Application.Workbooks.CanCheckOut(fil) Then        Application.Workbooks.CheckOut fil        Set wb = Application.Workbooks.Open(fil)        MsgBox wb.Name & " is checked out to you."    End If

2.4.2. How it works

You have to specify the full server path when checking out a fileSharePoint puts shared workbooks in the /Shared Documents folder of the workspaceso you have to add that to the filename you pass to CanCheckOut and CheckOut . Notice also that those methods are part of the Workbooks collection; they aren't part of the SharedWorkspace object.

The CheckOut method doesn't open the workbook, so you need to add the Open method, as shown above. However, checking a workbook back in automatically closes the file, as shown below:

    Set wb = Application.Workbooks("ch02.xls")    If wb.CanCheckIn Then        ' CheckIn closes the file.        wb.CheckIn True, "Minor change"        MsgBox "File was checked in."    Else        MsgBox wb.Name & " could not be checked in."    End If

In some cases, a file may not be able to be checked in. For instance, you can't check in the current workbook from within its own code:

    If ThisWorbook.CanCheckIn Then ' Always False!

The problem here is ThisWorkbook you can only check the file back in from external code. In these cases, you can display the SharePoint site to provide a way to check the workbook back in. See the next topic for instructions.