Exporting Hyperlinks to Excel

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

When I export my query to Excel, the hyperlink fields display the entire
filepath rather than the display name shown in the Access field. Any way to
resolve this issue? Example below:
The display name for the hyperlink field is: SH0902761

What shows up in Excel is:
SH0902761#DBArchives\ProFormaInvoices\SH0902761.pdf#

Once again, I so very much appreciate all the support!
 
Hi Melissa,
I suppose that to export your hyperlink to xl from access you use something
like that

ExcelSheet.Application.ActiveSheet.Hyperlinks.Add
ExcelSheet.Application.Cells(k, i), Address:=rec_all![all]

to impose the shown name of the hyperlink you must use the texttodisplay
property so your line of code will became

ExcelSheet.Application.ActiveSheet.Hyperlinks.Add
ExcelSheet.Application.Cells(k, i), Address:=rec_all![all],
TextToDisplay:="thenameyouwanttodisplayforthehyperlink"

HTH Paolo
 
I'm just exporting the query via the Docmd.Transferspreadsheet method. I
have one hyperlink for each recordset, so I really don't think I could use
the texttodiplay property. Or maybe I'm mistaken. Furthermore, I should
have specified that I don't even need the hyperlink to transfer to Excel. I
just need the display name to show up as text. I was trying to see if there
was an expression in the query I could use to copy only the text name into a
new field. I'll keep trying.

Thanks so much for the advice.
--
Thanks.


Paolo said:
Hi Melissa,
I suppose that to export your hyperlink to xl from access you use something
like that

ExcelSheet.Application.ActiveSheet.Hyperlinks.Add
ExcelSheet.Application.Cells(k, i), Address:=rec_all![all]

to impose the shown name of the hyperlink you must use the texttodisplay
property so your line of code will became

ExcelSheet.Application.ActiveSheet.Hyperlinks.Add
ExcelSheet.Application.Cells(k, i), Address:=rec_all![all],
TextToDisplay:="thenameyouwanttodisplayforthehyperlink"

HTH Paolo

Melissa said:
When I export my query to Excel, the hyperlink fields display the entire
filepath rather than the display name shown in the Access field. Any way to
resolve this issue? Example below:
The display name for the hyperlink field is: SH0902761

What shows up in Excel is:
SH0902761#DBArchives\ProFormaInvoices\SH0902761.pdf#

Once again, I so very much appreciate all the support!
Click to expand...
Click to expand...
 
I figured it out. Thought others might be able to use this. I created a new
filed in the query named P/I Number. I used an expression to convert the
hyperlink to string and then used the instr() and Left funtion to extract the
display name.

P/I Number:
IIf(CStr(nz([proformainvoice],""))="","",Left(CStr(nz([proformainvoice],"")),InStr(1,CStr(nz([proformainvoice],"")),"#",1)-1))
--
Thanks.


Paolo said:
Hi Melissa,
I suppose that to export your hyperlink to xl from access you use something
like that

ExcelSheet.Application.ActiveSheet.Hyperlinks.Add
ExcelSheet.Application.Cells(k, i), Address:=rec_all![all]

to impose the shown name of the hyperlink you must use the texttodisplay
property so your line of code will became

ExcelSheet.Application.ActiveSheet.Hyperlinks.Add
ExcelSheet.Application.Cells(k, i), Address:=rec_all![all],
TextToDisplay:="thenameyouwanttodisplayforthehyperlink"

HTH Paolo

Melissa said:
When I export my query to Excel, the hyperlink fields display the entire
filepath rather than the display name shown in the Access field. Any way to
resolve this issue? Example below:
The display name for the hyperlink field is: SH0902761

What shows up in Excel is:
SH0902761#DBArchives\ProFormaInvoices\SH0902761.pdf#

Once again, I so very much appreciate all the support!
Click to expand...
Click to expand...
 
Back
Top