alex878
05-22-2012, 02:48 AM
Hi guys,
I have a question about VBA and XML.
All Im trying to do (very simple) is to extract the weather from a XML page. I have tried doing this with stock quotes from yahoo using yql and it works really well and is very easy to get what i need.
So all I want to do is to access the 15.1 in the below XML code (<b>Temperature:</b> 15.1°) but i dont know how to do this because of <![CDATA[ is in the way and how I am doing this currently is trimming the contents within the XML description tag but obviously this is a really bad idea when the spacing changes in the website.
Does anyone know how I can access the weather of 15.1 degress within <![CDATA[ ?????
Note: for the URLS (http) was removed.
Thanks.
Sub testw()
Dim xml As Object
Dim response As String
Dim URL As String
Dim bb As String
URL = "rss.weatherzone.com.au/?u=12994-1285<=aploc&lc=5594&obs=1&fc=1&warn=1"
Set xml = CreateObject("MSXML2.XMLHTTP.5.0")
With xml
.Open "GET", URL, False
.send
End With
response = xml.responseXML.xml
MsgBox response
On Error Resume Next
URL = Left(xml.responseXML.SelectNodes("//description").Item(1).Text, 33)
bb = Right(URL, 5)
msgbox "Melb. " & bb & "°C"
end sub
<description><![CDATA[
<b>Temperature:</b> 15.1°C[/COLOR]
<img align="top" src="weatherzone.com.au/images/widgets/nav_trend_down.gif" alt="falling"/>
<br />
<b>Feels like:</b> 15.1°C<br />
<b>Dew point:</b> 2.8°C
<img align="top" src="weatherzone.com.au/images/widgets/nav_trend_up.gif" alt="rising"/>
<br />
<b>Relative humidity:</b> 44%<br />
<b>Wind:</b> NW at 28 km/h, gusting to 37 km/h
<img align="top" src="weatherzone.com.au/images/widgets/nav_trend_down.gif" alt="falling"/>
<br />
<b>Rain:</b> 0.0mm since 9am<br />
<b>Pressure:</b> 1020.3 hPa
<img align="top" src="weatherzone.com.au/images/widgets/nav_trend_steady.gif" alt="steady"/>
<br />
]]></description>
I have a question about VBA and XML.
All Im trying to do (very simple) is to extract the weather from a XML page. I have tried doing this with stock quotes from yahoo using yql and it works really well and is very easy to get what i need.
So all I want to do is to access the 15.1 in the below XML code (<b>Temperature:</b> 15.1°) but i dont know how to do this because of <![CDATA[ is in the way and how I am doing this currently is trimming the contents within the XML description tag but obviously this is a really bad idea when the spacing changes in the website.
Does anyone know how I can access the weather of 15.1 degress within <![CDATA[ ?????
Note: for the URLS (http) was removed.
Thanks.
Sub testw()
Dim xml As Object
Dim response As String
Dim URL As String
Dim bb As String
URL = "rss.weatherzone.com.au/?u=12994-1285<=aploc&lc=5594&obs=1&fc=1&warn=1"
Set xml = CreateObject("MSXML2.XMLHTTP.5.0")
With xml
.Open "GET", URL, False
.send
End With
response = xml.responseXML.xml
MsgBox response
On Error Resume Next
URL = Left(xml.responseXML.SelectNodes("//description").Item(1).Text, 33)
bb = Right(URL, 5)
msgbox "Melb. " & bb & "°C"
end sub
<description><![CDATA[
<b>Temperature:</b> 15.1°C[/COLOR]
<img align="top" src="weatherzone.com.au/images/widgets/nav_trend_down.gif" alt="falling"/>
<br />
<b>Feels like:</b> 15.1°C<br />
<b>Dew point:</b> 2.8°C
<img align="top" src="weatherzone.com.au/images/widgets/nav_trend_up.gif" alt="rising"/>
<br />
<b>Relative humidity:</b> 44%<br />
<b>Wind:</b> NW at 28 km/h, gusting to 37 km/h
<img align="top" src="weatherzone.com.au/images/widgets/nav_trend_down.gif" alt="falling"/>
<br />
<b>Rain:</b> 0.0mm since 9am<br />
<b>Pressure:</b> 1020.3 hPa
<img align="top" src="weatherzone.com.au/images/widgets/nav_trend_steady.gif" alt="steady"/>
<br />
]]></description>