How to Open a Hyperlink

[Shatin]

Can someone please help me with the code below. The purpose of the macro is
to first ask for a brand number. The brand number is then concatenated to
form a valid hyperlink, which points to a pdf file. The macro then opens the
hyperlink. However, I can't get it to work. Any help would be much
appreciated.

-----
Option Explicit

Sub BrandCheck()

Dim hLink As Hyperlink
Dim begLink As String
Dim brandNum As String
Dim endLink As String

brandNum = InputBox("Enter the brand number:")
If brandNum = "" Then
Exit Sub
End If

begLink = "http://www.acb.com/"
endLink = ".pdf"

hLink.Address = begLink & brandNum & endLink
hLink.Follow NewWindow:=False, AddHistory:=True

End Sub

 

[David McRitchie]

You're not putting the hyperlink into your worksheet so why
not just use:

Shell "iexplore " & begLink & brandNum & ".pdf"

If on the otherhand you wanted to stick with Worksheet Functions

A1: 'Brand
B1: '.pdf
A2: branda
B2: =HYPERLINK("http://www.acb.com/" & A2 & B$1, "[x]")

sidenote: can anyone tell me why I can't just type the above line
into Outlook Express but have to type it into notepad (or similar)
then copy and paste into Outlook Express so I don't lose the
double quotes around the http://www.acb.com/
or any other link enclosed in double quotes.
I am using plain text, uuencode, wrap at 132, indent with "> "

 

[Shatin]

You're not putting the hyperlink into your worksheet so why

not just use:

Shell "iexplore " & begLink & brandNum & ".pdf"

David, I tried the above but got the following error:

Runtime error '53':
File not found

Excel made no attempt to access the Web before giving this error msg.

 

[Dave Peterson]

Another way:

Option Explicit
Sub BrandCheck()

Dim hLinkAddr As String
Dim begLink As String
Dim brandNum As String
Dim endLink As String

brandNum = InputBox("Enter the brand number:")
If brandNum = "" Then
Exit Sub
End If

begLink = "http://www.acb.com/"
endLink = ".pdf"

hLinkAddr = begLink & brandNum & endLink
On Error Resume Next
ActiveWorkbook.FollowHyperlink Address:=hLinkAddr, _
NewWindow:=False, AddHistory:=True
If Err.Number <> 0 Then
MsgBox "Something bad happened"
Err.Clear
End If
On Error GoTo 0

End Sub

 

[Dave Peterson]

Sidenote response:

I once corrected someone who left out the double quotes in an example like
yours. I was corrected that it might not be the user--but OE which stripped the
double quotes out.

(My uninformed guess is that it's just the way MSOE works.)

but if you double up on your typing:

=hyperlink(""http://www.abc.com/"" & a2 & b$1, "[x]")

I think it'll come through ok. (It gets converted correctly at least.)

You're not putting the hyperlink into your worksheet so why
not just use:

Shell "iexplore " & begLink & brandNum & ".pdf"

If on the otherhand you wanted to stick with Worksheet Functions

A1: 'Brand
B1: '.pdf
A2: branda
B2: =HYPERLINK("http://www.acb.com/" & A2 & B$1, "[x]")

sidenote: can anyone tell me why I can't just type the above line
into Outlook Express but have to type it into notepad (or similar)
then copy and paste into Outlook Express so I don't lose the
double quotes around the http://www.acb.com/
or any other link enclosed in double quotes.
I am using plain text, uuencode, wrap at 132, indent with "> "

Can someone please help me with the code below. The purpose of the macro is
to first ask for a brand number. The brand number is then concatenated to
form a valid hyperlink, which points to a pdf file. The macro then opens the
hyperlink. However, I can't get it to work. Any help would be much
appreciated.

-----
Option Explicit

Sub BrandCheck()

Dim hLink As Hyperlink
Dim begLink As String
Dim brandNum As String
Dim endLink As String

brandNum = InputBox("Enter the brand number:")
If brandNum = "" Then
Exit Sub
End If

begLink = "http://www.acb.com/"
endLink = ".pdf"

hLink.Address = begLink & brandNum & endLink
hLink.Follow NewWindow:=False, AddHistory:=True

End Sub

 

[Shatin]

Dave,

Your macro works perfectly! Many thanks for your help.

-Shatin