Problem moving hyperlinks from Excel to Access

  Bling Bling 12:56 22 Jun 2006
Locked

Hi all,

I have a list of comic books in an Excel spreadsheet that I am trying to move into an Access database. In my Excel sheet, I have a column describing each book's edition (eg, first edition, limited edition etc). I have also been using these entries as hyperlinks to pictures of each book's cover stored on my computer. I usually just highlight the text, 'insert hyperlink' and point it to the relevant jpeg.

When I try to import the data into Access, I get the list of entries, but the hyperlinks don't work. I have tried changing the data type of the 'Edition' column to 'Hyperlink' in design view, but none of the links work. I get an error about not being able to find a server.

I thought that maybe if I could get Excel to display the hyperlink addresses instead of the text, I could copy the column and paste it into a new column in my Access table, but I can't get Excel to show the hyperlinks.

With over 2,000 entries in the spreadsheet, I don't want to have to go through each one and add a new hyperlink in Access. Can anyone help?

  silverous 13:14 22 Jun 2006

..In your excel spreadsheet, add another column alongside the hyperlink.

In that new column, put a formula:

=ExpandHyperlink(A2)

where A2 is the cell with the hyperlink in.

It will give an error initially.

Press ALT+F11 to get into the VBA editor in excel, insert a module and paste in the code from here:

click here

Then you will see your new hyperlink column will contain both link and text separated by #. When you import into access and change the field type to be hyperlink it will now work!

If you want a sample spreadsheet with this working let me know.

  Bling Bling 13:50 22 Jun 2006

Hi, thanks for the help. I think i'm doing something wrong as I can't get it to work. All I get in the new cells is the error '#NAME?' I've inserted the module and pasted in the code, but it's made no difference. I tried renaming it from 'module1' to 'ExpandHyperlink' but it made no difference. Do I need to run the module somehow? Sorry if that's a stupid question, I haven't used VBA before!

  silverous 14:19 22 Jun 2006

Try pressing F9 to force it to calculate (after that you shouldn't need to do so again) - any better? If not click on the envelope next to my name and email me what you have or send me an email and i'll email you what I did so you can see!

Once it works you can select the cell then double-click the 'square dot' at the bottom right of the cell and it should push the formula all the way down alongside your comics.

  silverous 12:42 23 Jun 2006

?

  Bling Bling 19:08 23 Jun 2006

Hi Silverous, i'm still not getting any luck with this. I've tried to e-mail you through the link next to your name but unfortunately I can't add an attachment so I can't send you my spreadsheet. I'll try again and send you my email address.

  ade.h 19:25 23 Jun 2006

click here for guidelines about the PM system.

  Bling Bling 19:35 23 Jun 2006

ahh, not to worry, I've got it!

It turns out that the formula I was using, =ExpandHyperlink(A2), was at fault. On my version of Excel (Office 2002), I have to use 'insert function' and then select the 'ExpandHyperlink' function from a drop down and put in the cell to reference, so the formula becomes:

=book1.xls!ExpandHyperlink.ExpandHyperlink(A2)

As soon as I entered this, it worked! Now I just have to get it into Access and hope it works.

Thanks so much for all the help!

  silverous 22:17 23 Jun 2006

Didn't know about that, glad it worked.
I'm pretty confident the link will work once imported into access - it did for me!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best Black Friday Deals 2017

How modern book design was influenced by illustrated manuscripts

Best Black Friday Apple Deals 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)