Export Hyperlinks to Excel

[Guest]

I am exporting records from Access to Excel. One of the fields is a
hyperlink and I am having trouble getting a working link by the time it
reaches Excel. What techniques are needed to make this work?

One thing that I noticed is that the hyperlink in Excel has leading and
trailing pound symbols (#) that weren't there in Access. If I remove the #
then the hyperlink works. Is a possible solution to prevent # from being
added? If so, how do I do that?

Thanks,

David

 

[Guest]

Hi David,

I think the simplest approach is to use the HyperlinkPart() function in your
query, to extract the URL from the hyperlink field.

Alternatively you can export the field value complete with the #s (whose
meaning is explained in the Help for HyperlinkPart) and then use a formula in
Excel to extract the URL. The Excel HYPERLINK() function turns a URL into a
hyperlink.

 

[Guest]

Hi John,

Thank you for your help. I didn't know about the HperlinkPart() function so
I am learning something new today.

I am exporting from a query. Based on your suggestion I changed the
hyperlink to incorporate the new function as: HyperlinkPart([Hyper],0) where
"Hyper" is the field name for the hyperlink in the Access table. After
export to Excel, the hyperlinks in the spreadsheet look like hyperlinks and
no longer have the leading and trailing #. However, they still don't do
anything when I click them. Am I still doing something wrong? Is there a
way to export a hyperlink from Access to Excel that retains the full
hyperlink functionality in Excel?

Thanks again,

David

 

[John Nurick]

Hi David,

Two things. First,
HyperlinkPart([Hyper], 0)
returns the DisplayedValue of the link in Access, which is not
necessarily the same as the address. Try
HyperlinkPart([Hyper], 5)
instead: this returns the full address (including a subaddress such as a
bookmark if there is one).

Next, it's so long since I've done this that I forgot that all that gets
you is the URLs as text in Excel cells. It's also necessary to persuade
Excel that they're hyperlinks.

One way to do this is to have the URLs in one column, and use the
HYPERLINK() worksheet function in another column. E.g. if there's a URL
in cell B3, putting =HYPERLINK(B3) in another cell gives you a clickable
hyperlink.

Or you can use a bit of VBA code to turn the URLs into real Excel
hyperlinks. This shows the basic idea:


Sub ConvertURLToHyperLinkDemo()
Dim C As Excel.Range

With Application
For Each C In .Selection.Cells
ActiveSheet.Hyperlinks.Add C, C.Text
Next C
End With

End Sub

Hi John,

Thank you for your help. I didn't know about the HperlinkPart() function so
I am learning something new today.

I am exporting from a query. Based on your suggestion I changed the
hyperlink to incorporate the new function as: HyperlinkPart([Hyper],0) where
"Hyper" is the field name for the hyperlink in the Access table. After
export to Excel, the hyperlinks in the spreadsheet look like hyperlinks and
no longer have the leading and trailing #. However, they still don't do
anything when I click them. Am I still doing something wrong? Is there a
way to export a hyperlink from Access to Excel that retains the full
hyperlink functionality in Excel?

Thanks again,

David

Hi David,

I think the simplest approach is to use the HyperlinkPart() function in your
query, to extract the URL from the hyperlink field.

Alternatively you can export the field value complete with the #s (whose
meaning is explained in the Help for HyperlinkPart) and then use a formula in
Excel to extract the URL. The Excel HYPERLINK() function turns a URL into a
hyperlink.

 

[Guest]

Thanks John,

I used a combination of your suggestions to come up with something that
works - but it isn't elegant and I suspect there is room for improvement.

I created a "Template" spreadsheet to write to. In the template I put a
column of hyperlinks as "holders" in the column where the hyperlink fields
will be written. Next to that column I put a column with your Excel formula
of =HYPERLINK(B3).

In Access I changed from HyperlinkPart([Hyper],0) to HyperlinkPart([Hyper],5).

When I export from Access the new hyperlink goes into the Excel column with
the Hyperlink holders. The resulting value displays the new and correct
hyperlink value. It is a hyperlink and when clicked, it opens a document.
Unfortunately the document it opens is not the correct one. Instead it opens
the document from the Holder hyperlink. So the export process changed "Text
to Display" but not the "Address" portion of the hyperlink.

However, the column with the Excel formula =HYPERLINK(B3) does open the
correct document. So I am hiding the column with the actual hyperlinks and
only displaying the column with the formula. So in the end it looks right
and acts right.

I also tried to work with the code you included but couldn't figure out how
to make that work.

In summary, I do have something that works and I guess that is the important
thing. But I would certainly welcome ideas on cleaning up the process.

Thanks again,

David

Hi David,

Two things. First,
HyperlinkPart([Hyper], 0)
returns the DisplayedValue of the link in Access, which is not
necessarily the same as the address. Try
HyperlinkPart([Hyper], 5)
instead: this returns the full address (including a subaddress such as a
bookmark if there is one).

Next, it's so long since I've done this that I forgot that all that gets
you is the URLs as text in Excel cells. It's also necessary to persuade
Excel that they're hyperlinks.

One way to do this is to have the URLs in one column, and use the
HYPERLINK() worksheet function in another column. E.g. if there's a URL
in cell B3, putting =HYPERLINK(B3) in another cell gives you a clickable
hyperlink.

Or you can use a bit of VBA code to turn the URLs into real Excel
hyperlinks. This shows the basic idea:


Sub ConvertURLToHyperLinkDemo()
Dim C As Excel.Range

With Application
For Each C In .Selection.Cells
ActiveSheet.Hyperlinks.Add C, C.Text
Next C
End With

End Sub

Hi John,

Thank you for your help. I didn't know about the HperlinkPart() function so
I am learning something new today.

I am exporting from a query. Based on your suggestion I changed the
hyperlink to incorporate the new function as: HyperlinkPart([Hyper],0) where
"Hyper" is the field name for the hyperlink in the Access table. After
export to Excel, the hyperlinks in the spreadsheet look like hyperlinks and
no longer have the leading and trailing #. However, they still don't do
anything when I click them. Am I still doing something wrong? Is there a
way to export a hyperlink from Access to Excel that retains the full
hyperlink functionality in Excel?

Thanks again,

David

Hi David,

I think the simplest approach is to use the HyperlinkPart() function in your
query, to extract the URL from the hyperlink field.

Alternatively you can export the field value complete with the #s (whose
meaning is explained in the Help for HyperlinkPart) and then use a formula in
Excel to extract the URL. The Excel HYPERLINK() function turns a URL into a
hyperlink.
--
John Nurick
Microsoft Access MVP


:

I am exporting records from Access to Excel. One of the fields is a
hyperlink and I am having trouble getting a working link by the time it
reaches Excel. What techniques are needed to make this work?

One thing that I noticed is that the hyperlink in Excel has leading and
trailing pound symbols (#) that weren't there in Access. If I remove the #
then the hyperlink works. Is a possible solution to prevent # from being
added? If so, how do I do that?

Thanks,

David

 

[Sharonk]

I am exporting records from Access to Excel. One of the fields is a
hyperlink and I am having trouble getting a working link by the time it
reaches Excel. What techniques are needed to make this work?

One thing that I noticed is that the hyperlink in Excel has leading and
trailing pound symbols (#) that weren't there in Access. If I remove the #
then the hyperlink works. Is a possible solution to prevent # from being
added? If so, how do I do that?

Thanks,

David

I know this post is a wee bit old but I've written a function that works for me so thought I'd share -
=HYPERLINK(MID(A2,FIND("#",A2)+1,LEN(A2)-FIND("#",A2)-1),IF(LEFT(A2,FIND("#",A2)-1)="",MID(A2,FIND("#",A2)+1,LEN(A2)-2),LEFT(A2,FIND("#",A2)-1)))

Bear in mind that all of my links have exported as two-parts and not the four that Access allows, so if you've longer links, this might not work for you.

 

[Ian]

Thanks for posting that @Sharonk! Welcome to PCR .