To create a hyperlink that goes to a Web page or other Internet
content, you must enter a valid URL as the hyperlink address.
You can enter a URL that points to any Internet file type or resource
supported by the browser or to an ActiveX control, such as the
WebBrowser control, that will be used to display or run it. For
example, the URL to the home page of the Microsoft Office Developer
Forum is:
http://www.microsoft.com/officedev/
When you enter a URL like the previous example that doesn't specify
a particular file name, be sure to include a slash mark (/) at
the end of the address. Although URLs that do not end in a slash
mark generally work, they require the server to perform additional
operations that add to the overall network load and slow down
the opening of the hyperlink. When you specify a file name at
the end of a URL, you do not end the URL with a slash mark. For
example:
http://www.microsoft.com/default.asp
If your users have Microsoft Internet Explorer version 3.0 or
if your application uses the WebBrowser control, your application
can open a Microsoft Excel workbook, Word document, or PowerPoint
presentation within the browser or control. To do this, the corresponding
Office application or viewer (Microsoft Excel Viewer, Word Viewer,
or PowerPoint Viewer) must also be installed on the user's computer.
In this case, a URL can point directly to an Office document on
a Web or intranet server. For example:
http://YourIntranetServer/YourWordDoc.doc
You can also open an Office document in Microsoft Internet Explorer
version 3.0 or in the WebBrowser control directly from the
standard file system, without using a Web server. To do so, use
the File protocol, as follows:
file://c:\my documents\sales.doc
Specifying a UNC or Standard Path as a Hyperlink Address
To create a hyperlink that starts a Microsoft Office 97 application
and opens one of its documents from a LAN, enter a universal naming
convention (UNC) path as the hyperlink address. This ensures that
the hyperlink continues to work if the document or the application
that contains the hyperlink is moved to another computer. A UNC
path starts with two backslashes (\\) and supplies the server
name, share name, and full path to the file. For example, a UNC
path to a Microsoft Excel workbook would be in the following format:
\\server\share\path\workbook.xls
You can also specify a network path that uses a mapped drive letter,
such as E:\path\workbook.xls.
However, because the path is specific to that drive letter, the
hyperlink only works if the user's computer has the drive letter
mapped to the appropriate server and share. If you want to create
a hyperlink that goes to a file on a local drive, you can use
a standard file path starting with a drive letter, such as C:\path\workbook.xls.
In this case, if the application is moved to another computer,
the hyperlink only works if the file specified in the address
is stored on the same drive and in the same folder on the new
computer.
You can also enter a UNC or standard file path as a hyperlink
address to open any file type that is registered on the computer
running your document or application. For example, if Notepad
is installed and registered to open text (.txt) files, you could
enter a UNC path to open a text file in the following format:
\\server\share\path\filename.txt
Absolute vs. Relative Links
When you create a hyperlink, you can use a path based on either
an absolute link or a relative link.
A path based on an absolute link points to a fixed file location.
Absolute links identify the destination of a hyperlink by its
full address such as C:\My Documents\Sales.doc or http:\\www.microsoft.com\default.htm.
Use an absolute link for hyperlinks to destinations that won't
be moved or that require a full path. For example, use absolute
links in hyperlinks to other Web sites, such as a list of your
favorite Web sites.
A path based on a relative link points to a destination relative
to the file the hyperlink is located in. When the first part of
the path is shared by both the file that contains the hyperlink
and the destination file, that part is called a hyperlink
base. For example, if the path to the file that contains
the hyperlink is C:\My Documents\Databases and the path to
the destination file is C:\My Documents\Workbooks, then C:\My
Documents is the hyperlink base. The hyperlink base address is
automatically added to the beginning of the path for all relative
links. You can specify the hyperlink base on the Summary
tab of the document's property sheet. To open a document's property
sheet in Microsoft Excel, Word, or PowerPoint, click Properties
on the File menu. To open the property sheet for a Microsoft Access
database, click Database Properties on the File
menu.
When a hyperlink uses a path based on a relative link, you can
move the file that contains the hyperlink and the destination
file without breaking the hyperlink if you move the destination
file to an identically named location. For example, if you set
the hyperlink base to C:\My Documents and then create a relative
link to a document in C:\My Documents\Workbooks, and you
move the document that contains the relative link to a new computer,
you must copy the destination file into an identically named folder
in C:\My Documents on the new computer. Alternatively, you can
move the destination file to a folder named Workbooks within another
folder (for example, D:\Applications), and then open the document
that contains the relative link and update the hyperlink base
to the new folder's name.
If you save a document or database object that contains relative
links as an HTML document, the hyperlink base is omitted from
the anchor tags created for those relative links. For example,
suppose you create a relative link to a database called Names.mdb
in C:\My Documents\Databases, and set the hyperlink base to C:\My Documents.
When you save the document as an HTML document, the anchor tag
created is <A HREF="Databases\Names.mdb">.
To keep the hyperlink from breaking in the HTML document, you
must create a Databases folder in the folder that contains the
HTML document on the HTTP server, and then copy the Names.mdb
database into that folder.
Using Objects and Collections to Work
with Hyperlinks
All Office 97 applications except Outlook provide objects
and collections that you can use to work with hyperlinks in Visual
Basic code. Although there are a great number of similarities
across each application, in some cases the objects and collections
that hyperlinks are associated with differ slightly in each Office
application.
To work with hyperlinks in Visual Basic code, you use the Hyperlink
object. In all Office 97 applications except Microsoft Access,
the Hyperlink object is a member of the Hyperlinks
collection. In Microsoft Access, the Hyperlink object is
a member of the Controls collection. The objects that can
contain a Hyperlinks collection differ for each application.
They are listed in the following table.
| Application |
Objects that can contain a Hyperlinks collection
|
| Microsoft Word | Document, Range, or Selection objects
|
| Microsoft Excel | Worksheet or Range objects
|
| Microsoft PowerPoint |
Slide or Master objects
|
| Microsoft Access | None. Microsoft Access doesn't have a Hyperlinks collection. All Hyperlink objects are members of the Controls collection. In addition, you can have a set of records that contains fields with the Hyperlink data type and use Visual Basic to work with the records as if they were a collection.
|
The objects that can have a Hyperlink object associated
with them differ for each application. The following table summarizes
which objects can have an associated Hyperlink object.
| Application |
Objects that can have a Hyperlink object associated with them
|
| Microsoft Word | Shape, InlineShape, Selection, or Range objects
|
| Microsoft Excel | Shape, Selection, or Range objects
|
| Microsoft PowerPoint |
Shape.ActionSettings or TextRange.ActionSettings objects
|
| Microsoft Access | CommandButton, ComboBox, Image, Label, ListBox, or TextBox objects
|
Adding New Hyperlink Objects to the Hyperlinks Collection
In Microsoft Excel and Word, use the Add method to create
a Hyperlink object and add it to the Hyperlinks
collection. To create a hyperlink with the Add method,
use the following syntax:
object.Add(anchor,
address, subaddress)
The following table describes the arguments of the Add
method.
| Argument | Description
|
| object | Required. An expression that returns a Hyperlink object.
|
| anchor | Required. The anchor for the hyperlink. Can be either a Range or a Shape object.
|
| address | Required. The address of the hyperlink.
|
| subaddress | Optional. The subaddress of the hyperlink.
|
Microsoft Word Examples
In Word, you can use the Add method to add a hyperlink
to either a Range object (a range or selection of text),
a Shape object (a graphic object), or an InlineShape
object (a graphic object within a line of text).
Creating a Hyperlink for a Microsoft Word Range Object
The following example inserts the text "Microsoft Web Site"
at the beginning of the active document, selects the inserted
text, and then adds a hyperlink to the text that goes to the Microsoft
home page at http://www.microsoft.com/.
Sub AddHyperlinkRange()
Dim r As Range
Set r = ActiveDocument.Range(Start := 0, End := 0)
r.InsertBefore "Microsoft Web Site"
Selection.MoveRight Unit := wdWord, Count := 3, Extend := wdExtend
ActiveDocument.Hyperlinks.Add Anchor := Selection.Range, _
Address := "http://www.microsoft.com/"
End Sub
Creating a Hyperlink for a Microsoft Word Shape Object
The following example creates a beveled shape, adds the text "Microsoft
Web Site" to the shape, and then adds a hyperlink to the
shape that goes to the Microsoft home page at http://www.microsoft.com/.
Sub AddHyperlinkShape()
ActiveDocument.Shapes.AddShape(msoShapeBevel, 150, 150, 100, 30).Select
With Selection
.ShapeRange.TextFrame.TextRange.Select
.Collapse
.TypeText Text:="Microsoft Web Site"
.ShapeRange.Select
End With
ActiveDocument.Hyperlinks.Add Anchor:=Selection.ShapeRange, _
Address:= "http://www.microsoft.com/"
End Sub
Creating a Hyperlink Associated with a Command Button
In addition to creating Hyperlink objects in Visual Basic
code, you can create a command button by using the user interface
and then add code to the command button's Click event procedure
to make it follow a hyperlink. This doesn't create a Hyperlink
object so the hyperlink isn't available in the document's Hyperlinks
collection. To create a command button that follows a hyperlink
in Microsoft Excel, Word, and PowerPoint, use the following procedure.
To
create a command button that follows a hyperlink
- Rightclick the menu
bar and then click Control Toolbox on the shortcut menu.
- In
the toolbox, click the Command Button tool, and then click
where you want to place the command button.
- Rightclick
the command button, and then click Properties on the shortcut
menu.
- In the Caption property box, enter
the text you want on the button. Set any other properties you
want to control the button's appearance and then close the Properties
dialog box.
- Rightclick the command button,
and then click View Code. Enter a procedure that uses the
FollowHyperlink method in the button's Click event. For
example:
Private Sub CommandButton1_Click()
FollowHyperlink "http://www.microsoft.com/"
End Sub
- Save
the code, and exit Design mode to test the button.
Note When
using this method, the command button doesn't display blue underlined
text or the hand cursor when the mouse is over the button.
For more information about the FollowHyperlink method,
see "The FollowHyperlink Method" later in this chapter.
Microsoft Excel Examples
In Microsoft Excel, you can use the Add method to add a
hyperlink to either a Range object (a range of one or more
cells) or a Shape object (a graphic).
Creating a Hyperlink for a Microsoft Excel Range Object
The following example adds the display text "MSN Web site"
to cell A1 in the first worksheet in the current workbook, and
then adds a hyperlink to that range that goes to the Web site
at http://www.msn.com/.
Sub AddHyperlink_Range()
Dim wrk As Worksheet
Set wrk = ActiveWorkbook.Sheets(1)
wrk.Range("A1").Value = "MSN Web site"
wrk.Hyperlinks.Add Address := "http://www.msn.com/", _
Anchor := wrk.Range("A1")
End Sub
Creating a Hyperlink for a Microsoft Excel Shape Object
The following example adds a rounded rectangle labeled "Click
Here" to the first worksheet in the current workbook, and
then adds a hyperlink to the rectangle that goes to cell C6 on
the first sheet of Book2.xls.
Sub AddHyperlink_Shape()
Dim wrk As Worksheet
Dim shp As Shape
Set wrk = ActiveWorkbook.Sheets(1)
Set shp = wrk.Shapes.AddShape(msoShapeRoundedRectangle, 50, 50, 100, 50)
shp.Select
Selection.Characters.Text = "Click Here"
wrk.Hyperlinks.Add Anchor := shp, Address := "C:\My Documents\Book2.xls", _
SubAddress := "Sheet1!C6"
End Sub
Microsoft PowerPoint Examples
PowerPoint doesn't use the Add method to create a new hyperlink.
Instead, you create a hyperlink by working with the ActionSettings
collection of a Shape object (a graphic) or a TextRange
object (text associated with a Shape object). A Shape
or TextRange object can have two different hyperlinks assigned
to it: one that's followed when the user clicks the object during
a slide show, and another that's followed when the user passes
the mouse pointer over the object during a slide show.
To specify which mouse action to work with, first use the ActionSettings
property to return the ActionSettings collection, then
use ActionSettings(index), where index
is either ppMouseClick or ppMouseOver. Set the Action
property to ppActionHyperlink to specify that the action
is a hyperlink. After a hyperlink is created, it's available from
the Hyperlinks collections for the Shape, TextRange,
and Slide objects.
Creating a Hyperlink for a Microsoft PowerPoint Shape Object
The following example adds a Custom action button with text that
reads "Microsoft.com" to the first slide in the active
presentation, and then adds a hyperlink to the button that goes
to the Microsoft home page.
Sub AddHyperlinkButton()
Dim sld As Slide, shp As Shape
Set sld = ActivePresentation.Slides(1)
Set shp = sld.Shapes.AddShape(msoShapeActionButtonCustom, 50, 50, 160, 30)
With shp.TextFrame
.TextRange.Text = "Microsoft.com"
.MarginBottom = 5
.MarginLeft = 5
.MarginRight = 5
.MarginTop = 5
End With
With shp.ActionSettings(ppMouseClick)
.Action = ppActionHyperlink
.Hyperlink.Address = "http://www.microsoft.com/"
End With
End Sub
Creating a Hyperlink for a Microsoft PowerPoint TextRange
Object
The following example adds a rectangle to the first slide in the
active presentation, adds text to the rectangle, and then adds
a hyperlink to the text. This example defines a hyperlink for
all the text in the text range. It is possible to define more
than one hyperlink within a text range for selected characters.
Sub AddHyperlinkText()
Dim sld As Slide, shp As Shape, txt As Text
Set sld = ActivePresentation.Slides(1)
Set shp = sld.Shapes.AddShape(msoShapeRectangle, 0, 0, 250, 140)
shp.TextFrame.TextRange.Text = "Microsoft Web Site"
Set txt = shp.TextFrame.TextRange
With txt.ActionSettings(ppMouseClick)
.Action = ppActionHypertext
.Hyperlink.Address = "http://www.microsoft.com/"
End With
End Sub
Microsoft Access Example
Microsoft Access doesn't provide a Hyperlinks collection
or use the Add method to create a hyperlink on a form or
report. Instead, you create hyperlinks for label, command button,
and image controls by setting either the HyperlinkAddress
property or the HyperlinkSubAddress property, or both,
of the control.
Note You
can also create a field with the Hyperlink data type to store
hyperlink addresses in a table, and then bind that field to a
text box, list box, or combo box on a form. For more information,
see "Storing Hyperlinks in Microsoft Access Tables"
later in this chapter.
Creating a Hyperlink Control in Microsoft Access
The following example creates a new label on a form and then sets
the HyperlinkAddress and HyperlinkSubAddress properties
to create a hyperlink. When you create a hyperlink in Visual Basic,
and you want it to be colored and underlined, you must also explicitly
set the ForeColor and FontUnderline properties.
Sub CreateHyperlinkLabel(strForm As String, xPos As Integer, _
yPos As Integer, strCaption As String, Optional strAddress As String, _
Optional strSubAddress As String)
Dim ctlLabel As Control
' Open form, hidden in Design view.
DoCmd.OpenForm strForm, acDesign,,,,acHidden
' Create label control with text specified by strCaption, at
' the position specified by xPos and yPos.
Set ctlLabel = CreateControl(strForm, acLabel, , "", _
strCaption, xPos, yPos)
' Set hyperlink address, text color, and underline.
With ctlLabel
.HyperlinkAddress = strAddress
.HyperlinkSubAddress = strSubAddress
.ForeColor = "1279872587"
.FontUnderline = True
End With
' Save form.
DoCmd.Save acForm, strForm
End Sub
To use this example to create a hyperlink, you must specify the
form, position, display text, and hyperlink address. For example,
enter the following code into the Debug window:
CreateHyperlinkLabel "Form1",100,100,"Microsoft Web Site","http://www.microsoft.com/"
You can use similar code to create image and command button controls
and set properties to create a hyperlink.
Referring to Hyperlink Objects
Use the Hyperlink property to return a reference to a Hyperlink
object. The objects that can have a Hyperlink object associated
with them differ somewhat for each application.
Microsoft Word Example
Use the Hyperlink property to return the hyperlink for
a shape. Note that a shape can have only one hyperlink associated
with it. The following example follows the hyperlink associated
with the first shape in the active document.
ActiveDocument.Shapes(1).Hyperlink.Follow
Selection and Range objects can have multiple Hyperlink
objects associated with them. For these objects, you must either
loop through the object's Hyperlinks collection or specify
a member of the object's Hyperlinks collection by using
the Item method. The following example loops through the
hyperlinks in the current selection.
Dim H As Hyperlink, hLinks As Hyperlinks
Set hLinks = Selection.Hyperlinks
For Each H In hLinks
MsgBox H.Address
Next H
The following example displays the address of the first hyperlink
in the first 20 characters of the current document in the Immediate
window.
Debug.Print ActiveDocument.Range(0,20).Hyperlinks(1).Address
Microsoft Excel Example
Use the Hyperlink property to return the hyperlink for
a shape. Note that a shape can have only one hyperlink associated
with it. The following example follows the hyperlink associated
with the first shape on the first worksheet.
Worksheets(1).Shapes(1).Hyperlink.Follow NewWindow:=True
Microsoft PowerPoint Example
As mentioned earlier in this chapter, a shape in PowerPoint can
have up to two different hyperlinks assigned to it: one that's
followed when the user clicks the shape during a slide show, and
another that's followed when the user passes the mouse pointer
over the shape during a slide show. To return a hyperlink for
a shape, you must first reference the appropriate member of the
ActionSettings collection (ppMouseOver or ppMouseClick),
and then use the Hyperlink property.
The following example displays the address for the mouseclick
hyperlink of the third shape on the first slide of the active
presentation in the Immediate window.
Debug.Print ActivePresentation.Slides(1).Shapes(3). _
ActionSettings(ppMouseClick).Hyperlink.Address
Microsoft Access Example
In Microsoft Access, you can use the Hyperlink property
to return a reference to the Hyperlink object associated
with a CommandButton, ComboBox, Image, Label,
ListBox, or TextBox control.
The CreateHyperlink procedure in the following example sets the
Address and SubAddress properties for a label, image
control, or command button to the values passed to the procedure.
The Address property setting is optional, because a hyperlink
to a database object in the current database uses only the SubAddress
property.
To try this example, create a form with two text box controls
named txtAddress and txtSubAddress, and a command button named
cmdFollowLink. Then paste the sample code into the Declarations
section of the form's module. Display the form in Form view, enter
appropriate values in the txtAddress and txtSubAddress text boxes,
and click the cmdFollowLink button.
Private Sub cmdFollowLink_Click()
CreateHyperlink Me!cmdFollowLink, Me!txtSubAddress, Me!txtAddress
End Sub
Sub CreateHyperlink(ctlSelected As Control, txtSubAddress As TextBox, _
Optional txtAddress As TextBox)
Dim hlk As Hyperlink
Select Case ctlSelected.ControlType
Case acLabel, acImage, acCommandButton
Set hlk = ctlSelected.Hyperlink
With hlk
If Not IsMissing(txtAddress) Then
.Address = txtAddress
Else
.Address = ""
End If
.SubAddress = txtSubAddress
.Follow
.Address = ""
.SubAddress = ""
End With
Case Else
MsgBox "The control '" & ctlSelected.Name & "' does not support hyperlinks."
End Select
End Sub
Referring to a Hyperlink Object by Its Position in the Hyperlinks
Collection
Use the Item method (or the Item property in Microsoft
Excel) of the Hyperlinks collection to return a single
Hyperlink object based on its position in the collection.
The first object in the collection has an Item value of
1. The Item method is the default member of the Hyperlinks
collection, so you can refer to the Item method in either
of the following ways:
Hyperlinks.Item(1)
Hyperlinks(1)
Microsoft Word Example
The following example follows the first hyperlink in the selection.
If Selection.Hyperlinks.Count >= 1 Then
Selection.Hyperlinks(1).Follow
End If
Note The
Count property for the Hyperlinks collection of a Selection object
returns the number of items in the main story only. To count items
in other stories, specify the story in the StoryRanges collection.
For example, to count all of the hyperlinks in the primary footer
story you can use the following code.
ActiveDocument.StoryRanges(wdPrimaryFooterStory).Hyperlinks.Count.
Microsoft Excel Example
The following example uses the Follow method to activate
the second hyperlink in the range of cells from E5 to E8.
Worksheets(1).Range("E5:E8").Hyperlinks(2).Follow
Microsoft PowerPoint Example
The following example sets the Address property of the
second hyperlink on the first slide in the current PowerPoint
presentation.
ActivePresentation.Slides(1).Hyperlinks(2).Address = "C:\New\Newsales.ppt"
Looping Through the Hyperlinks Collection
You can use the Hyperlinks collection in Microsoft Excel,
Word, and PowerPoint to loop through the set of Hyperlink
objects associated with an object. In Microsoft Access, you can
loop through the Controls collection or a set of records
to work with the hyperlinks in your application.
The following examples perform operations on a Hyperlinks
collection that contains existing Hyperlink objects. In
the Microsoft Excel, Word, and PowerPoint examples that follow,
the object that contains the Hyperlinks collection is specific
to the application. However, you can modify each example to run
in another application by referring to the appropriate object.
Because the Microsoft Access examples use the Controls
collection or a set of records instead of the Hyperlinks
collection, you can only use them in Microsoft Access.
Microsoft Word Example
If the active document includes hyperlinks, this example inserts
a list of the hyperlink destinations at the end of the document.
Dim hLink As Hyperlink
Set myRange = ActiveDocument.Range(Start:=ActiveDocument.Content.End - 1)
Count = 0
For Each hLink In ActiveDocument.Hyperlinks
Count = Count + 1
With myRange
.InsertAfter "Hyperlink #" & Count & vbTab
.InsertAfter hLink.Address
.InsertParagraphAfter
End With
Next hLink
Microsoft Excel Example
The following example updates all hyperlinks on the first worksheet
in the active workbook that have the specified address.
Dim hLink As Hyperlink
For Each hLink in ActiveWorkbook.Sheets(1).Hyperlinks
If LCase(hLink.Address) = "C:\Current Work\Sales.ppt" Then
hLink.Address = "C:\New\Newsales.ppt"
End If
Next hLink
Note In
Word, you can use the Hyperlinks collection to access hyperlinks
created by inserting a HYPERLINK field. In Microsoft Excel, however,
you cannot use the Hyperlinks collection to access hyperlinks
created by entering a formula using the HYPERLINK function.
Microsoft PowerPoint Example
The following example updates an outdated Internet address for
all hyperlinks in the active presentation.
Dim hLink As Hyperlink
Dim S As Slide
oldAddr = InputBox("Old internet address")
newAddr = InputBox("New internet address")
For Each S In ActivePresentation.Slides
For Each hLink In s.Hyperlinks
If LCase(hLink.Address) = Lcase(oldAddr) Then hLink.Address = newAddr
Next hLink
Next S
Microsoft Access Examples
Microsoft Access doesn't support the Hyperlinks collection,
but you can loop through the Controls collection on a form
or report to work with the hyperlinks associated with any control
on the form or report. The following procedure displays the name
and hyperlink address values for controls that contain hyperlinks
in the Debug window.
Sub ListHyperlinks(strForm As String)
Dim Frm As Form
Dim Ctl As Control
DoCmd.OpenForm strForm, acDesign, , , , acHidden
Set Frm = Forms(strForm)
' Ignore controls without hyperlinks.
On Error Resume Next
For Each Ctl In Frm.Controls
If Not (Ctl.ControlType = acTextBox) Then
Debug.Print "Control:" & Ctl.Name & vbCrLf & _
"Address:" & Ctl.Hyperlink.Address & vbCrLf & _
"Subaddress:" & Ctl.Hyperlink.SubAddress & vbCrLf
Else
Debug.Print "Control:" & Ctl.Name & vbCrLf & _
"Text box control bound to Hyperlink field " & _
Ctl.ControlSource & vbCrLf
End If
Next Ctl
Frm.Close
End Sub
In addition to creating Hyperlink objects that belong to
the Controls collection of forms and reports, you can have
a set of records that contains fields with the Hyperlink data
type and use Visual Basic to work with the records as if they
were a collection. For example, you can loop through the records
in a table to work with the properties of a field. The following
procedure works with the Suppliers table in the Northwind sample
database. If a field is a Hyperlink field, the procedure loops
through all the records in the table. If a field is not null (empty),
it displays the record number, field name, and displayed value
in the Debug window.
Sub HyperlinkRecordset()
Dim dbs As Database
Dim rstSuppliers As Recordset
Dim fldField As Field
' Return reference to current database.
Set dbs = CurrentDb
' Create dynaset-type Recordset object.
Set rstSuppliers = dbs.OpenRecordset("Suppliers", dbOpenDynaset)
' Print displayed value for fields containing hyperlinks.
For Each fldField In rstSuppliers.Fields
If (fldField.Attributes And dbHyperlinkField) Then
With rstSuppliers
Do While Not .EOF
If Not IsNull(fldField.Value) Then
Debug.Print rstSuppliers.AbsolutePosition + 1 & " " & _
fldField.Name & " " & _
HyperlinkPart(fldField.Value, acDisplayedValue)
End If
.MoveNext
Loop
.MoveFirst
End With
End If
Next fldField
' Free all object variables.
rstSuppliers.Close
Set dbs = Nothing
End Sub
For more information about Hyperlink fields, see "Storing Hyperlinks in Microsoft Access Tables" later in this chapter.
Using Methods and Properties to Work with
Hyperlinks
The following table summarizes the methods and properties you
can use to work with hyperlinks in Visual Basic.
| Method or property name
| Description |
| Hyperlink property
| Returns a reference to a hyperlink object in code.
|
| Follow method
| Follows a hyperlink defined by an existing Hyperlink object. The Follow method has the same effect as clicking the hyperlink.
|
| FollowHyperlink method
| Follows a hyperlink address specified in code or passed to the method from a text box. For example, you can prompt a user to type a hyperlink address in a dialog box or form, and then use the FollowHyperlink method to go to that address.
|
| ExtraInfoRequired property(Word only)
| A read-only property that returns True if extra information is required to resolve the specified hyperlink. You can specify extra information, such as a file name or a query string, by using the extrainfo argument with the Follow or FollowHyperlink methods.
|
| AddToFavorites method
| Adds a shortcut to the Favorites folder. The AddToFavorites method can reference a Hyperlink object or the current document (Microsoft Access database, Microsoft Excel workbook, Microsoft PowerPoint presentation, or Microsoft Word document).
|
| Address property
| Returns the address of the specified hyperlink. This property is read/write, except in Word, where it is read-only.
|
| Subaddress property
| Returns a named location in the destination of the specified hyperlink. The named location can be a bookmark (Microsoft Word), a named cell or cell reference (Microsoft Excel), a database object (Microsoft Access), or a slide number (Microsoft PowerPoint). This property is read/write, except in Word, where it is read-only.
|
| Type property (Microsoft Excel, Word, and PowerPoint only)
| Returns the type of object the hyperlink is associated with. Can be one of the following constants:
msoHyperlinkInlineShape (Word only)
msoHyperlinkRange
msoHyperlinkShape
|
| HyperlinkAddress property(Microsoft Access only)
| Sets or returns the address of a hyperlink for a label, image control, or command button. The HyperlinkAddress property is equivalent to setting or returning the Address property for the control in Visual Basic; for example, object.HyperlinkAddress is equivalent to object.Hyperlink.Address. You can also set the HyperlinkAddress property in the control's property sheet.
|
| HyperlinkSubAddress property(Microsoft Access only)
| Sets or returns the location within the Office document or object specified by the HyperlinkAddress property. When no HyperlinkAddress property is specified, HyperlinkSubAddress specifies a database object in the current database.
The HyperlinkSubAddress property is equivalent to setting or returning the SubAddress property for the control in Visual Basic; for example, object.HyperlinkSubAddress is equivalent to object.Hyperlink.SubAddress. You can also set the HyperlinkSubAddress property in the control's property sheet.
|
For more information about these methods and properties, search
Help in the appropriate application for the name of the method
or property.
The Follow Method
The Follow method follows a hyperlink defined by an existing
Hyperlink object, and has the same effect as clicking the
hyperlink. The Follow method downloads the document or
Web page specified by the hyperlink address associated with a
Hyperlink object and opens it in the appropriate application.
If the hyperlink refers to a file system path or uses the File
protocol, the Follow method opens the document instead
of downloading it.
The syntax for the Follow method is:
expression.Follow(newwindow,
addhistory, extrainfo, method,
headerinfo)
The following table describes the arguments of the Follow
method.
| Argument | Description
|
| expression | Required. An expression that returns a Hyperlink object.
|
| newwindow | Optional. A Boolean value where True ( – 1) opens the document in a new window and False (0) opens the document in the current window. The default value is False.
|
| addhistory | Optional. A Boolean value where True ( – 1) adds the hyperlink to the History folder and False (0) doesn't add the hyperlink to the History folder. The default value is True.
|
| extrainfo | Optional. A string or an array of Byte data that specifies additional information for HTTP to use to resolve the hyperlink.
For example, you can use the extrainfo argument to specify the coordinates of an image map or the contents of a form. The string is either appended or posted, depending on the value of the method argument. In Word, you can use the ExtraInfoRequired property to determine whether extra information is required.
|
| method | Optional. Specifies the way the extrainfo argument is handled. You can set the method argument to msoMethodGet or msoMethodPost.
|
| headerinfo | Optional. A string that specifies header information for the HTTP request. The default value is a zero-length string (" "). You can combine several header lines into a single string by using the following syntax:
"string1" & vbCr & "string2"
The specified string is automatically converted into ANSI characters. Note that the headerinfo argument may overwrite default HTTP header fields.
|
For the method argument of the Follow
method, you can specify one of the constants described in the
following table.
| Constant | Description
|
| msoMethodGet
| The extrainfo argument is a string that's appended to the URL, separated by a question mark, when you use the HTTP GET method from an HTML form. For example, you can submit a query to an HTTP server by using an address in the following format:
http://www.web.com/cgi-bin/srch?item1+item2
item1+item2 is the extra information that's passed to the srch program on the HTTP server.
|
| msoMethodPost |
The extrainfo argument is posted to the server as a string or a byte array when you use the HTTP POST method. For example, data from a form is typically submitted to an HTTP server with a series of name/value pairs in the following format:
name1=value1&name2=value2
This data can be submitted as either a string or byte array, depending on what format the program on the server has been programmed to use. Use the HTTP POST method to submit extra information if the program on the HTTP server is reading the form's data from the standard input stream (STDIN).
|
For examples that illustrate uses of the Follow method,
see the code samples in previous sections of this chapter.
The FollowHyperlink Method
The FollowHyperlink method follows a hyperlink address
specified in code or passed to the method from a variable or object.
For example, you can prompt a user to type a hyperlink address
in a dialog box, and then use the FollowHyperlink method
to go to that address. The FollowHyperlink method downloads
the document or Web page specified by the hyperlink address associated
with a Hyperlink object and opens it in the appropriate
application. If the address refers to a file system path or uses
the File protocol, the FollowHyperlink method opens the
document instead of downloading it.
The syntax for the FollowHyperlink method is:
expression.FollowHyperlink(address,
subaddress, newwindow, addhistory,
extrainfo, method, headerinfo)
The following table describes the arguments of the FollowHyperlink
method.
| Argument | Description
|
| expression | Required. An expression that returns one of the following objects:
Microsoft Word Document objectMicrosoft Excel Workbook objectMicrosoft PowerPoint Presentation objectMicrosoft Access Application object
|
| address | A string expression that evaluates to a valid hyperlink address.
|
| subaddress | A string expression that evaluates to a named location in the document specified by the address argument. The default is a zero-length string (" "). If no address is specified, subaddress specifies a named location in the document or database.
|
For information about the newwindow, addhistory,
extrainfo, method, and headerinfo
arguments, see the preceding section, "The Follow
Method."
Microsoft Word Examples
This example follows the specified URL and displays the Microsoft
home page in a new window.
ActiveDocument.FollowHyperlink Address:="http://www.microsoft.com", _
NewWindow:=True, AddHistory:=True
This example opens the HTML document named Default.htm directly
from the local hard disk.
ActiveDocument.FollowHyperlink Address:="file://C:\Pages\Default.htm"
Microsoft Excel Example
This example follows the specified URL address and displays the
names of all the topics related to opera.
ActiveWorkbook.FollowHyperlink Address:="http://search.Yahoo.com/bin/search", _
AddHistory:=False, Method:=msoMethodGet, ExtraInfo:="p=Opera"
Microsoft PowerPoint Example
This example loads the document at www.gohere.com in a new window
and adds it to the History folder.
Application.ActivePresentation.FollowHyperlink _
Address:="http://www.gohere.com", NewWindow:=True, AddToHistory:=True
Microsoft Access Examples
The following function prompts a user for a hyperlink address
and then follows the hyperlink.
Function GetUserAddress() As Boolean
Dim strInput As String
On Error GoTo Error_GetUserAddress
strInput = InputBox("Enter a valid address")
Application.FollowHyperlink strInput, , True
GetUserAddress = True
Exit_GetUserAddress:
Exit Function
Error_GetUserAddress:
MsgBox Err & ": " & Err.Description
GetUserAddress = False
Resume Exit_GetUserAddress
End Function
You can call this function with a procedure such as the following.
Sub CallGetUserAddress()
If GetUserAddress = True Then
MsgBox "Successfully followed hyperlink."
Else
MsgBox "Could not follow hyperlink."
End If
End Sub
In Microsoft Access, you can also use the FollowHyperlink
method to specify a hyperlink for controls that don't support
the HyperlinkAddress or HyperlinkSubAddress properties
(controls other than labels, image controls, and command buttons,
or text boxes bound to Hyperlink fields).
This example uses the FollowHyperlink method to add hyperlink
behavior to an unbound object frame control. Add the following
code to the Click event of an unbound object frame named OLEUnbound1
to start a Web browser and open the specified hyperlink address
when you click the image.
Note
You can use similar code in Microsoft Excel, Word, or PowerPoint
to create a command button that follows a hyperlink. To do so,
add a command button by using the Control Toolbox, and then define
a Click event procedure for the button. For more information,
see "Creating a Hyperlink Associated with a Command Button"
earlier in this chapter.
Private Sub OLEUnbound1_Click()
Dim strAddress As String
On Error GoTo Error_OLEUnbound1
' Set reference to hyperlink address.
strAddress = "http://www.microsoft.com/"
' Follow hyperlink address.
Application.FollowHyperlink strAddress, , True
Exit_OLEUnbound1:
Exit Sub
Error_OLEUnbound1:
MsgBox Err & ": " & Err.Description
Resume Exit_OLEUnbound1
End Sub
Tip Using
the FollowHyperlink method to add hyperlinks to controls
that don't support the HyperlinkAddress or HyperlinkSubAddress
properties doesn't provide any feedback to the user to indicate
that the control can follow a hyperlink. One way to inform a user
that the control contains a hyperlink is to set the control's
ControlTipText property so that a text message appears
when the user rests the pointer on the control.
Handling Hyperlink Errors
If an error occurs when using the Follow or FollowHyperlink
methods in Visual Basic, an Automation error is displayed that
contains only an error number in both decimal and hexadecimal
format. For example, if xyz.htm doesn't exist, and you run the
following code in Microsoft Access:
Application.FollowHyperlink "http://www.microsoft.com/xyz.htm"
the error message shown in the following illustration occurs.
This error number indicates that the requested item could not
be found.
You can prevent these error messages from being displayed to users
of your application. To do so, check the Number property
of the Err object against a decimal value in the table
that follows. Then handle the error by either returning an appropriate
message or performing a suitable action. If you want to be certain
that all errors are handled, write an error handler that traps
the entire set of error numbers.
Function GetUserAddress() As Boolean
Dim strInput As String
Dim lngErrNumber As Long
On Error GoTo Error_GetUserAddress
strInput = InputBox("Enter a valid address")
Application.FollowHyperlink strInput, , True
GetUserAddress = True
Exit_GetUserAddress:
Exit Function
Error_GetUserAddress:
' Set variable equal to error number.
lngErrNumber = Err.Number
' Check variable against all possible error numbers.
Select Case lngErrNumber
Case -2146697211
MsgBox "Cannot locate the Internet server or proxy server."
Case -2146697210
MsgBox "The site reports that the item you requested " _
& "could not be found. (HTTP/1.0 404)"
.
. ' Repeat for all possible error numbers.
.
End Select
GetUserAddress = False
Resume Exit_GetUserAddress
End Function
The following table lists the error numbers and descriptions for
all errors that can occur when using the Follow and FollowHyperlink
methods.
| Decimalerror number
| Hexadecimalerror number
| Description |
| – 2146697214, – 2147221020 and – 2147012891
| 0x800C0002, 0x800401E4 and 0x80072EE5
| The address of this site is not valid. Check the address and try again.
|
| – 2146697213
| 0x800C0003 | Cannot start an Internet session.
|
| – 2146697212 and – 2147012867
| 0x800C0004 and 0x80072EFD
| Cannot connect to the Internet server.
|
| – 2146697211
| 0x800C0005 | Cannot locate the Internet server or proxy server.
|
| – 2146697210 and – 2147012868
| 0x800C0006 and 0x80072EFC
| The site reports that the item you requested could not be found. (HTTP/1.0 404)
|
| – 2146697209
| 0x800C0007 | The Internet site reports that a connection was established but the data is not available.
|
| – 2146697208
| 0x800C0008 | Cannot download the information you requested.
|
| – 2146697207
| 0x800C0009 | The item you requested requires proper authentication. (HTTP/1.0 401)
|
| – 2146697206
| 0x800C000A | The Internet site cannot return the object you requested. (HTTP/1.0 403)
|
| – 2146697205 and – 2147012894
| 0x800C000B and 0x80072EE2
| The connection to this Internet site took longer than the allotted time.
|
| – 2146697204
| 0x800C000C | The site reports that the request is not valid.
|
| – 2146697203 and – 2147012888
| 0x800C000D and 0x80072EE8
| The required Internet protocol is not installed on your computer, or the Internet address you requested may not be valid.
|
| – 2146697202
| 0x800C000E | A security problem has occurred.
|
| – 2146697201 and – 2147221014
| 0x800C000F and 0x800401EA
| Cannot open the specified file.
|
| – 2146697200
| 0x800C0010 | Cannot start the program needed to open this file.
|
| – 2147221018 and – 2147221164
| 0x800401E6 and 0x80040154
| No program is registered to open this file.
|
| – 2147467260
| 0x80004004 | The hyperlink cannot be followed to the destination.
|
Note In
PowerPoint, one error number is returned for all Follow or FollowHyperlink
method errors: – 2147467259 (0x80004005).
The AddToFavorites Method
The AddToFavorites method adds a shortcut to the Favorites
folder in the Windows program folder.
The syntax for the AddToFavorites method is:
expression.AddToFavorites
Expression is an expression that returns either
a Hyperlink object or one of the objects listed in the
following table.
| Application |
Object |
| Microsoft Word | Document
|
| Microsoft Excel | Workbook
|
| Microsoft PowerPoint |
Presentation |
| Microsoft Access
| Application |
When referring to a Hyperlink object, the shortcut is the
friendly name of the document. The friendly name is determined
by the text in the <TITLE> HTML tag. If the document
doesn't have a friendly name, the shortcut name is resolved by
the application. If there is an existing shortcut of the same
name, it is overwritten without notification.
Microsoft Word Examples
In Word, the shortcut created by the AddToFavorites method
can refer to a Document object or to a Hyperlink
object.
The following example creates a shortcut to Sales.doc and adds
it to the Favorites folder. If Sales.doc isn't currently open,
Word opens it from the C:\My Documents folder.
Sub AddDocument()
Dim isOpen As Boolean, doc As Document
For Each doc In Documents
If LCase(doc.Name) = "Sales.doc" Then isOpen = True
Next doc
If isOpen <> True Then Documents.Open _
FileName:="C:\My Documents\Sales.doc"
Documents("Sales.doc").AddToFavorites
End Sub
To add an existing hyperlink in the document to the Favorites
folder, you must refer to the document's Hyperlinks collection.
The following example adds all of the hyperlinks in the document
to the Favorites folder.
Sub AddHyperlinks()
Dim H As Hyperlink
Dim Hlinks As Hyperlinks
Set Hlinks = ActiveDocument.Hyperlinks
For Each H In Hlinks
H.AddToFavorites
Next H
End Sub
Microsoft Excel Examples
In Microsoft Excel, the shortcut created by the AddToFavorites
method can refer to a Workbook object or to a Hyperlink
object.
To create a shortcut to the current workbook and add it to the
Favorites folder, use the following code.
ActiveWorkbook.AddToFavorites
To add an existing hyperlink in the current workbook to the Favorites
folder, you must refer to the workbook's Hyperlinks collection.
For example, to create a shortcut to the address in the first
hyperlink in the active workbook and add it to the Favorites folder,
use the following code.
ActiveWorkbook.Sheets(1).Hyperlinks(1).AddToFavorites
Microsoft PowerPoint Examples
In PowerPoint, the shortcut created by the AddToFavorites
method can refer to a Presentation object or to a Hyperlink
object.
To add a shortcut to the current presentation, use the following
code.
Application.ActivePresentation.AddToFavorites
To add a hyperlink in the current slide to the Favorites folder,
you must refer to the slide's Hyperlinks collection. The
following example adds all of the hyperlinks on the first slide
of the current presentation to the Favorites folder.
Sub AddHyperlinks()
Dim H As Hyperlink
Dim Hlinks As Hyperlinks
Set Hlinks = ActivePresentation.Slides(1).Hyperlinks
For Each H In Hlinks
H.AddToFavorites
Next H
End Sub
Microsoft Access Examples
In Microsoft Access, the shortcut created by the AddToFavorites
method can refer to the Application object, which represents
the current database, or to a hyperlink associated with a Control
object.
To create a shortcut to the current database and add it to the
Favorites folder, use the following code.
Application.AddToFavorites
To refer to a hyperlink associated with a Control object,
you must use the Hyperlink property to access the Hyperlink
object. For example, to create a shortcut to a hyperlink defined
for a command button named Command0 on the current form and add
it to the Favorites folder, use the following code.
Me!Command0.Hyperlink.AddToFavorites
Storing Hyperlinks in Microsoft Access
Tables
In Microsoft Access 97, you can create a field with the Hyperlink
data type to store hyperlink addresses in a table. You can follow
a hyperlink stored in a Microsoft Access table by clicking it
in the table. However, typically the field is bound to a text
box, list box, or combo box control on a form. Like other bound
fields, as the user moves from record to record, the value in
the control changes to display the current record's hyperlink
value. For example, you can use hyperlinks in this way to create
an application in which users can go to Web pages, or to other
content on the Internet or an intranet, from a predefined list
of addresses.
In addition to storing hyperlinks to Internet addresses, you can
also use hyperlinks in Microsoft Access to go to database objects
and other Office documents. For example, you could create a document
management application that uses a Hyperlink field to store paths
to Word documents on a network. Users of such an application could
add records to track new documents, or click the hyperlink in
a previously added record to open the specified document.
To create a Hyperlink field, add a field in table Design view
and set its DataType property to Hyperlink. You can also
create a Hyperlink field in table Datasheet view by clicking Hyperlink
Column on the Insert menu.
The Hyperlink Field Storage Format
In Microsoft Access, a Hyperlink field stores up to three pieces
of information: the displaytext, the address,
and the subaddress. Each piece is separated
by a pound sign (#), in the following format:
displaytext#address#subaddress
The following table describes each piece of the Hyperlink field
storage format.
| Piece | Description
| Required? |
| displaytext | The text the user sees in the Hyperlink field in a table, or in a text box bound to the Hyperlink field. You can set the display text to any text string.
For example, you may want the display text to be a descriptive name for the Web site or object specified by the address and subaddress. If you do not specify display text, Microsoft Access displays the value of address, or subaddress if address is also not specified.
| No |
| address
| A valid URL that points to a page or file on the Internet or an intranet, or the path to a file on a local hard disk or LAN.
If you enter a path on a LAN, you can omit a mapped drive letter and use the universal naming convention (UNC) format: \\server\share\path\filename. This prevents the path from becoming invalid if the database is later copied to another computer's hard disk or into a shared network folder.
| Yes, unless subaddress points to an object in the current database (.mdb) file.
|
| subaddress
| The location within a file or document; for example, a database object, such as a form or report. When referring to a database object, the name of the object should be preceded by its type: Table, Query, Form, Report, Macro, or Module. Other possible values for subaddress include a bookmark in a Word document, a NAME anchor tag in an HTML document, a PowerPoint slide, or a cell in a Microsoft Excel worksheet.
| No |
Each piece of the Hyperlink field storage format can be up to
2,000 characters. The maximum length of the entire Hyperlink field
value is 6,000 characters.
The following table gives examples of valid Hyperlink field values.
| Hyperlink field value
| Goes to |
| Cajun Delights#http://www. cajundelights.com/cajun.htm#
| The Cajun Delights Web page. Only the words "Cajun Delights" are displayed in the field or control.
|
| #http://www.cajundelights.com/cajun.htm#
| The Cajun Delights Web page. The text "http://www.cajundelights.com" appears in the field or control because no display text is specified.
|
| #http://www.cajundelights. com/cajun.htm#Price
| The HTML anchor with the NAME attribute Price on the Cajun Delights Web page. The text "http://www.cajundelights. com/cajun.htm" is displayed.
|
| Resume#c:\windows\personal\resume.doc#
| A Microsoft Word file named Resume.doc located in the \Windows\Personal folder. Only the word "Resume" is displayed in the field or control.
|
| #c:\windows\personal\resume.doc#
| A Microsoft Word file named Resume.doc located in the \Windows\Personal folder. The text "c:\windows\personal\resume.doc" appears in the field or control because no display text is specified.
|
| #c:\windows\personal\resume. doc#Qualifications
| The section in the Resume.doc Word file marked with the bookmark name Qualifications. The text "c:\windows\personal\resume.doc" is displayed.
|
| #\\databases\samples\northwind.mdb#Form Suppliers
| The Suppliers form in the Northwind sample application located in the Samples share on the Databases server on a LAN (UNC format path). The text "\\databases\samples\northwind.mdb" is displayed.
|
| Suppliers Form##Form Suppliers
| The Suppliers form in the current database. The words "Suppliers Form" are displayed in the field or control.
|
| #c:\windows\personal\1996 Sales.ppt#13
| Slide 13 in the 1996 Sales PowerPoint presentation located in the \Windows\Personal folder. The text "c:\windows\personal\1996 Sales.ppt" is displayed.
|
| #c:\windows\personal\budget.xls#Sheet1!A2
| The A2 cell in Sheet1 of the Budget.xls file located in the \Windows\Personal folder. The text "c:\windows\personal\budget.xls" is displayed.
|
You can enter data in a Hyperlink field in three ways: by using
the Insert Hyperlink dialog box (available through the
Hyperlink command on the Insert menu), by typing
an address directly into a Hyperlink field, or by using Data Access
Objects (DAO) methods in Visual Basic code. When you use the Insert
Hyperlink dialog box or type directly into a Hyperlink field,
Microsoft Access adds the two pound signs (#) that delimit the
parts of the hyperlink data. When you use DAO methods, your code
must include the two pound signs to delimit the parts of the hyperlink
data.
You can display the stored hyperlink format in a table by moving
the insertion point into a Hyperlink field using the keyboard,
and then pressing F2. You can edit the stored
hyperlink in this form, but be careful to enter pound signs in
the appropriate locations. You can add or edit the displaytext
part of a Hyperlink field by rightclicking a hyperlink in
a table, pointing to Hyperlink on the shortcut menu, and
then typing the display text in the Display Text box. You
can add or edit the address or subaddress
part of a Hyperlink field by rightclicking a hyperlink in
a table, pointing to Hyperlink on the shortcut menu, and
then selecting Edit Hyperlink.
The HyperlinkPart Function
The HyperlinkPart function returns information about data
stored in a Hyperlink field. The syntax for the HyperlinkPart
function is:
object.HyperlinkPart(hyperlink
As Variant, part As Integer)
The following table describes the arguments of the HyperlinkPart
function.
| Argument | Description
|
| object | Optional. The Application object.
|
| hyperlink | Required. A Variant that represents the data stored in a Hyperlink field.
|
| part | Optional. The value for the part argument is an intrinsic constant that represents the information you want returned by the HyperlinkPart function.
|
You can set the part argument to the following
constants.
| Constant | Value
| Description |
| acDisplayedValue
| 0 | (Default) The underlined text displayed in a hyperlink.
|
| acDisplayText
| 1 | The displaytext part of a Hyperlink field.
|
| acAddress
| 2 | The address part of a Hyperlink field.
|
| acSubAddress
| 3 | The subaddress part of a Hyperlink field.
|
Note If
you use the HyperlinkPart function in an SQL statement or a query,
the part argument is required and you can't set it to the constants
listed in the preceding table — you must use the
value instead.
You use the HyperlinkPart function to return one of three
values stored in a Hyperlink field (displaytext,
address, or subaddress)
or the displayed value. The value returned depends on the setting
of the part argument. If you don't use the
part argument, the HyperlinkPart function
returns the value Microsoft Access displays for the hyperlink
(which corresponds to the acDisplayedValue setting for
the part argument).
When a value is provided in the displaytext
part of a Hyperlink field, the value displayed by Microsoft Access
will be the same as the displaytext setting.
When there's no value in the displaytext part
of a Hyperlink field, Microsoft Access displays the value of the
address or subaddress part
of the Hyperlink field, depending on which value is first present
in the field.
The following table shows the values returned by the HyperlinkPart
function for data stored in a Hyperlink field.
| Hyperlink field data
| HyperlinkPart function returned values
|
| #http://www.microsoft.com/#
| acDisplayedValue: http://www.microsoft.com/
acDisplayText: No value returned.
acAddress: http://www.microsoft.com/
acSubAddress: No value returned.
|
| Microsoft#http://www.microsoft.com/#
| acDisplayedValue: Microsoft
acDisplayText: Microsoft
acAddress: http://www.microsoft.com/
acSubAddress: No value returned.
|
| Customers##Form Customers
| acDisplayedValue: Customers
acDisplayText: Customers
acAddress: No value returned.
acSubAddress: Form Customers
|
| ##Form Customers | acDisplayedValue: Form Customers
acDisplayText: No value returned.
acAddress: No value returned.
acSubAddress: Form Customers
|
The following example uses all four of the part
argument constants to display information returned by the HyperlinkPart
function for each record in a table containing a Hyperlink field.
To try this example, paste the DisplayHyperlinkParts procedure
into the Declarations section of a module. You can call the DisplayHyperlinkParts
procedure from the Debug window, passing to it the name of a table
that contains hyperlinks and the name of the field that contains
Hyperlink data, as shown in the following example.
DisplayHyperlinkParts "MyHyperlinkTableName", "MyHyperlinkFieldName"
Sub DisplayHyperlinkParts(strTable As String, strField As String)
Dim dbs As Database, rst As Recordset
Dim strMsg As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable)
While Not rst.EOF ' For each record in table.
strMsg = "DisplayValue = " & HyperlinkPart(rst(strField), acDisplayedValue) _
& vbCrLf & "DisplayText = " & HyperlinkPart(rst(strField), acDisplayText) _
& vbCrLf & "Address = " & HyperlinkPart(rst(strField), acAddress) _
& vbCrLf & "SubAddress = " & HyperlinkPart(rst(strField), acSubAddress)
' Show parts returned by HyperlinkPart function.
MsgBox strMsg
rst.MoveNext
Wend
End Sub
When you use the HyperlinkPart function in a query, the
part argument is required. For example, the
following SQL statement uses the HyperlinkPart function
to return information about data stored as a Hyperlink data type
in the URL field of the Links table:
SELECT Links.URL, HyperlinkPart([URL],0)
AS Display, HyperlinkPart([URL],1)
AS Name, HyperlinkPart([URL],2)
AS Addr, HyperlinkPart([URL],3) AS SubAddr
FROM Links;
For another example of using the HyperlinkPart function,
see "Displaying a Document in the WebBrowser Control by Using a Hyperlink Stored in a Table" later in this chapter.
Following a Hyperlink in a Text Box Bound to a Hyperlink Field
When you use the Follow method in Microsoft Access, you
don't need to know the address specified by a control's HyperlinkAddress
or HyperlinkSubAddress property, or by the Hyperlink field
that is bound to a text box, list box, or combo box control. You
only need to know the name of the control that contains the hyperlink.
This example uses the Follow method to automatically open
the Web page specified in a text box bound to a Hyperlink field
on a form whenever the user moves to a new record. Add the following
code to the OnCurrent event of a form.
Private Sub Form_Current()
Dim txt As TextBox
On Error GoTo Error_Form1
' Set reference to the txtAddress text box bound to a Hyperlink field.
Set txt = txtAddress
' Follow the hyperlink.
txt.Hyperlink.Follow
Exit_Form1:
Exit Sub
Error_Form1:
MsgBox Err & ": " & Err.Description
Resume Exit_Form1
End Sub
For another example of following a hyperlink stored in a table,
see "Displaying a Document in the WebBrowser Control by Using a Hyperlink Stored in a Table" later in this chapter.
Creating a Hyperlink Field with Visual Basic
You can use Data Access Objects (DAO) code to create a field with
the Hyperlink field type. To do so, you must first create a field
with the Memo data type and then set the Attributes property
of the field to dbHyperlinkField. The following example
creates a table named Hyperlinks that contains a Text field and
a Hyperlink field.
Sub CreateHyperlinkField()
Dim db As Database
Dim tbl As TableDef
Set db = CurrentDb()
Set tbl = db.CreateTableDef("Hyperlinks")
With tbl
.Fields.Append .CreateField("Text", dbText)
.Fields.Append .CreateField("Hyperlink", dbMemo)
.Fields("Hyperlink").Attributes = dbHyperlinkField
End With
db.TableDefs.Append tbl
RefreshDatabaseWindow
End Sub
