VBA Fastest way to search a 40mb xml file or convert a binary array of that file to string for searching

Howdy, I have VBA running behind MS Access. I need to repeatedly search and retrieve data from a 40mb File. Its too big to load into a string variable. I've loaded it into binary array (rather zippily I might add), but I'm at a loss for how to leverage that format.....I can't seem to grasp looping thru the array a byte at a time in search of a string...... So I dumped that idea...(but I'm willing to learn how to leverage that strategy)..I'm on to FilesystemObject based searching; here's what I have so far:

I get the beggining(x) and end(y) of the node & then put it into the FLat variable.

 Set ts = FSO.OpenTextFile(DLookup("gsgtver", "Eramdat", "EramID = 1"), ForReading, False, TristateFalse)
 x = InStr(1, ts.ReadAll, ">" & sFirst & "<")

 ts.Close

 Set ts = FSO.OpenTextFile(DLookup("gsgtver", "Eramdat", "EramID = 1"), ForReading, False, TristateFalse)
 y = InStr(x, ts.ReadAll, "</FixRecord")

 ts.Close

 Set ts = FSO.OpenTextFile(DLookup("gsgtver", "Eramdat", "EramID = 1"), ForReading, False, TristateFalse)
 FLat = Mid(ts.ReadAll, x, y - x)

 ts.Close

If I didn't have to do this 10 to 120 times I'd chalk it up to "acceptable wait state", but as you can开发者_StackOverflow guess, this ain't acceptable. Any suggestions on how to do this more slickilly?

I see you're looking for slickiness, so I advise loading your XML file into an XML document object. Why treat the XML information as a string "soup", when it's actually neatly organised in an easily searchable hierarchy?

Dim xmlDoc As Object
Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")
xmlDoc.async = False ' Don't ask.
xmlDoc.Load "C:\myfile.xml" 
'Voilà, your XML is loaded and parsed, ready to be searched.

See documentation form XML DOMDocuments here.

Then you can have random access to the data in the XML document using the XPath notation (tutorial here), e.g.

Set someElement = _
    xmlDoc.selectSingleNode("//MyElementName[@MyAttributeName = 'blabla']")
theStringINeed = someElement.Text

where the stuff in quotes is an XPath. I'm sure you can retrieve the thing you're looking for using a single XPath statement. The XML object will do the searching for you if you tell it what you're looking for. That's what it's there for.

This is really the correct way of accessing XML-formatted information. Anything else and you're really just making your own life miserable in comparison.

How about the following approach:

1. Loop through each element in 40mb xml file
2. Assign element to string variable
3. Add string variable to a Collection object

You end up with a collection of strings that represent your XML. Then when you need to search it you just do a For Each through your string collection and do your comparisons one element at a time.