Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting Hyperlinks from Excel Cells to Access

    I am now in the process of converting my Excel based spreadsheet to Access tables. It's easy to import the spreadsheet into Access but there is one problem.
    One column in the Excel spreadsheet has text data in each
    cell and each cell is hyperlinked to an image of a document
    corresponding to the text entry. Access only sees the text,
    not the Hyperlink information, when you try to import.
    Is there a way of transferring the hyperlink information over to Access as well?

  2. #2
    New Lounger
    Join Date
    Apr 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Hyperlinks from Excel Cells to Access

    I had a similar problem, and posted a request for help but never got a response. However, I believe I found something that works. Put the link to the document behind the text surrounded by #. For example...

    Company 1 Doc#LinkCompany DocumentsCompany 1 Doc.doc#

    Notice that the path is relative. It needs to be relative to where the database is. You may be able to do a static path also...

    Company 1 Doc#c:databaseLinkCompany DocumentsCompany 1 Doc.doc#

    but I haven't tried this. You can get your column in the correct format in excel in a relatively straightforward way, by making a couple of new columns. In the first you have your document, in the second put the path LinkCompany Documents in the third put the formula =a1&"#"&b1&a1&"#"

    This assumes that the text you want for the Access field and the name of the document are the same. I got lucky because in my case they were. If they are not, my suggestion doesn't work real well. It may be too much trouble but if you have a lot of documents (I had thousands), I used a freeware directory lister, and saved a text file of the directory contents. Imported it into excel, set up what I described above then imported it into Access. Took me about 30 minutes, where manually linking the documents would have taken hours.

    Hope this helps.

Posting Permissions

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