Is there a way to import and process an xml table as an array instead of putting it on a spreadsheet?

Question

The question is really: How do I flush/prevent Excel from bloating my RAM.

I have a bunch of html strings stored in a seperate Sheet which points to different xml web data and I´ve created a loop that:

1. imports the xml´s (one by one) as tables to a worksheet
2. performs a bunch of string functions/iterations
3. puts the results in an array
4. deletes xml table and repeat from #1
5. pastes the result to a new worksheet

Bob would´ve been my uncle if it weren´t for the fact that all this repeated XML importing is bloating the EXCEL.EXE memory usage to exceed 1.3GB. Every time I open the file it takes forever although the file it self isn´t bigger than 300k. I´m also starting to suspect that excel "remembers" what has been imported although it has been deleted. Is there a work around for this rediculous memory onslaught?

I´m playing with the thought of processing the xml´s in an array instead of importing them to a sheet because I haven´t found another way around this, but I fear it is beyond my skill level. All help is greatly appreciated.

Answer 1

I got a solution up and running. Thanks to @Blackhawk for pointing me in the right direction. It loads the xml into de MSXML DOM and iterates through the NodeList based on GetElementsByTagName. Then it extracts the information I´m after to the array using a couple of string functions and loops.

I´m happy with the job it does but please feel free to point out where it can be made better!

Sub IterateThroughNodelistXML()
Application.ScreenUpdating = False

Dim MyArray(1 To "some upper bound", 1 To 3)
Dim xmldoc As MSXML2.DOMDocument
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
Dim xmlNode As MSXML2.IXMLDOMNode

Dim web_addresses, XML_Output As Worksheet
Dim str, strHttp, strValue_1, strValue_2 As String

Dim intPosition_1, intPosition_2 As Integer

Dim l, s As Integer
Dim i, bAscii As Byte
Dim c As Long
Dim dDate As Date

Set XML_Output = Sheets(1)
Set web_addresses = Sheets(2)

Set xmldoc = New MSXML2.DOMDocument
xmldoc.async = False

For l = 1 To web_addresses.Range("a1").CurrentRegion.Rows.Count

strHttp = web_addresses.Cells(l, 1).value

    xmldoc.Load ("http://www.some_web_page" & strHttp)

    Set xmlNodeList = xmldoc.getElementsByTagName("something")

            For s = 1 To xmlNodeList.Length

        str = xmlNodeList.Item(s).nodeTypedValue

                    intPosition_1 = InStrRev(str, "some search string", -1, vbBinaryCompare) - "some constant"
                    strValue_1 = Mid(str, intPosition_1, "some constant")

                i = 9
                bAscii = 0

                        While bAscii < 48 Or bAscii > 57

                                intPosition_2 = InStr(1, str, "some other search string", vbBinaryCompare) - i 

                                strValue_2 = Mid(str, intPosition_2, i)
                                    bAscii = AscW(strValue_2)

                            i = i - 1
                        Wend        

            c = c + 1

                dDate = Date
                MyArray(c, 1) = strValue_1
                MyArray(c, 2) = strValue_2
                MyArray(c, 3) = dDate

            Next s

   Next l

   XML_Output.Range("a4").Resize(UBound(MyArray, 1), UBound(MyArray, 2)).value = MyArray

   Application.ScreenUpdating = True
End Sub