B
B.C.Lioness
Hi there. I am importing an Access Database to an Excel spreadsheet. The DB
includes a column of hyperlinks. I am having a few issues trying to do this.
First, when I update the data in Excel, the hyperlink column turns into
plain text (the excel file updates automatically when the file is opened)
with a # added in front of it. I tried to get around this by creating a
column next to my imported data that uses =HYPERLINK(MID(G113,2,255)) to
create the hyperlink and get rid of the #. This database is 1600 lines long,
and part way through creating the hyperlinks in Access, I realized I should
check that when I open the Excel file the links work. Some do, some don't.
Now I see 2 problems:
1) Only some of the hyperlink cells had a # put in front, so the others
that didn't are now losing a valuable character
2) some of the cells have the text repeated in it eg.
http://www.samtec.com/technical specifications/overview.asp?series=TSW#
http://www.samtec.com/technical specifications/overview.asp?series=TSW#.
These repeated text cells appear to be only with web links, not file links,
but not all weblinks do it.
Some of the hyperlinks I am making are to .pdf files stored on my computer
and some are links to websites. I can't see any pattern to when/where the #
is added in front of the link, happens to web links and file links. Some of
the cells are links to the same file or website and the repeated text problem
is happening in only some of the occurences of the same links.
All of this would be easier if I could find a way to have the hyperlink
column from Access keep its hyperlink qualities when it opens in the Excel
file, but I can't find a way to do this.
I am not ruling out the need for extra columns with the proper code to
convert to hyperlink (although it seems inefficient) but I am not sure how to
deal with the occassional added # or repeated text with formulas in the Excel
file.
Any Suggestions?
Thanks,
Lori.
includes a column of hyperlinks. I am having a few issues trying to do this.
First, when I update the data in Excel, the hyperlink column turns into
plain text (the excel file updates automatically when the file is opened)
with a # added in front of it. I tried to get around this by creating a
column next to my imported data that uses =HYPERLINK(MID(G113,2,255)) to
create the hyperlink and get rid of the #. This database is 1600 lines long,
and part way through creating the hyperlinks in Access, I realized I should
check that when I open the Excel file the links work. Some do, some don't.
Now I see 2 problems:
1) Only some of the hyperlink cells had a # put in front, so the others
that didn't are now losing a valuable character
2) some of the cells have the text repeated in it eg.
http://www.samtec.com/technical specifications/overview.asp?series=TSW#
http://www.samtec.com/technical specifications/overview.asp?series=TSW#.
These repeated text cells appear to be only with web links, not file links,
but not all weblinks do it.
Some of the hyperlinks I am making are to .pdf files stored on my computer
and some are links to websites. I can't see any pattern to when/where the #
is added in front of the link, happens to web links and file links. Some of
the cells are links to the same file or website and the repeated text problem
is happening in only some of the occurences of the same links.
All of this would be easier if I could find a way to have the hyperlink
column from Access keep its hyperlink qualities when it opens in the Excel
file, but I can't find a way to do this.
I am not ruling out the need for extra columns with the proper code to
convert to hyperlink (although it seems inefficient) but I am not sure how to
deal with the occassional added # or repeated text with formulas in the Excel
file.
Any Suggestions?
Thanks,
Lori.