Problem moving hyperlinks from Excel to Access

  Bling Bling 12:56 22 Jun 06
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 06

..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 06

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 06

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 06

?

  Bling Bling 19:08 23 Jun 06

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 06

click here for guidelines about the PM system.

  Bling Bling 19:35 23 Jun 06

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 06

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…