When you paste web data to Excel, many hyperlinks are created. In todays episode, I create a couple of lines of code to extract all of the links and write them to a new column in Excel. Episode 664 also shows you how to use the Watch Window to discover property names.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Now in yesterday's podcast we went through and copied this data from a web page and all of the links came in with of course the text to display, and not the link itself.
It'd be really cool if could get in another column all of the linked locations.
So for example, if I would right click or just hover we'll see that this is going to mrexcel.com/tip067.shtml.
or if I right click and say "Edit Hyperlink" I would actually be able to come here and copy this data, but as you can imagine, it'd be very tedious to go through especially if I had hundreds and hundreds of links, to get the link for each individual one.
Well this is a case where just a few lines of VBA can really solve the problem.
I'm going to switch over to VBA Editor.
Alt F11 and insert a new module and I'll create a new Subroutine called Get Links and I'm gonna say for each hl, that's my variable for hyperlink in ActiveSheet.Hyperlinks Next hl.
This basically will loop through all of the hyperlinks that are found in the sheet.
Now my problem is I don't use hyperlinks enough and I really don't remember the properties for hyperlinks.
So what I'm going to do is I'm going to start to run this in step mode. I'm going to press F8, press F8.
Press F8 now at this point the hyperlink variable is enabled there's something in there, and so I'm going to right-click on it and say "Add Watch" Click OK.
The watch window pops up and when I press the + sign, I get to see all of the properties associated with that variable. So for example, hl.address is the link.
That's the information I'm really trying to get.
Now the next thing I want to do though is figure out where this hyperlink is living.
What row is it on? and I don't see anything here.
That's obviously row 1 row 2 or something like that, so I'm going to go to parent and the parent does have hopefully Yes, a "Row" so, hl.parent.row is going to tell me what row we're on so now, I'm going to go back to my code. I'm going to stop running the code and we'll say something like Cells which row that would be hl.parent.row What column do I want to write this to.
Well all of my links are in Column 1 right now column A so I want to write it to column 2.
.value = hl.address and we just learned address from the Watch window. When I go to the next line, ofcourse you'll see that address becomes capitalized and parent.row becomes capitalized that means I didn't type it wrong, but at this point, I think we're good to go, so let me switch back to excel here.
Will make column A little bit wider and I'll run my macro, GetLinks.
Click Run, and there we go. I've solved this problem taking dozens of links and actually now have the link over in Column B Now in tomorrow's podcast, I'll show you how to take this column of links and go out to, every single page and pull it into a new workbook in excel.
Great trick. Want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Now in yesterday's podcast we went through and copied this data from a web page and all of the links came in with of course the text to display, and not the link itself.
It'd be really cool if could get in another column all of the linked locations.
So for example, if I would right click or just hover we'll see that this is going to mrexcel.com/tip067.shtml.
or if I right click and say "Edit Hyperlink" I would actually be able to come here and copy this data, but as you can imagine, it'd be very tedious to go through especially if I had hundreds and hundreds of links, to get the link for each individual one.
Well this is a case where just a few lines of VBA can really solve the problem.
I'm going to switch over to VBA Editor.
Alt F11 and insert a new module and I'll create a new Subroutine called Get Links and I'm gonna say for each hl, that's my variable for hyperlink in ActiveSheet.Hyperlinks Next hl.
This basically will loop through all of the hyperlinks that are found in the sheet.
Now my problem is I don't use hyperlinks enough and I really don't remember the properties for hyperlinks.
So what I'm going to do is I'm going to start to run this in step mode. I'm going to press F8, press F8.
Press F8 now at this point the hyperlink variable is enabled there's something in there, and so I'm going to right-click on it and say "Add Watch" Click OK.
The watch window pops up and when I press the + sign, I get to see all of the properties associated with that variable. So for example, hl.address is the link.
That's the information I'm really trying to get.
Now the next thing I want to do though is figure out where this hyperlink is living.
What row is it on? and I don't see anything here.
That's obviously row 1 row 2 or something like that, so I'm going to go to parent and the parent does have hopefully Yes, a "Row" so, hl.parent.row is going to tell me what row we're on so now, I'm going to go back to my code. I'm going to stop running the code and we'll say something like Cells which row that would be hl.parent.row What column do I want to write this to.
Well all of my links are in Column 1 right now column A so I want to write it to column 2.
.value = hl.address and we just learned address from the Watch window. When I go to the next line, ofcourse you'll see that address becomes capitalized and parent.row becomes capitalized that means I didn't type it wrong, but at this point, I think we're good to go, so let me switch back to excel here.
Will make column A little bit wider and I'll run my macro, GetLinks.
Click Run, and there we go. I've solved this problem taking dozens of links and actually now have the link over in Column B Now in tomorrow's podcast, I'll show you how to take this column of links and go out to, every single page and pull it into a new workbook in excel.
Great trick. Want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
