Import frem web - show all tables

[Simonlandersen]

Hi

Im using Excel 2013 without any add-ins. Im currently looking to use excel for basic web scraping. Fx would it be interesting for me to scrape certain youtube videos, so i can easily keep trap of the total views for my videos. But when i try to import from web via excel, it makes almost the entire page into one table. Therefore i cannot get the data that i want, without gettin the rest of the page aswell.

Is there any add-in that can filter the big table on youtube and let me choose only the total views, or is there any way of filtering the data that i extract from the web?

Best regards,
Simon

 

[mohan.pandey87]

Yes you can get total views data only by using HTML control object.

 

[Simonlandersen]

I'm still a bit new to this, how do i implement the HTML control object?

 

[ukmikeb]

Hi

Welcome to the MrExcel Forum.

Try :-

Sub YTvideos()
'
' YTvideos Macro
'
'Sheets("YTvideos").Select
On Error GoTo Errortst
Set aBrowser = CreateObject("InternetExplorer.Application")
    With aBrowser
        .Silent = True
        .Visible = False
        .Navigate "http://www.youtube.com/user/- Your account name -/videos"
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
    End With
    
    Set HT = aBrowser.Document
For i = 0 To 14
    Range("A" & i + 1).Value = HT.getElementsByclassName("yt-lockup-title")(i).innertext
'
    Range("B" & i + 1).Value = HT.getElementsByclassName("yt-lockup-meta")(i).innertext
 Next i
Errortst:
 Exit Sub
End Sub

Run on an empty sheet, change the upper limit of the For...Next, outputs to Columns A and B.

hth

 

[ukmikeb]

Hi

I've found a way to identify how many rows there are with the following code :-

   x = HT.getElementsByclassName("yt-lockup-title").Length
For i = 0 To x - 1
    Range("A" & i + 1).Resize(, 2) = Array(HT.getElementsByclassName("yt-lockup-title")(i).innertext, HT.getElementsByclassName("yt-lockup-meta")(i).innertext)
Next i

which replaces the existing code from For .... Next to Exit Sub inclusive.

hth