Creating Hyperlinks in Excel 2003

Creating Hyperlinks in Excel 2003

Didn't find your answer?

I am trying to create an electronic catalogue with links to our web site of products. There are 4000 products so I am trying to automate the creation of Hyperlinks.

If I manually type a web address then Excel seems to recognise it as such and when I click on the cell I go straight to the appropriate product on the web site.

However if I generate the hyperlink using, for instance, "www.etc"&A1 where A1 is the product number and then copy and past the value created I get the correct web reference but Excel does not recognise the cell as a hyperlink. Interestingly if I then double click the cell created as if to edit it then hit enter, Excel then recognises it as a hyperlink, but I don’t fancy doing that 4000 times!

Does anybody have an idea as to why this is happening and is there a solution - if not I may have some time between Xmas and the New Year!

Thanks

Gary Taylor

Replies (3)

Please login or register to join the discussion.

avatar
By gary_taylor
18th Dec 2007 21:08

Hyperlinks
Hi Richard

Thank you so much for your advice.

I think I am almost there except that the catalogue cannot have formula within it just the hyperlink. I created the links as you suggested and they work fine but if I then copy the formula and paste the values it still seems to loose the ability to launch into a web site.

Any further thoughts would be most appeciated.

Kind regards

Gary

Thanks (0)
avatar
By RichardSchollar
19th Dec 2007 09:39

A little code goes a long way
Gary

1. Create the hyperlink string using a formula like:

="http://www.yourdomain.co.uk/" & A1

where A1 holds the product IDs, so that you are left with a full web path in the formula cells (make sure you create these wherever you want the hyperlinks to actually be in the finished document eg C1:C1000). These cells won't contain hyperlinks yet.

2. Copy and pastespecial>values to fix these cells' values.

3. Select all the cells that contain these paths eg C1:C1000

4. Open up the Visual Basic Editor (most easily achieved by the shortcut combination Alt+F11)

5. Open up the Immediate Window at the bottom of the VBE using the shortcut Ctlr+G

6. Type in the following (all on one line and hit return):

For Each cell In Selection: cell.Hyperlinks.Add cell, cell.Value: Next cell

7. the above creates the hyperlinks. That's it, you're finished!

This way you have no code in the actual workbook either, you just have the newly created hyperlinks in your cells.

If you run into any difficulties, please post back.

Richard

Thanks (0)
avatar
By gary_taylor
19th Dec 2007 10:26

Absolutely brilliant
I really must go on a VBE / Macro course.

I am not sure how this worked but it did, so it’s good enough for me.

Thank you very much, I can now enjoy my Xmas break

Have a good Xmas.

Kind regards

Gary


Thanks (0)