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