copy hyperlink

[namrata]

hi,

I want to Copy hyperlink form my source code to my excel sheet. please tell me how to do it.

 

[Andrew Poulsom]

Use the Add method of the Hyperlinks collection. Example:

ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:= _
        "http://www.MrExcel.com", TextToDisplay:="MrExcel"

 

[namrata]

u getting it wrong

this is my source code
"

<div id="srProductTitle_B0041ORWE8_0" class="productTitle"><a href="http://www.amazon.com/AME-Sleepwear-Boys-Roddin-Pajama/dp/B0041ORYH8/ref=sr_1_1?ie=UTF8&s=apparel&qid=1290159410&sr=8-1"><img ******="if (typeof uet =='function') {
uet('af');uet('cf');

}amznJQ.available('search-js-general', function() {SPUtils.triggerATFEvent();});"
src="http://ecx.images-amazon.com/images/I/51B4A6XZYdL._SL160_AA160_.jpg" class="" border="0" alt="Product Details" width="160" height="160"/><br clear="all" />AME Sleepwear Boys 2-7 Hot Roddin' Coat Pajama</a></div>"

<div id="srItemData_B0041ORWE8_0">


From This Source code i wan to copy the link which changes with evry page.

</pre>

 

[Andrew Poulsom]

Sorry, I don't know what you mean, and I don't think I'm getting it wrong because the code I posted works.

 

[namrata]

hey m sorry.....but the code you provided will print only one link and the text i have a webpage in amazon from where evrytym i enter the product name i want it to copy the url......
the source code is


<div id="srProductTitle_B0041ORWE8_0" class="productTitle">
<a href="http://www.amazon.com/AME-Sleepwear-Boys-Roddin-Pajama/dp/B0041ORYH8/ref=sr_1_1?ie=UTF8&s=apparel&qid=1290159410&sr=8-1">
<img ******="if (typeof uet =='function') {
uet('af');uet('cf');}amznJQ.available('search-js-general', function() {SPUtils.triggerATFEvent();});"
src="http://ecx.images-amazon.com/images/I/51B4A6XZYdL._SL160_AA160_.jpg" class="" border="0" alt="Product Details"
width="160" height="160"/><br clear="all" />AME Sleepwear Boys 2-7 Hot Roddin' Coat Pajama</a></div>
<div id="srItemData_B0041ORWE8_0">

want to copy this Href Url in my excel

</pre>

 

[Andrew Poulsom]

What is the product code in that hyperlink?

 

[namrata]

B0041ORWE8http://www.mrexcel.com/forum/view-s...r_1_1?ie=UTF8&s=apparel&qid=1290159410&sr=8-1</pre>

 

[Andrew Poulsom]

What other parts of that URL are variable and what do you expect to get if you click the hyperlink?

 

[namrata]

actually i want only the code from the url



wwwdotamazondotcom/AME-Sleepwear-Boys-Roddin-Pajama/dp/B0041ORYH8/ref=sr_1_1?ie=UTF8&s=apparel&qid=1290159410&sr=8-1</pre>i have created a crawler which will enter the product in search text box and the description gets displayed. the description has hyperlink in it whose source code i already pasted form that i want the code to be displayed in my sheet....so the link will change every time i enter the product description.

 

[Andrew Poulsom]

If A1 contains B0041ORYH8 you can use the formula:

=HYPERLINK("http://www.amazon.com/AME-Sleepwear-Boys-Roddin-Pajama/dp/"&A1&"/ref=sr_1_1?ie=UTF8&s=apparel&qid=1290159410&sr=8-1""")