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:

imports the xml´s (one by one) as tables to a worksheet
performs a bunch of string functions/iterations
puts the results in an array
deletes xml table and repeat from #1
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.

Do the xml files have tabular data or something more complex? It is possible to use the COM MSXML library to directly interact with xml files through the xml DOM, but it can get sort of complicated depending upon the xml. — Blackhawk, Nov 14 '13 at 15:36
Yes it contains tabular data. I will look into the msxml library, thanks for the pointer. I read somewhere about XPATH which seems like a homerun on the topic, but I'm sitting on excel2007 which apparently doesn't provide full support. Have you used xpath? @blackhawk — 10199045, Nov 14 '13 at 21:32
Yes, in fact opening the xml documents with MSXML will allow you to query the DOM using XPath. I can try to dig up an example and post it as an answer. — Blackhawk, Nov 14 '13 at 21:39

10199045 · Accepted Answer · 2013-11-19T10:29:35.217

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

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

1 Answers1