Flush the DOMDocument currently in memory

[EDN Admin]

<span style="color:#6f6f6f; font-family:arial,helvetica,sans-serif
<table border="0" cellspacing="0" cellpadding="0" width="100%
<tbody>
<tr>
<td valign="top" style="
<div id="x_posts" style="font:normal normal normal 12px/normal Arial,Helvetica,sans-serif
<div id="x_edit2599524" style="padding:0px
<table id="x_post2599524" border="0" cellspacing="0" cellpadding="6" width="100%" align="center" style="
<tbody>
<tr>
<td id="x_td_post_2599524" style="
<div id="x_post_message_2599524 <span style="color:#000000; font-family:Arial; font-size:x-small Hi,<br/>
<br/>
Im working with a subroutine that I found online that could potentially streamline any monitoring I do of an XML feed online that changes frequently. None of the specifics really matter, essentially Im just parsing an XML feed and filling in a spreadsheet. <br/>
<br/>
What Id like it to do is run this subroutine every 3-5 minutes. The last line of this subroutine does this...I think. (basically calls the same subroutine after waiting 5 minutes)<br/>
<br/>
The issue Im running into is that my subroutine isnt flushing the old XML block and reloading it with up-to-date information.<br/>
<br/>
Since I wasnt the original author of this code, I cant say that Im an expert on what it all does, but it seems like its pretty straightforward XML parsing from a URL.<br/>
<br/>
My question is how do I flush what XML I have in memory so that I force the function to revisit the XML feed online to load the current information.<br/>
<br/>
Here is the code I have to work with:<br/>
<br/>
<div style=" Code:
<pre dir="ltr" style="background-color:#ececec; color:#333333; border-color:initial; width:640px; height:498px; text-align:left; font:normal normal normal 11px/normal monospace; overflow-x:auto; overflow-y:auto; border-style:inset; padding:6px; margin:0px Sub foo()
Dim xmlLoad As New MSXML2.DOMDocument
Dim allevents As IXMLDOMNode
Dim eventslen As Integer
Dim events As IXMLDOMNode
Dim XMLHttpRequest As MSXML2.XMLHTTP
Dim i As Integer
Dim URL As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
On Error Resume Next

ActiveWorkbook.Sheets("Current Lines").Range("A2:G30").ClearContents

URL = "someURL"
Set XMLHttpRequest = New MSXML2.XMLHTTP
XMLHttpRequest.Open "GET", URL, False
XMLHttpRequest.send
Set xmlLoad = New MSXML2.DOMDocument
Do Until xmlLoad.readyState = 4
Loop
xmlLoad.LoadXML (XMLHttpRequest.responseText)

Set allevents = xmlLoad.DocumentElement.ChildNodes(3)

eventslen = allevents.ChildNodes.Length

i = 0
For i = 0 To (eventslen - 1) Step 1
Set events = allevents.ChildNodes(i)
<snipped> FILL IN SPREADSHEET WITH XML DATA HERE
Next i

Set xmlLoad = Nothing
Set allevents = Nothing


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Application.OnTime Now + TimeValue("00:05:00"), "foo"
End Sub[/code]

<span style="color:#000000; font-family:Arial; font-size:x-small Im not familiar enough with the VBA XML stuff to know how to do what I want to do, so if anyone has any advice, please feel free to give it.<br/>
<br/>
p.s. Im using Excel 2010 and Microsoft XML, v6.0 reference library in the Excel VBA.<br/>
<br/>
Thanks,<br/>
mg<br/>
<br/>
</td>
</tr>
</tbody>
</table>


</td>
</tr>
</tbody>
</table>


View the full article