Hyperlinks Export to Excel Errors

[LindaD]

In Access 2003 I want to export query results to Excel.
The data includes hyperlinks that are on company shared
network drives. The hyperlinks don't work once exported
to Excel...only for me but not for anyone else I email the
Excel file to. I have tried copy/paste the result of the
query, tried exporting...everyone gets "file not found".
The only way it works is if I "send" the query in email
which creates an Excel file in Outlook open message. That
file the hyperlinks work. Or if I copy and paste the
query data into an open Outlook message. What's the
problem? When exported the Excel file shows the hyperlink
as ///\\http:\xxxxx. The field is an Access hyperlink
field and all users have read only rights to the directory
the link goes to. Help is appreciated.
-Linda

 

[John Nurick]

Hi Linda,

Usually this question is the other way round: how to import Excel
hyperlinks to Access. From Access to Excel, I know of two ways to go.

1) Export as usual. Insert a new column in the Excel sheet, and in it
use the HYPERLINK() worksheet function to create a working hyperlink
from the contents of the imported column. You will probably need to use
Excel's text-handling worksheet functions (e.g. SEARCH() and MID()) to
parse out the relevant part(s) of the imported data.

2) Export the data and then write VBA code that works down each cell in
the "hyperlink" column, parsing the contents and passing the relevant
part(s) along with the cell reference to Excel's
Worksheet.Hyperlinks.Add() function.