Exporting clickable Hyperlinks…

Generally, I store the path to my file and the name of the File in two separate fields. I do this because if there is a likelihood that the database will be upsized to SQL Server the Hyperlink data type in Access is not supported and I’d have to do it anyway.  Not a big deal as in Access as I can use…

Application.FollowHyperlink  or…

Allen Browne’s GoHyperlink()

However, it is a problem if I then want to export then to Adobe, Word or Excel and keep the links clickable.  To do that requires a little trickery (been wanting to use that word!).

For Adobe (.PDF) and Word (.DOC or .DOCX)…

• In your Recordsource (should be a Query) for your Report create a field:

theDocumentLink: IIf([FieldThatHoldsThePath]<>"",[FieldThatHoldsNameOfDocument] & "#" & [FieldThatHoldsThePath] & "#","")

• On the Report add theDocumentLink Control
• In the Property Sheet for the Control in the Format tab…

Is Hyperlink to Yes
Display As Hyperlink to If Hyperlink

Now while in Preview of the Report you can select PDF or XPS or Word from the Data tab and your links will be clickable and display whatever text you put in [FieldThatHoldsNameOfDocument].

For Excel (.XLS or .XLSX)…

I tend to use Excel Automation because I can control a lot more. I also use Templates to I don’t have to worry about Formatting.  In this Template I have two hidden fields that hold the Path (column B) and the Name to Display (column C), column F will display the Hyperlink.

The first thing to is tell it how many rows are in your Recordset. Then I need to add which Row I am starting on.  In my case I am starting at Row 5 so I need to add 4 to my Recordset.

• Add to your declarations

Dim lngLastRow As Long

• Then under the CopyFromRecordset line add, this is so we can get the row count to apply our formula to

‘Add the number of rows from the top the Recordset will be copied from
lngLastRow = rst.RecordCount + 4

• Now, for the Formula which you want to be sure to add after your data has pasted, so don’t add before the CopyFromRecordset…

'Column F to display the Hyperlink, column B the Path and column C the Text I want to Display
With xlWSh
For i = 5 To lngLastRow
xlWSh.Cells(.Rows.Count, 6).End(xlUp).Offset(1, 0).Formula = "=HYPERLINK(B" & CStr(i) &", C" & CStr(i) & ")"
Next I
End With

When you open the Excel spreadsheet your links will be clickable.

 2,817 total views,  1 views today