Using VBA to grab a URL address and place in a link on and Excel Worksheet.

[rlmays]

Hi, I am pretty much a newbie to VBA, but I have a project that I am hoping VBA can help me with.
I have a spreadsheet of 1500 item numbers and in the next column a list (not hyperlinked) of the URLs for the picture of each of these items.
For 150 of these,I have successfully, manually turned the item number into the hyperlink by copying and pasting the URL into the hyperlink dialog box when it opens.
At the 10% mark I am hoping that someone can give me some information about how to work the already created hyperlink dialog box into some script that will get VBA to do the other 90% for me. I am happy to watch a video if anyone knows of one that explains this kind of thing. Any help will be appreciated. Thanks,

 

[venkat1926]

is the url in text form in B column is connected to number in A. then see reply 3 under this

How do I convert a column of text URLs into active hyperlinks in Excel? - Stack Overflow

i am quoting from above for your convenience

quote

<tbody style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
[TD="class: votecell, bgcolor: transparent"]

up vote3down vote​

[/TD]
[TD="class: answercell, bgcolor: transparent"]Here's a way I found. I'm on a Mac using Excel 2011. If column B had the text values you want to be hyperlinks, put this formula in the cell C1 (or D1 or whatever as long as it's a free column): <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap;">=HYPERLINK(B1,B1)</code> This will insert a hyperlink with the location as the link text and the "friendly name" as the link text. If you have another column that has a friendly name for each link, you could use that too. Then, you could hide the text column if you didn't want to see it.
If you have a list of IDs of something, and the urls were all <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap;">http://website.com/folder/ID</code>, such as:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">A1 | B1101 | http://website.com/folder/101102 | http://website.com/folder/102103 | http://website.com/folder/103104 | http://website.com/folder/104</code></pre>you could use something like <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap;">=WEBSITE("http://website.com/folder/"&A1,A1)</code> and you wouldn't need the list of urls. That was my situation and worked nicely.
According to this post: Hyperlink Formula in Excel this method will work in Excel 2007 as well.


answered Aug 1 '12 at 18:24

marty
unquote

[/TD]

</tbody>