inventiveman
New Member
- Joined
- Jul 30, 2013
- Messages
- 8
Hi, I'm new to Web page scraping using VBA in Excel and hoping somesome can help me.
I'd like to listen to web streamed music while I work on my computer and would like the current playing song's Artist & Title displayed in a small window near the top or bottom of the screen. That window could be Notepad, or even in the StatusBar of Excel. I can do all the coding except scrape the data.
I've followed a good web tutorial on Wise Owl
but suspect these techniques may not work on webpages that contain javascript.
I have managed to do this using VBA & IE but it's unreliable. Sometimes IE doesn't fully load & freezes, and when it does work often the Artist & Title isn't displayed for 70 or more seconds and the song is half over.
So I've tried using XML to bypass the use of IE but that raises ERROR 91 ObjectVariableNotSet or other errors that I don't have the experience to fix.
The VBA code, and comments relating to problems, I've used for one of the Radio Stations is:
Many home radio stations are often slow to display the current playing song but the site I've found is fast in doing so I'd prefer to use it.
Can anyone please help?
I'd like to listen to web streamed music while I work on my computer and would like the current playing song's Artist & Title displayed in a small window near the top or bottom of the screen. That window could be Notepad, or even in the StatusBar of Excel. I can do all the coding except scrape the data.
I've followed a good web tutorial on Wise Owl
but suspect these techniques may not work on webpages that contain javascript.
I have managed to do this using VBA & IE but it's unreliable. Sometimes IE doesn't fully load & freezes, and when it does work often the Artist & Title isn't displayed for 70 or more seconds and the song is half over.
So I've tried using XML to bypass the use of IE but that raises ERROR 91 ObjectVariableNotSet or other errors that I don't have the experience to fix.
The VBA code, and comments relating to problems, I've used for one of the Radio Stations is:
VBA Code:
Sub RadioStationSongIE()
' appropriate Libraries are Referenced
Dim IE As New SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument
Dim HTMLTagNames As MSHTML.IHTMLElementCollection
Dim HTMLTagName As MSHTML.IHTMLElement
Dim Song As String
IE.Visible = True
IE.navigate "https://www.kiis1065.com.au/"
Do While IE.ReadyState <> READYSTATE_COMPLETE
' IE sometimes doesn't fully load
DoEvents 'Escape loop with CTRL-Break when page doesn't fully load
Loop
Set HTMLDoc = IE.Document
' THIS WORKS, BUT often takes 70 seconds + to get & display new Song
' if no Song (or special segment) is being Played then a generic message is displayed
Set HTMLTagNames = HTMLDoc.getElementsByClassName("po-audio-player__component-on-air__name")
Song = HTMLTagNames(0).Title
Application.StatusBar = Song
Debug.Print Song
For Each HTMLTagName In HTMLTagNames 'Check in case there's more than 1 TagName
Debug.Print HTMLTagName.Title
Next HTMLTagName
IE.Quit
End Sub
Sub RadioStationSongXML()
' this is the same as RadioStationSongIE except using XML & "GET"
' BUT raises ERROR 91 ObjectVariableNotSet
Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim HTMLTagNames As MSHTML.IHTMLElementCollection
Dim HTML_ID As MSHTML.IHTMLElement
XMLPage.Open "GET", "https://www.kiis1065.com.au/", False
XMLPage.send
HTMLDoc.body.innerHTML = XMLPage.responseText
Set HTMLTagNames = HTMLDoc.getElementsByClassName("po-audio-player__component-on-air__name")
Song = HTMLTagNames(0).Title 'This raises ERROR 91 ObjectVariableNotSet
Application.StatusBar = Song
Debug.Print Song
For Each HTMLTagName In HTMLTagNames 'Check in case there's more than 1 TagName
Debug.Print HTMLTagName.Title
Next HTMLTagName
'
' COMMENTS
' The other 2 Preferred web pages I've tried (Fast in displaying current Song) are:
'
' https://lava.net.au/stations
' KIIS 1065 (Row3 Column2)
'
' or 'Player' on this page
' https://player.lava.net.au/kiis1065
' A minute or so after the 'Play' (►) button is pressed a 'Playing Now' section opens and shows the Title & Artist for the current song.
' This section then remains open irrespective of whether the Play or Pause button is showing.
' A generic message shows when there's no song playing.
' But when using Firefox, the 'Inspect(ed) Elements' that show the songs, don't seem to be available to VBA,
' or if they are I don't know how to get them.
'
' The Title & Artist is displayed shortly after the Song starts so either of these is my preferred webpage to scrape.
'
Debug.Print Song
End SubMany home radio stations are often slow to display the current playing song but the site I've found is fast in doing so I'd prefer to use it.
Can anyone please help?
good luck with new PC setup
