YouTube Playlist links listing on Excel sheet
Sometimes you may need to scrap the links of all videos of YouTube of a playlist. Why? Maybe you like a tutorial series of a channel and want to store the links of the videos in your worksheet. If you have such kind of requirement, then the below code can be of great help to you in saving your time. To get all the links associated with the playlist page, you have to simply paste the below code in the visual basic editor.
Sometimes you may need to scrap the links of all videos of YouTube of a playlist. Why? Maybe you like a tutorial series of a channel and want to store the links of the videos in your worksheet. If you have such kind of requirement, then the below code can be of great help to you in saving your time. To get all the links associated with the playlist page, you have to simply paste the below code in the visual basic editor.
To open the vbe, press ALT+F11 and to view the code window, press F7 if it is not visible. Now paste the code there. To run the code, press F5.
In the below code, it is assumed that the HTML file is already saved at local drive which can be selected when file dialog asks you to select the file. You can also directly navigate to the playlist link but then you have to slightly modify the code and pass the value of playlist path to strURL.
Option Explicit
Sub pScrapWebPgLinks()
Dim IE As New SHDocVw.InternetExplorer
Dim strURL As Variant
Dim HTMLDoc As HTMLDocument
Dim TDelements As IHTMLElementCollection
Dim ATags As IHTMLElementCollection
Dim ATag As IHTMLElement
Dim K As Integer
strURL = Application.GetOpenFilename
If strURL = False Then Exit Sub
With IE
.Visible = True
.navigate strURL
End With
Do While IE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
Set HTMLDoc = IE.document
Set ATags = HTMLDoc.getElementsByTagName("a")
Cells.Clear
For Each ATag In ATags
If ATag.parentElement.className = "pl-video-title" Then
K = K + 1
Range("A" & K).Value = K
Range("B" & K).Value = ATag.innerText
Range("C" & K).Value = ATag.getAttribute("href")
End If
Next
End Sub
The above code is based on HTML information found on the source code page of Wise Owl Tutorial site which is a good source of useful information about Excel and VBA.
<td class="pl-video-title">
<a class="pl-video-title-link yt-uix-tile-link yt-uix-sessionlink spf-link " dir="ltr" href="/watch?v=VWLlIXjUtgY&index=14&list=PLNIs-AWhQzcmPg_uV2BZi_KRG4LKs6cRs" data-sessionlink="ei=29LIWLv6LNzyoAPf_oyADA&feature=plpp_video&ved=CC0QxjQYDSITCPvTxZvl19ICFVw5aAodXz8DwCj6LA">
SQL Server Integration Services (SSIS) Part 14 - Script Tasks using Visual Basic
</a>
<div class="pl-video-owner">
by <a href="/user/WiseOwlTutorials" class="g-hovercard yt-uix-sessionlink spf-link " data-ytid="UCbi5G5PjWBaQUFy7XU_O7yw" data-sessionlink="ei=29LIWLv6LNzyoAPf_oyADA&feature=playlist&ved=CC0QxjQYDSITCPvTxZvl19ICFVw5aAodXz8DwCj6LA" data-name="playlist" >WiseOwlTutorials</a>
</div>
<div class="pl-video-bottom-standalone-badge">
</div>
</td>
Option Explicit
Sub pScrapWebPgLinks()
Dim IE As New SHDocVw.InternetExplorer
Dim strURL As Variant
Dim HTMLDoc As HTMLDocument
Dim TDelements As IHTMLElementCollection
Dim ATags As IHTMLElementCollection
Dim ATag As IHTMLElement
Dim K As Integer
strURL = Application.GetOpenFilename
If strURL = False Then Exit Sub
With IE
.Visible = True
.navigate strURL
End With
Do While IE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
Set HTMLDoc = IE.document
Set ATags = HTMLDoc.getElementsByTagName("a")
Cells.Clear
For Each ATag In ATags
If ATag.parentElement.className = "pl-video-title" Then
K = K + 1
Range("A" & K).Value = K
Range("B" & K).Value = ATag.innerText
Range("C" & K).Value = ATag.getAttribute("href")
End If
Next
End Sub
The above code is based on HTML information found on the source code page of Wise Owl Tutorial site which is a good source of useful information about Excel and VBA.
<td class="pl-video-title">
<a class="pl-video-title-link yt-uix-tile-link yt-uix-sessionlink spf-link " dir="ltr" href="/watch?v=VWLlIXjUtgY&index=14&list=PLNIs-AWhQzcmPg_uV2BZi_KRG4LKs6cRs" data-sessionlink="ei=29LIWLv6LNzyoAPf_oyADA&feature=plpp_video&ved=CC0QxjQYDSITCPvTxZvl19ICFVw5aAodXz8DwCj6LA">
SQL Server Integration Services (SSIS) Part 14 - Script Tasks using Visual Basic
</a>
<div class="pl-video-owner">
by <a href="/user/WiseOwlTutorials" class="g-hovercard yt-uix-sessionlink spf-link " data-ytid="UCbi5G5PjWBaQUFy7XU_O7yw" data-sessionlink="ei=29LIWLv6LNzyoAPf_oyADA&feature=playlist&ved=CC0QxjQYDSITCPvTxZvl19ICFVw5aAodXz8DwCj6LA" data-name="playlist" >WiseOwlTutorials</a>
</div>
<div class="pl-video-bottom-standalone-badge">
</div>
</td>
No comments:
Post a Comment