Question Access and Excel

[targheauto]

Hi everyone! I hope this is the right place where to post this. My problem is this: I have a piece of VB code in my Access application that creates an Excel sheet from the results of a query on the database.
One of the fields in the query contains an URL.
What I'd want to accomplish would be to have a hyperlink inside a cell, pointing to a product image in a website.
The query extracts a field like this:

"=COLLEG.IPERTESTUALE("http://esempio.microsoft.com/relazione/relazione bilancio.xls"; "Fare clic per visualizzare la relazione.")"

(COLLEG.IPERTESTUALE is the Italian localization of the Excel function).

What happens is I get the cells in the column populated with all the information coming from the query (i.e., the COLLEG.IPERTESTUALE command above), but I have to manually enter in each cell and double click it in order to activate the link.

How can I have hyperlinks automatically active?

Thanks so much
Alex

 

[WayPay]

Have a look at DoCmd.FollowHyperlink; that might do what you want.

 

[targheauto]

Mmmm ... interesting! But where would you use it? In the VB code that generates the xls sheet? Here's a snippet of that code:

'access recordset for data to be copied
Dim rs1 As DAO.Recordset

'create excel application object
Set xlapp = New Excel.Application

'open the workbook
Set wb = xlapp.Workbooks.Open(fileName2)

'create the new worksheet
Set ws1 = wb.Worksheets("WS1")

'create the recordsets from queries
Set rs1 = CurrentDb.OpenRecordset("query1")

'copy the data from the recordsets into the worksheets from cell A1
ws1.Range("A2").CopyFromRecordset rs1

So you see, here I just put into Excel what results from the query.
Where would you use the DoCmd.FollowHyperlink in this case?

Thanks a lot in advance for your precious help,
Alex

 

[targheauto]

Have a look at DoCmd.FollowHyperlink; that might do what you want.

Hey thanks for the suggestion. But, where would you use this method?
Here's a snippet of my code that generates the excel sheet:

'access recordset for data to be copied
Dim rs1, rs2 As DAO.Recordset

'create excel application object
Set xlapp = New Excel.Application

'open the workbook
Set wb = xlapp.Workbooks.Open(fileName2)

'create the new worksheets
Set ws1 = wb.Worksheets("sheet1")

'create the recordsets from queries
Set rs1 = CurrentDb.OpenRecordset("query1")

'copy the data from the recordsets into the worksheets from cell A1
ws1.Range("A2").CopyFromRecordset rs1

So you see, I just paste query data in excel. Where would you apply
the FollowHyperlink method in this case?
Thanks so much in advance for your precious help!
Alex

 

[WayPay]

I think I was answering the wrong question . Are you trying to make links in an Excel-file activate on a single- rather than a double-click? Or is you problem getting the data into the Excel-file?

 

[targheauto]

Oh no problem
Well, I put the data into excel from an access query that is run from inside
VB code in a form.
The data inside excel sheet goes right.
Problem is that when you open the sheet, you have to double click INTO
each cell containing the URL and hit ENTER in order to "activate" the hyperlink. Otherwise it's just like text, nothing more.
Hope I explained it right,
thanks a lot
Alex

I think I was answering the wrong question . Are you trying to make links in an Excel-file activate on a single- rather than a double-click? Or is you problem getting the data into the Excel-file?

 

[WayPay]

Hmm.. if I type www.google.com into a cell and click on it, it opens that web page (even without http://). It might be an Excel setting; Access doesn't seem to be the problem, anyway.

 

[chergh]

Never actually had the need to put hyperlinks in an excel spreadsheet before so not entirely sure this will work, and don't have excel here to try it. Also got no idea on the localisation either but try this:

ws1.hyperlinks.add anchor:= ws1.range("A1") _
                   address:= "http://esempio.microsoft.com/relazione/relazione bilancio.xls" _
                   texttodisplay:= "http://esempio.microsoft.com/relazione/relazione bilancio.xls"

Just gave it a try and it seems to work fine.

 

[targheauto]

Thanks thanks to both to take care of my problem!
I think I didn't explain it thouroughly. Actually, I don't manipulate excel
cell data from VB code. From VB code in Access I just throw inside
an excel sheet what I extract from an SQL query on my DB.
That is, I do not work on excel cells one by one from inside VB code.
If I manually type an URL inside an excel sheet, it will open properly in a browser window, no problems at all.
But, one of the fields returned by my query is an URL and when my VB code fills the sheet with query data, that column (= query field) is kinda
inactive, i.e., I have to double click inside the cell and hit the enter key
in order for the link to work properly.
I also tried to make my query return a field containing an excel function call, the function that creates a hyperlink and which is called "COLLEG.IPERTESTUALE" in the italian localisation.
Same result. It is as if the function is not "calculated" or activated automatically from excel. I also tried to "calculate" the sheet hitting F9 but
nothing changes.
Any idea?
Thanks so much in advance,
Alex

Never actually had the need to put hyperlinks in an excel spreadsheet before so not entirely sure this will work, and don't have excel here to try it. Also got no idea on the localisation either but try this:

ws1.hyperlinks.add anchor:= ws1.range("A1") _
                   address:= "http://esempio.microsoft.com/relazione/relazione bilancio.xls" _
                   texttodisplay:= "http://esempio.microsoft.com/relazione/relazione bilancio.xls"

Just gave it a try and it seems to work fine.

 

[chergh]

All you need to do is loop through the column that contains your hyperlinks.

So if your hyperlinks were in the 3rd column of your query they would be copied to column C so the code would look something like:

for i = 2 to ws1.range("C65536").end(xlup).row
   ws1.hyperlinks.add anchor:= ws1.range("C" & i) _
                      address:= ws1.range("C" & i).value _
                      texttodisplay:= ws1.range("C" & i).value
next i

Replace C with whatever column your hyperlinks are in and it should hpefully work.

 

[targheauto]

Sounds great thanks, but it gives me a syntax error in VB ...
what's wrong btw? (I copy-pasted your code as is)

All you need to do is loop through the column that contains your hyperlinks.

So if your hyperlinks were in the 3rd column of your query they would be copied to column C so the code would look something like:

for i = 2 to ws1.range("C65536").end(xlup).row
   ws1.hyperlinks.add anchor:= ws1.range("C" & i) _
                      address:= ws1.range("C" & i).value _
                      texttodisplay:= ws1.range("C" & i).value
next i

Replace C with whatever column your hyperlinks are in and it should hpefully work.

 

[chergh]

Your getting the syntax error as I didn't test the code, it's just a few commas missing.

You also need to change the column unless your hyperlink actually are in column C.

for i = 2 to ws1.range("C65536").end(xlup).row
   ws1.hyperlinks.add anchor:= ws1.range("C" & i), _
                      address:= ws1.range("C" & i).value, _
                      texttodisplay:= ws1.range("C" & i).value
next i

 

[targheauto]

HEY IT WORKS !!!
GREAT !!!!

And I didn't have to change the column name either, it was actually C

THANKS!!!!
Alex

Your getting the syntax error as I didn't test the code, it's just a few commas missing.

You also need to change the column unless your hyperlink actually are in column C.

for i = 2 to ws1.range("C65536").end(xlup).row
   ws1.hyperlinks.add anchor:= ws1.range("C" & i), _
                      address:= ws1.range("C" & i).value, _
                      texttodisplay:= ws1.range("C" & i).value
next i