Add Hyperlink in Excel from MS-Access

[Frank H]

Can anybody assist ?

I am updating an XL file from within an MS-Access (2003) form.
I am using code like the example below... it works 100%

I now need to add a hyperlink to one of the cells.
Can anybody advise how I select the appropriate cell and add a hyperlink
with both an address and text to display ?

TIA Frank

Code.....

Set objXL = New Excel.Application
With objXL

Set objWkb = .Workbooks.Open(XLTemplate)

'Sheet 1
Set objSht = .ActiveWorkbook.Worksheets("1. ABC")

With objSht
.Cells(3, 2).Value = "aaaaa"
.Cells(9, 2).Value = "bbb"
.Cells(4, 2).Value = "ccc"
etc
End With

'Sheet 2
Set objSht = .ActiveWorkbook.Worksheets("2. DEF")

With objSht
.Cells(6, 4).Value = "C"
etc
End With
objXL.ActiveWorkbook.SaveAs XLFile
End With

objXL.Quit
Set objXL = Nothing
Set objSht = Nothing
Set objWkb = Nothing

 

[James A. Fortune]

See response below cited text.

Can anybody assist ?

I am updating an XL file from within an MS-Access (2003) form.
I am using code like the example below... it works 100%

I now need to add a hyperlink to one of the cells.
Can anybody advise how I select the appropriate cell and add a hyperlink
with both an address and text to display ?

TIA Frank

Code.....

Set objXL = New Excel.Application
With objXL

Set objWkb = .Workbooks.Open(XLTemplate)

'Sheet 1
Set objSht = .ActiveWorkbook.Worksheets("1. ABC")

With objSht
.Cells(3, 2).Value = "aaaaa"
.Cells(9, 2).Value = "bbb"
.Cells(4, 2).Value = "ccc"
etc
End With

'Sheet 2
Set objSht = .ActiveWorkbook.Worksheets("2. DEF")

With objSht
.Cells(6, 4).Value = "C"
etc
End With
objXL.ActiveWorkbook.SaveAs XLFile
End With

objXL.Quit
Set objXL = Nothing
Set objSht = Nothing
Set objWkb = Nothing

Maybe (Air Code):

Dim strYesterday As String
Dim strLink As String

strYesterday = Format(DateAdd("d", -1, Date()), "mm\.dd\.yyyy")
strLink = "'http://www.merriam-webster.com/cgi-bin/mwwodarch.pl?" &
strYesterday
objXL.Range("A1").Select
objWkb.ActiveSheet.Hyperlinks.Add Selection, strLink
objWkb.ActiveCell.FormulaR1C1 = "Yesterday's Word of the Day: " & strLink

That should create a hyperlink that looks like (all underlined):

Yesterday's Word of the Day:
http://www.merriam-webster.com/cgi-bin/mwwodarch.pl?03.22.2009

yet should be anchored to just the URL part.

Maybe include the following code to size the columns so that the width
of the hyperlink column is wide enough (more Air Code):

objXL.Cells.Select
objXL.Selection.Columns.AutoFit
objXL.Range("A1").Select

Another option is to use one cell for the label and the one to the right
of it for the hyperlink. Also, you should move to the sheet you want
before selecting your hyperlink cell if the current selection is not on
that sheet. E.g., instead of using objXL.Range("A1").Select in the
first code segment, use something like:

objSht.Range("A1").Select

James A. Fortune
(e-mail address removed)

Yes. Yes. A flaw more and more common among Jedi. Too sure of
themselves they are. Even the older, more experienced ones. -- Yoda,
Attack of the Clones

 

[James A. Fortune]

strLink = "'http://www.merriam-webster.com/cgi-bin/mwwodarch.pl?" &

I'm not sure where the single quote came from .

James A. Fortune
(e-mail address removed)

CHYACK - Impudence or cheek. Also verb. -- Dictionary of Australian
Slang, Second Edition, Sidney J. Baker, 1943 (Price: Three shillings &
sixpence)

 

[Frank H]

James

Thanks for the response

I'm afraid I dont understand the Excel object at all, pardon my ignorance!
The place where i wish to place the hyperlink is after the last cell in the
first worksheet, ie at (cell 10,2) in my code. I have tried all variations
of your suggestion and get a variety of error msgs. Can you tell me exactly
what code I should add after the line ".cells(4,2).value='ccc'" to add a
hyperlink to cell (cell,2) ?
Thanks again

Frank H

 

[Ken Snell MVP]

Maybe (Air Code):

Dim strYesterday As String
Dim strLink As String

strYesterday = Format(DateAdd("d", -1, Date()), "mm\.dd\.yyyy")
strLink = "'http://www.merriam-webster.com/cgi-bin/mwwodarch.pl?" &
strYesterday
objXL.Range("A1").Select
objWkb.ActiveSheet.Hyperlinks.Add Selection, strLink
objWkb.ActiveCell.FormulaR1C1 = "Yesterday's Word of the Day: " & strLink

The use of .ActiveSheet and .ActiveCell will create new instances of EXCEL
in memory, and they'll continue running after the original EXCEL instance is
shut down. Always use fully qualified references when automating EXCEL from
ACCESS. Your code should create a worksheet object to represent the
worksheet that is the 'active one' and then use that object in the
reference. And you should use the worksheet object to reference a Range
object, which then would replace the ActiveCell reference.

 

[James A. Fortune]

The use of .ActiveSheet and .ActiveCell will create new instances of EXCEL
in memory, and they'll continue running after the original EXCEL instance is
shut down. Always use fully qualified references when automating EXCEL from
ACCESS. Your code should create a worksheet object to represent the
worksheet that is the 'active one' and then use that object in the
reference. And you should use the worksheet object to reference a Range
object, which then would replace the ActiveCell reference.

You might be right, but given that the creation of objWkb included a
fully qualified reference to objXL, any objects referenced using objWkb
will also be fully qualified and thus not create new instances of EXCEL
in memory. Still, your suggestion is safer and it's better to be safe
than scarry .

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

James

Thanks for the response

I'm afraid I dont understand the Excel object at all, pardon my ignorance!
The place where i wish to place the hyperlink is after the last cell in the
first worksheet, ie at (cell 10,2) in my code. I have tried all variations
of your suggestion and get a variety of error msgs. Can you tell me exactly
what code I should add after the line ".cells(4,2).value='ccc'" to add a
hyperlink to cell (cell,2) ?
Thanks again

Frank H

:

If you use the code I posted, replace A1 in objXL.Range("A1").Select
(the one before the line to add the hyperlink) with the name of the cell
in which you want the hyperlink to be created. I'll have to see if the
Range object can somehow handle cell coordinates. If not, perhaps I can
come up with a function that will convert cell coordinates into cell names.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

I'll have to see if the
Range object can somehow handle cell coordinates. If not, perhaps I can
come up with a function that will convert cell coordinates into cell names.

I think I've already done that once. The following should help if the
spreadsheet has less than 703 columns :

http://groups.google.com/group/comp.databases.ms-access/msg/e3471bbef8237db3

Sample usage:

MsgBox (DecimalToExcelCol(28) & CStr(3)) => AB3

I believe that the .Cells indices are also one-based so the above should
correspond to the name of objSht.Cells(28, 3). In retrospect, I should
have named the function something more descriptive like
ConvertLongColumnNumberToExcelColumnLetters .

James A. Fortune
(e-mail address removed)

DIZZY LIMIT, THE - The final touch, The last straw. -- Dictionary of
Australian Slang, Second Edition, Sidney J. Baker, 1943 (Price: Three
shillings & sixpence)

 

[Frank H]

Thanks guys for you help, am fumbling along & have got a bit further.
The following is part of my code as it is now

====================================================

Set objSht = .ActiveWorkbook.Worksheets("2. Components")

With objSht

rst.MoveFirst
Do Until rst.EOF
.Cells(rst!Row, 3).Value = rst!MeetStandard
.Cells(rst!Row, 4).Value = rst!Material

(a bunch more of these type of lines then... )

Range("V15").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="PK06_001.JPG", TextToDisplay:="PK06_001.JPG (5)"

rst.MoveNext
Loop


End With

====================================================

What I now get is this...

First time I run it, it creates the hyperlink in the correct cell,
but in the wrong sheet (creates it in last sheet, not sheet 2).
Can you tell me why, I thought the active sheet was set above ?

It leaves an instance of Excel in memory, although it only opens
one instance irrespective of the number of times I run the code.
How do I ensure it closes ?

(The end of the sub looks like this...

objXL.Quit

ExitHere:
Set objXL = Nothing
Set objSht = Nothing
Set objWkb = Nothing
Set rst = Nothing
Set db = Nothing
Exit Sub

=====================================================

I thank you again for your help

Frank H

 

[Frank H]

Further to my previous message, I am testing it again, but it now runs once,
then errors out on subsequent runs with the error
Error 1004: Method 'Range' of object '_Global' failed

It then leaves an instance of Excel open each time.
If I close then manually & restart the app it repeats the sequence.

I don't know why it didnt do this previously, but I was able to run abt 10
sheets.

Yours frustratingly, Frank H

 

[James A. Fortune]

Thanks guys for you help, am fumbling along & have got a bit further.
The following is part of my code as it is now

====================================================

Set objSht = .ActiveWorkbook.Worksheets("2. Components")

With objSht

rst.MoveFirst
Do Until rst.EOF
.Cells(rst!Row, 3).Value = rst!MeetStandard
.Cells(rst!Row, 4).Value = rst!Material

(a bunch more of these type of lines then... )

Range("V15").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="PK06_001.JPG", TextToDisplay:="PK06_001.JPG (5)"

Ken said:

Always use fully qualified references when automating EXCEL from ACCESS.

I said:

.... given that the creation of objWkb included a fully qualified
reference to objXL, any objects referenced using objWkb will also be
fully qualified and thus not create new instances of EXCEL in memory.

Apparently you didn't listen to the advice of either of us.

The answer to both problems appears to be your bare Range and
ActiveSheet objects. You need to include objXL or objWkb as I've shown.
Without the fully qualified reference, it caused your unwanted
instance of Excel. Plus, only objSht knew about the worksheet you
wanted, so without looking at objSht it had to use the worksheet it was
already on to resolve cell references. For the Range, you can just put
a period before it so that it uses objSht. Use objWkb to qualify the
line of code that adds the hyperlink.

James A. Fortune
(e-mail address removed)

BUSHED - Confused, muddled, lost. -- Dictionary of Australian Slang,
Second Edition, Sidney J. Baker, 1943 (Price: Three shillings & sixpence)

 

[Frank H]

Ken said:

Always use fully qualified references when automating EXCEL from ACCESS.

I said:

.... given that the creation of objWkb included a fully qualified
reference to objXL, any objects referenced using objWkb will also be
fully qualified and thus not create new instances of EXCEL in memory.

Apparently you didn't listen to the advice of either of us.

The answer to both problems appears to be your bare Range and
ActiveSheet objects. You need to include objXL or objWkb as I've shown.
Without the fully qualified reference, it caused your unwanted
instance of Excel. Plus, only objSht knew about the worksheet you
wanted, so without looking at objSht it had to use the worksheet it was
already on to resolve cell references. For the Range, you can just put
a period before it so that it uses objSht. Use objWkb to qualify the
line of code that adds the hyperlink.

James A. Fortune
(e-mail address removed)

BUSHED - Confused, muddled, lost. -- Dictionary of Australian Slang,
Second Edition, Sidney J. Baker, 1943 (Price: Three shillings & sixpence)

Hello again James

Thanks for your follow up.
I can assure you that I read your & Kens advice carefully, my problem is as
I indicated in one of my earlier posts that I'm completely new to these
concepts and am trying to come to understand them. Your latest explanation
makes more sense to me & I'll go away quietly & figure it out.
We all have to start somewhere. Once again thanks for your interest.

Frank H

 

[James A. Fortune]

Hello again James

Thanks for your follow up.
I can assure you that I read your & Kens advice carefully, my problem is as
I indicated in one of my earlier posts that I'm completely new to these
concepts and am trying to come to understand them. Your latest explanation
makes more sense to me & I'll go away quietly & figure it out.
We all have to start somewhere. Once again thanks for your interest.

Frank H

It's analogous to file paths. Fully qualified implies having the
equivalent of a path all the way back to the drive letter rather than
using a relative path. Excel Object references that are equivalent to
going all the way back through the Object Model to the top Excel object
allow Access to close all the objects when it closes the top Excel
Object. An unclosed Excel Object in Access might keep its Excel
instance open because Access "thinks" the Object may need it. BTW, how
did you discover TextToDisplay?

James A. Fortune
(e-mail address removed)

MURRUMBIDGEE JAM - Brown sugar moistened with cold tea and spread on damper.

DAMPER - Bread baked in ashes in the bush or outback.

-- Dictionary of Australian Slang, Second Edition, Sidney J. Baker,
1943 (Price: Three shillings & sixpence)

 

[Frank H]

It's analogous to file paths. Fully qualified implies having the
equivalent of a path all the way back to the drive letter rather than
using a relative path. Excel Object references that are equivalent to
going all the way back through the Object Model to the top Excel object
allow Access to close all the objects when it closes the top Excel
Object. An unclosed Excel Object in Access might keep its Excel
instance open because Access "thinks" the Object may need it. BTW, how
did you discover TextToDisplay?

James A. Fortune
(e-mail address removed)

MURRUMBIDGEE JAM - Brown sugar moistened with cold tea and spread on damper.

DAMPER - Bread baked in ashes in the bush or outback.

-- Dictionary of Australian Slang, Second Edition, Sidney J. Baker,
1943 (Price: Three shillings & sixpence)

Thanks for that James, It now works and I understand your explanation.
I discovered TextToDisplay by chance when manually editing a hyperlink in
Excel when trying to work this problem out.
The Edit Hyperlink form has a field 'Text to Display' as well as the
Address. I put something in it. I then checked the macro that I recorded and
there it was.

Thanks again for your help and patience.

Not an Aussie by any chance ?


DOPEY BUGGER - Me sometimes

 

[James A. Fortune]

Thanks for that James, It now works and I understand your explanation.
I discovered TextToDisplay by chance when manually editing a hyperlink in
Excel when trying to work this problem out.
The Edit Hyperlink form has a field 'Text to Display' as well as the
Address. I put something in it. I then checked the macro that I recorded and
there it was.
Thanks.


Thanks again for your help and patience.

Not an Aussie by any chance ?

I'm not Australian and I've never been there, but I was streaming a
Sydney radio station several years ago. It was amazing how a few months
later several of the songs finally became popular in the U.S. One song
didn't make it though. It was a parody:

Bloke lyrics:
http://www.lyrics007.com/Meredith Brooks Lyrics/Bloke Lyrics.html

DOPEY BUGGER - Me sometimes

Neither of those words are in that dictionary. Perhaps I can come up
with a suitable expression that conveys the same imagery.

James A. Fortune
(e-mail address removed)

 

[Frank H]

I'm not Australian and I've never been there, but I was streaming a
Sydney radio station several years ago. It was amazing how a few months
later several of the songs finally became popular in the U.S. One song
didn't make it though. It was a parody:

Bloke lyrics:
http://www.lyrics007.com/Meredith Brooks Lyrics/Bloke Lyrics.html


Neither of those words are in that dictionary. Perhaps I can come up
with a suitable expression that conveys the same imagery.

James A. Fortune
(e-mail address removed)

LOL - good lyrics, believe it or not, there are still a few of those blokes
around !