Monday, July 30, 2018

Using hyperlinks in a workbook in the browser

Using hyperlinks in a workbook in the browser

A hyperlink is a link from one document to another that opens the second page or file when you click it. You can use a hyperlink in a workbook in the browser exactly as you can in other applications.

The following sections describe how to use a hyperlink to move to different kinds of target locations, and how create a hyperlink for use in a workbook in the browser.

What do you want to do?

Use a hyperlink in a workbook in the browser

Creating a hyperlink to use in a browser-based workbook

Differences between hyperlinks in a workbook in the browser and in Excel

Web Part property settings that affect hyperlinks

Use a hyperlink in a workbook in the browser

After you add a hyperlink to the Excel workbook and save it to a site, you can use the hyperlink to browse to a different location and open the page that you specified. For example, you can

Open a document, file, or Web Page in a new window   . When you link to a document or Web page outside the current workbook, the Web viewer opens a new window in the browser to display it.

Start an e-mail program and create a message.    When you click a hyperlink to an e-mail address, your e-mail program automatically starts and creates an e-mail message that has the correct address in the To box.

Go to another location in the current workbook   . If a hyperlink connects to another location in the current workbook, the active cell selection changes to the new location in workbook, but does not open a separate window.

Navigating to another location in the current workbook can be very useful, especially in a large workbook that contains many worksheets. For example, you might create a dedicated worksheet that behaves as a main menu, with links to each separate worksheet. Or, you might create links that connect cells in one worksheet to related information in a different worksheet.

Top of Page

Creating a hyperlink to use in a browser-based workbook

You cannot create a hyperlink directly in a workbook in Excel Services. Instead, you must create or edit a hyperlink in a workbook by using Excel, and then publish or upload the workbook to a document library. There are several ways to create a hyperlink. You can type a URL directly in a cell, use the Hyperlink button in the Links group in Excel, or use the Hyperlink function.

When you create a hyperlink in an Excel workbook, the hyperlink encodes the destination as a Uniform Resource Locator (URL). The URL can take different forms, depending on the destination location (on the Web, in the same document, etc.). The See Also section contains a link that provides detailed information about (and many examples of) different kinds of hyperlinks in an Excel workbook.

The following examples of hyperlink syntax contain detailed explanations of the elements of the hyperlink. You can use these examples to help you create hyperlinks.

  • Link to a Web page

    This example shows a hyperlink that connects to a separate Web page that is located on the www.example.com Web server:

    http://www.example.com/jan07/news.htm.

    URL Component

    Description

    http://

    The protocol designator that indicates the destination is a Web location.

    www.example.com

    The address of the Web server or network location

    Jan07

    The path or folder on the server

    News.htm

    The file name of the destination page

  • Get data from another Web page

    You can create or run a Web query that retrieves data from a Web page. You can also create a Web query that retrieves data that is stored at a location on the Internet, or on a company intranet. A Web query sends the target location as a query string at the end of the base address of the target page. Web pages often contain information that is perfect for analysis in Excel. For example, you can use a Web query to retrieve data from another workbook on your company intranet.

    For example, this hyperlink opens the Customer Order section of a corporate Web site. Then, it locates a workbook called PurchaseOrder23456, and finds the cell range F1 through F8 on the May worksheet:

    http://MySharePointServer/MySite/_layouts/xlviewer.aspx?id=http://CorporateServer/CustomerOrderSite/Shared%20Documents/PurchaseOrder23456.xlsx&range=May!F2:F8

    URL Component

    Description

    http://

    The protocol (HTTP is required for Web locations)

    MySharePointServer/MySite/_layouts

    The intranet site

    xlviewer.aspx

    The Excel Web Access viewer

     ?id

    The question mark character begins the URL query string

    http://CorporateServer/CustomerOrderSite/Shared%20Documents/

    The site address of the destination

    PurchaseOrder23456.xlsx

    The workbook name

    &range=

    The ampersand (&) character connects the workbook name to the range value of the destination cells

    May

    The worksheet name in the workbook

     !

    The exclamation point character connects the worksheet name to the range of cells that is the destination

    F2:F8

    The actual range of cells that is the target of the query

    If you pass a workbook location as a query string parameter in a hyperlink, the viewer opens a new window (or a new tab in the browser. Then, the viewer focus moves to the specific workbook location. If it is a cell location, the viewer positions the cell in the center of the Web page. If it is a local or global named range, the viewer positions upper-left cell of the range in the center of the Web page. In either case, the viewer does not select the cell.

Differences between hyperlinks in a workbook in the browser and in Excel

In most ways, a hyperlink in a workbook in the browser behaves exactly like a hyperlink in an Excel workbook. However, there are some differences.

  • Workbooks in the browser only support absolute hyperlinks, or hyperlinks that contain a full address. This includes the protocol, the Web Server, the path, and the file name. They do not support relative hyperlinks, which contain only part of a full address. In contrast, Excel supports both absolute hyperlinks and relative hyperlinks.

  • Workbooks in the browser determine the color of a hyperlink from the default hyperlink color defined for the browser, unless you explicitly specify the default hyperlink color in the Excel workbook.

  • The Web-based viewer converts hyperlinks that reference a whole column or row in Excel to a reference to the first cell in that row or column.

  • The Web-based viewer does not support hyperlinks in charts, graphic hyperlinks, or hyperlinks to noncontiguous ranges. Instead, the viewer converts links such as these to text strings.

Top of Page

Web Part property settings that affect hyperlinks

The Excel Web Access Web Part offers custom properties that you can use to help control the behavior of hyperlinks. The following table describes the properties.

If this property's check box:

Is selected:

Is cleared:

Allow Hyperlinks

All supported hyperlinks to files and documents outside the current workbook are active. This is the default behavior.

All supported hyperlinks to locations outside the workbook are converted to inactive text strings.

Allow Navigation

All supported hyperlinks to locations within the current workbook are active. This is the default behavior.

All supported hyperlinks to locations within the workbook are converted to inactive text strings.

Top of Page

No comments:

Post a Comment