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

See response below cited text.

"Frank H" wrote:
> 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-b....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 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 wrote:

> 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 Removed)

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

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

"James A. Fortune" wrote:

> See response below cited text.
>
> "Frank H" wrote:
> > 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-b....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 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

> 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.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken Snell MVP wrote:
> "James A. Fortune" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>
>>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.

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 Removed)

Frank H wrote:
> 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
>
> "James A. Fortune" wrote:
>
>
>>See response below cited text.
>>
>>"Frank H" wrote:
>>
>>>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-b....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 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
>>

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 Removed)

James A. Fortune wrote:

> 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....471bbef8237db3

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 Removed)

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

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




"James A. Fortune" wrote:

> Ken Snell MVP wrote:
> > "James A. Fortune" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >
> >
> >>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.
>
> 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 Removed)
>

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