+ Reply to Thread
Results 1 to 2 of 2

Importing Data from an Access Database Including a Hyperlink Colum

  1. #1
    B.C.Lioness
    Guest

    Importing Data from an Access Database Including a Hyperlink Colum

    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 which were
    copy/pasted to each other.

    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.


  2. #2
    Registered User
    Join Date
    08-28-2009
    Location
    Great Falls, MT
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Importing Data from an Access Database Including a Hyperlink Colum

    Ok, after much head beating, I have figured this out..

    I have an excel spreadsheet where I have a data connection to an ACCESS Database, one of the tables is hyperlinks. The hyperlink comes across as text only in this format:

    APWD15E#http://www.frigidaire.com/product.aspx?productid=2235#

    The Hash "#" shows the URL and the front is the "Friendly Display Name" of it.. I had to break this apart in order to put it into the =Hyperlink()

    The First version will insert the word NONE into the cell if there are no hashes, which would mean no hyperlink, the second one will simply leave the cell blank.

    =IF(ISERROR(FIND("#",A1)),"NONE",(HYPERLINK(SUBSTITUTE(MID(A1,FIND("#",A1,1)+1,99),"#",""),MID(A1,1, FIND("#",A1,1)-1))))

    =IF(ISERROR(FIND("#",A1)),"",(HYPERLINK(SUBSTITUTE(MID(A1,FIND("#",A1,1)+1,99),"#",""),MID(A1,1,FIND ("#",A1,1)-1))))

    Change the A1 to whatever cell your imported or table from access is that has the hyperlink info.

    This particular hyperlink will show up as the friendly name underlined:

    APWD15E

    Enjoy!
    Bob R.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1