Export Hyperlink to Excel with MSQuery



I have a user with an Access Database with a hyperlink column. Using MS
Query, I need to export various columns, including the hyperlink to Excel.
However, when I do so the hyperlink column doesn't translate as a hyperlink
in Excel.
So, _www.home.com_ in Access becomes #www.home.com#http://www.home.com in
Has anyone else encountered and resolved this kind of problem?
Thanks in anticipation, Sara


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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question