Export Hyperlink to Excel with MSQuery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hiya,
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
Excel.
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
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
 
Back
Top