Link to home
Start Free TrialLog in
Avatar of Lau_Foren
Lau_Foren

asked on

Xml to excel. Why certains node items are not been copied to excel.

I am trying to read an XML file like the one below into an Excel sheet
      <Item>
            <Filename>H:\MrX@gmail.com\inbox\Company\file214.txt</Filename>
            <Location>H:\MrX@gmail.com\inbox\Company</Location>
            <ShortName>file214.txt</ShortName>
      </Item>
      <Item>
            <Filename>H:\MrX@gmail.com\inbox\Company\===file215.txt===</Filename>
            <Location>H:\MrX@gmail.com\inbox\Company</Location>
            <ShortName>===file215.txt===</ShortName>
      </Item>      
      <Item>
            <Filename>H:\MrX@gmail.com\inbox\Company\= file216.txt =</Filename>
            <Location>H:\MrX@gmail.com\inbox\Company</Location>
            <ShortName>= file216.txt =</ShortName>
      </Item>      
      
I am using the following intructions for read the xml file and copy it to the working sheet

## all dim etc omitted to make this shorter

Set list = xDoc.SelectNodes("//Results/Item")
        For Each Node In list
            iRow = iRow + 1
            '***Note: node names are Casesensitive***
            On Error Resume Next
            mySheet.Range("A" & iRow) = Node.SelectSingleNode("Filename").Text
            mySheet.Range("B" & iRow) = Node.SelectSingleNode("Location").Text
            mySheet.Range("C" & iRow) = Node.SelectSingleNode("ShortName").Text
 etc
 My problem is that when the file name starts in "=", no shortname is copied
 That is, mySheet.range ("C"&iRow) is empty in cases 2 and 3 from the example above:
 
 Questions:
 Why is this happening?
 Other than equal sign, are there any other initial character that causes the same problem ?

 Of course, I need to process an XML file with lists over 10 K files (perhaps 100-300 K files) which names I cannot control. So I would like to know what limitations exists in MSXML2.DOMDocument60 and/or MSXML2.IXMLDOMNodeList as to unrecognizible characters or maximum node processing capacity.
ASKER CERTIFIED SOLUTION
Avatar of Lau_Foren
Lau_Foren

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial