Skip to Main Content
Excel 2003 Programming: A Developer's Notebook
book

Excel 2003 Programming: A Developer's Notebook

by Jeff Webb
August 2004
Intermediate to advanced content levelIntermediate to advanced
312 pages
8h 30m
English
O'Reilly Media, Inc.
Content preview from Excel 2003 Programming: A Developer's Notebook

Display a SharePoint Site

I have to admit that I wrote my own VBA procedure to do this at one point—but 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.

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

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham
Excel® 2007 VBA Programmer's Reference

Excel® 2007 VBA Programmer's Reference

John Green, Stephen Bullen, Rob Bovey, Michael Alexander
Office 2003 XML

Office 2003 XML

Simon St. Laurent, Mary McRae, Evan Lenz

Publisher Resources

ISBN: 0596007671Supplemental ContentCatalog PageErrata