Section 2.5.Display a SharePoint Site

2.5. Display a SharePoint Site

I have to admit that I wrote my own VBA procedure to do this at one pointbut I didn't need to. The Workbook object's FollowHyperlink method provides a built-in way to display the SharePoint site or any web page using the default browser.

2.5.1. How to do it

The SharedWorkspace object provides a URL property that returns the address of the shared workspace. You can use this property or the SPSITE constant with the FollowHyperlink method to display the site:

       ThisWorkbook.FollowHyperlink ThisWorkbook.SharedWorkspace.URL

Or:

       ThisWorkbook.FollowHyperlink SPSITE

If you're going to perform an action on the current workbook, you may need to close the workbook as part of the process and display the SharePoint site. For example, you can't check a workbook back in from its own code. The following code warns the user that she can't check an open file in, and then it displays the SharePoint site:

    Dim sw As SharedWorkspace, msg As String    Set sw = ThisWorkbook.SharedWorkspace    ' Can't check ThisWorkbook in! Must close first.    If sw.Connected Then        msg = "You must close this workbook before it can be checked in. " & _         "OK to close? After closing you can check in from SharePoint."        If MsgBox(msg, vbYesNo) = vbYes Then            ThisWorkbook.Save            ThisWorkbook.FollowHyperlink sw.URL            ThisWorkbook.Close        End If    End If

2.5.2. How it works

Because Excel doesn't wait for FollowHyperlink to finish, the preceding code works smoothly, saving and closing the workbook while the SharePoint site displays. Close has to be the last executable line, because after that, the code is unloaded (along with the workbook).

You might notice that you can't test CanCheckIn from the current workbook. It always returns False since Excel doesn't let you check it back in from code (this probably has something to do with CheckIn closing the workbook).

Perhaps a better solution to this problem is just to avoid it. If you're going to check workbooks in and out, don't write that code as part of a workbook. Make it part of an Addin instead.

2.5.3. What about...

To learn about	Look here
The Workbook object	C:\Program Files\Microsoft Office\OFFICE11\1033\VBAXL10.CHM