Import Hyperlink from MSAccess via MSQuery

[Guest]

I have a user with an Access query that has a hyperlink column. The user
requires that this query be available to other users via a spreadsheet.
Using MS Query, I need to export various columns, including the hyperlink to
Excel. All good, apart from the hyperlink column, which doesn't translate as
a hyperlink in Excel.
So, _http://www.home.com_ in Access becomes www.home.com#http://www.home.com
in Excel.
And _www.home.com_ in Access becomes "www.home.com" (i.e. plain text) in
Excel.
I have tried using the SQL in MSQuery to build Excels HYPERLINK function, so:
'=HYPERLINK('&'table.hyperlinkcolumn&','table.hyperlinkcolumn'&')'
which just inserts text of "=HYPERLINK(www.home.com,www.home.com).
Has anyone else encountered and resolved this kind of problem?
I am using Access & Excel 2000
Thanks in anticipation, Sara

 

[Guest]

Hi all,

I resolved the issue myself, once I found this article at MS Support:
http://support.microsoft.com/kb/271856/en-us , received inspiration from
http://j-walk.com/ss/excel/odd/odd29.htm and assistance from other posts in
the Excel Programming forum.

I found VBA's SpecialCells method are created the following Macro:

Private Sub Workbook_Open()
' Firstly, refresh the data from MSQuery
Range("B2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
' Select the cells to convert to Hyperlink
Range("C2:C65536").SpecialCells(xlCellTypeConstants, xlTextValues).Select
' Convert to Hyperlink
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
' Go Home
Range("A1").Select
End Sub

Being a newbie to VBA, I don't really know how efficient or elegant this
script is, but it does the job well and quickly and works in 2000 and 2002
(2003 untested).

Basically it refreshes my MSQuery from my MSAccess.mdb, selects any cell
filled with text in column C and then converts the text to hyperlinks. Once
it's done that the cursor goes back to Cell A1 and is ready for the user.
From reading, there'll only be a problem with this method if the number of
selections is greater than 8192 (http://support.microsoft.com/kb/832293/en-us)

A few prerequisites - the MSAccess.mdb columns containing the hyperlinks
must be text, all web addresses must be prefixed with 'http://' All
referenced documents must have the full path and file name, but don't need
the 'file://' prefix. Users must have at least read access to the
MSAccess.mdb and all referenced documents.
Sara