Creating Hyperlinks Using VBA

Excel 2002, like other applications in Microsoft Office, allows you to create hyperlinks in your spreadsheets. After clicking on a cell that contains a hyperlink, you can open a document located on a network server, an intranet, or the Internet. Hyperlinks can be created directly in the user interface with the Insert | Hyperlink option (Figure 16-1) or programmati-cally using VBA.

Insert Hyperlink

Link to: Text to display:

Existing File or Web Page

Place in This Document m

Create New Document

E-mail Address

Existing File or Web Page

Place in This Document m

Create New Document

.ook in:

¡^51 ffljgj

zi Ml fi] *

Current Folder

fol My Digital Camera J 3V& Floppy (A:) Local Disk (C:) ^ Compact Disc (D:) 3 Removable Disk (E:) ^ DirectCD (F:)

Browsed Pages

Recent Files

Address:

Figure 16-1: A dialog box used to insert a hyperlink in Microsoft Excel

In VBA, each hyperlink is represented by a Hyperlink object. To create a hyperlink to a web page, use the Add method of the Hyperlinks collection. This method is shown below:

Expression.Hyperlinks.Add(Ancftor, Address, [SubAddress], [ScreenTip], _ [TextToDisplay])

The arguments in square brackets are optional. Expression denotes a worksheet or range of cells where you want to place the hyperlink. Anchor is an object to be clicked. This can be either a Range or Shape object. Address points to a local network or a web page. SubAddress is the name of a range in the Excel file. ScreenTip allows the display of a screen label. TextToDisplay is a friendly name that you'd like to display in a spreadsheet cell for a specific hyperlink.

Let's see how this is done by creating a VBA procedure that places a hyperlink in a worksheet cell. This hyperlink, when clicked, should take you to the Yahoo site.

1. Open a new workbook.

2. Switch to the Visual Basic Editor screen, and insert a new module into the current VBA project.

3. In the Code window, enter the code of the FollowMe procedure shown below.

4. Activate the procedure created in step 3.

Sub FollowMe() Dim myRange As Range Set myRange = Sheets(1).Range("A1") myRange.Hyperlinks.Add _ Anchor:=myRange, _

Address:="http://search.yahoo.com/bin/search", _ ScreenTip:="Search Yahoo", _ TextToDisplay:="Click here" End Sub

When you activate the FollowMe procedure, cell A1 in the first worksheet will contain a friendly hyperlink "Click here" with the screen tip "Search Yahoo" (Figure 16-2). If you are now connected to the Internet, clicking on this hyperlink will activate your browser and load the Yahoo search engine (Figure 16-3).

Figure 16-2:

This hyperlink was placed in a worksheet by a VBA procedure.

Figure 16-2:

This hyperlink was placed in a worksheet by a VBA procedure.

Vba Search Engine
Figure 16-3: The main page of the Yahoo search engine was activated by clicking on the hyperlink placed in a worksheet cell.

If you'd rather not place hyperlinks in a worksheet but still make it possible for a user to reach the required Internet pages directly from the Excel worksheet, you can use the FollowHyperlink method. This method allows you to open the required web page without the need to place a hyperlink object in a worksheet. The format of this method looks like this:

Expression.FollowHyperlink(Address, [SubAddress], [NewUindow], _ [AddHistory], [ExtraInfo], [Method], [HeaderInfo])

Again, the arguments in square brackets are optional. Expression returns a Workbook object. Address is the address of the web page that you want to activate. SubAddress is a fragment of the object to which the hyperlink address points. This can be a range of cells in an Excel worksheet. NewWindow indicates whether you want to display the document or page in a new window. The default setting is False. The next argument, AddHistory, is not currently used. It is reserved for future use. ExtraInfo gives additional information that allows jumping to the specific location in a document or on a web page. For example, you can specify here the text for which you want to search. Method specifies the method in which the additional information (ExtraInfo) is attached. This can be one of the following constants: msoMethodGet or msoMethodPost.

When you use msoMethodGet, ExtraInfo is a String that's appended to the URL address. When using msoMethodPost, ExtraInfo is posted as a String or byte array.

The last optional argument, HeaderInfo, is a String that specifies header information for the HTTP request. The default value is an empty string.

Let's see how to use the FollowHyperlink method in a VBA procedure. The purpose of this procedure is to find any text entered in a worksheet cell using the AltaVista search engine.

1. In the Visual Basic Editor window, activate the Project Explorer window and double-click the Sheet2 (Sheet2) object in the Microsoft Excel Objects folder.

2. In the Code window, enter the Worksheet_BeforeDoubleClick procedure shown below (review Chapter 14 on creating and using event procedures in Excel):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim strSearch As String strSearch = Sheets(2).Range("C3").Formula If Target = Range("C3") Then Cancel = True

ActiveWorkbook.FollowHyperlink _

Address:="http://www.altavista.com/cgi-bin/query", _ ExtraInfo:="q=" & strSearch, _ Method:=msoMethodGet

End If

End Sub

3. Now switch to the Microsoft Excel application window and enter in cell C3 on sheet 2 any word or term you want to find information about (Figure 16-4).

4. Make sure that you are connected to Internet.

5. Double-click cell C3. This will cause the text entered in cell C3 to be sent to the AltaVista search engine. The screen should show the index to found topics with the specified criteria (Figure 16-5).

"j Hyperlinks, xls

A

e t

D

E

F i H

I

1

2

3

Arabic lessons

4

*

\

5

S

1 Fritpr

text vmi want tn sparrh fnr in rell I

6

Dm ihip^rlir.li nail: Hi

7

3

zl

M 4

*

n\ Sheet 1 \sheet2 J 5heet3 f

hi

I

Figure 16-4: Microsoft Excel worksheets can be used to send search parameters to any search engine on the Internet (see procedure Work-sheet_BeforeDoubleClick in step 2 above).

w- rziir^rermrrTaijjui.inTrm^iinr^iTTrTrTM

Fie Ed* View Favorfcei Tools He3p

^Bodt - ■ iä ¡3 âSMtch LS FavoHhü! ."^Histoiy | jgjT J | -

Addreîî htlp //www.-EKavicta coni/cg-bpi/query

^Go altavista ^

|Arabic lessorts

Image

Audio Video Directory News Fjn,iFv nst^otr Setting »dp ^ I Any languege j^j Advanced

New" Refine youi seoich with AltaVista Prisma Click a term to focus your sea«ch orcti<*. le iepfa« youisearc—

Arabic Alphabet =•=■ Arabic Software =■> Islam » Spanish .=■=•

Arabic Books Courses - Islamic ■■■• Standard •

ArabicLanauage >> Giaromar ■-- Learning » Teacher

New" Refine youi seoich with AltaVista Prisma Click a term to focus your sea«ch orcti<*. le iepfa« youisearc—

Arabic Alphabet =•=■ Arabic Software =■> Islam » Spanish .=■=•

Arabic Books Courses - Islamic ■■■• Standard •

ArabicLanauage >> Giaromar ■-- Learning » Teacher

with a .NAME

^riSiurf

[email protected]

1 flpst+jaw£- last m name H

Sponsored Matches fi bout

Aiahfc Lssspn? No Pro b Ism

Learn Arabic wilh our award-winning software used by 4 million people worldwide. Sold on CD-ROM or with inslant access online.

Sponsored Matches fi bout

Aiahfc Lssspn? No Pro b Ism

Learn Arabic wilh our award-winning software used by 4 million people worldwide. Sold on CD-ROM or with inslant access online.

Purchase Arabic Tutorials

Choose from dozens of Arabic language tutorial-learning products at WorldLanguage.com,

Learn to understand and speak Aiabic - guaranteed Quickly learn Aialiie for your job, business or vacation, with truly effective Arabic courses. Hello Saudi Arabia, Iraq, Syria best prices Electronics

Find Camcorders:

Find DVD Players:

Figure 16-5: A web page opened from a Microsoft Excel worksheet lists topics that were found based on the criteria entered in a worksheet cell (see also Figure 16-4).

The Google LSI Handbook

The Google LSI Handbook

Here's your chance to learn the secret formula that only the top webmaster's know about, that helps them easily dominate any keyword term. Discover How To Unravel The Mysteries Of Googles Search Engine Rankings, and Stay One Step Ahead Of The Rest In The keywords War!

Get My Free Ebook


Post a comment