Importing Hyperlink from Access to Ecxel

[duncansancho]

This is not strictly necessarily a VBA problem, but how can I import data by query to Ecxel and keep the hyperlinks?
Duncan

 

[ETID]

Create a live link to the table or query from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there to select your mdb, table or query...You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option, look for a check box).


then return your data to excel.


when data is returned,...you can right click in the data area and select properties to refresh on open...auto fill formulas etc.

In the returned data's sheet, add a new column with this formula for each hyperlink field...(in this case I assume col. A has hyperlink text in it)

=HYPERLINK(RIGHT(A2,LEN(A2)-SEARCH("#",A2,1)),LEFT(A2,SEARCH("#",A2,1)-1))

fill down...(this is where the autofill formulas option comes in handy)

this is now a live link, any time the workbook is opened. your current / up to date access data comes in (if you set the refresh on open option).

 

[tav1035]

duncansancho,
Import data into column 'A' and insert blank column for 'B' and insert the following =HYPERLINK(A1) into B1 and drag down.
tav1035