VBA help pls! how to copy (href) web link into excel cells?

[dawgnuts]

Hi guys, big fan of this website.

As I am very new to VBA, could someone please help me pull a website link from the html code in "view source" of a webpage into my excel spreadsheet cell?

For instance, on this webpage (BBC News - Home), I would like to pull the news title and hyperlink of the news title into my excel, I am able to pull the title into my cells using "sht.Range("A"& RowCount)= ele.innertext". But is there an equivalent method to copy the weblink? I highlighted the part where i am stuck in red (below).

I am currently watching a youtube video explaining how to pull data from webpages into excel via VBA, and my code basically looks like this:




<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; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Sub test()

Dim eRow As Long

Dim ele As Object

Set sht = Sheets("Sheet1")
RowCount =1
sht.Range("A"& RowCount)="Title"
sht.Range("B"& RowCount)="Web link"

eRow = Sheet1.Cells(Rows.Count,1).End(xlUp).Offset(1,0).Row

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.Visible =False
.navigate "http://www.bbc.co.uk/news/"

For Each ele In.document.all

Select Case ele.classname

Case"top-story-header"
RowCount = RowCount +1

Case"top-story-header"

sht.Range("A"& RowCount)= ele.innertext

Case"top-story-header"
sht.Range(B & RowCount)=href link here
End Select

Next ele

End With

Set objIE = Nothing

End Sub</code>

 

[dawgnuts]

the code doesnt even work but basically how do I amend this part of my code:


Case"above_postlist"

sht.Range("A"& RowCount)= ele.innertext




so that it will copy the weblink from this part of the html (ignore the smilies)

<tbody>
[TD="class: webkit-line-content"]<div id="above_postlist" class="above_postlist">[/TD]

[TD="class: webkit-line-number"][/TD]
[TD="class: webkit-line-content"][/TD]

[TD="class: webkit-line-number"][/TD]
[TD="class: webkit-line-content"] <a rel="nofollow" href=" http://www.mrexcel.com/forum/newreply.php?p=3836432&noquote=1 " class="newcontent_textcontrol" id="newreplylink_top">+ Reply to Thread[/TD]

</tbody>

 

[soulfire21]

After you use objIE.Navigate you need to wait for the page to load. To do this, add the following bit of code after the objIE.Navigate method

Do While objIE.Busy Or Not objIE.ReadyState = READYSTATE_COMPLETE
    DoEvents
Loop

 

[dawgnuts]

Thank you so much, that explains why my code works some times and others times not

 

[dawgnuts]

the code doesnt even work but basically how do I amend this part of my code:


Case"above_postlist"

sht.Range("A"& RowCount)= ele.innertext




so that it will copy the weblink from this part of the html (ignore the smilies)

<tbody>
[TD="class: webkit-line-content"]<div id="above_postlist" class="above_postlist">[/TD]

[TD="class: webkit-line-number"][/TD]
[TD="class: webkit-line-content"][/TD]

[TD="class: webkit-line-number"][/TD]
[TD="class: webkit-line-content"] <a rel="nofollow" href=" http://www.mrexcel.com/forum/newreply.php?p=3836432&noquote=1 " class="newcontent_textcontrol" id="newreplylink_top">+ Reply to Thread[/TD]

</tbody>

No one has any ideas on this? :c