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\Com pany\file2 14.txt</Fi lename>
<Location>H:\MrX@gmail.com \inbox\Com pany</Loca tion>
<ShortName>file214.txt</Sh ortName>
</Item>
<Item>
<Filename>H:\MrX@gmail.com \inbox\Com pany\===fi le215.txt= ==</Filena me>
<Location>H:\MrX@gmail.com \inbox\Com pany</Loca tion>
<ShortName>===file215.txt= ==</ShortN ame>
</Item>
<Item>
<Filename>H:\MrX@gmail.com \inbox\Com pany\= file216.txt =</Filename>
<Location>H:\MrX@gmail.com \inbox\Com pany</Loca tion>
<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("//Result s/Item")
For Each Node In list
iRow = iRow + 1
'***Note: node names are Casesensitive***
On Error Resume Next
mySheet.Range("A" & iRow) = Node.SelectSingleNode("Fil ename").Te xt
mySheet.Range("B" & iRow) = Node.SelectSingleNode("Loc ation").Te xt
mySheet.Range("C" & iRow) = Node.SelectSingleNode("Sho rtName").T ext
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.
<Item>
<Filename>H:\MrX@gmail.com
<Location>H:\MrX@gmail.com
<ShortName>file214.txt</Sh
</Item>
<Item>
<Filename>H:\MrX@gmail.com
<Location>H:\MrX@gmail.com
<ShortName>===file215.txt=
</Item>
<Item>
<Filename>H:\MrX@gmail.com
<Location>H:\MrX@gmail.com
<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("//Result
For Each Node In list
iRow = iRow + 1
'***Note: node names are Casesensitive***
On Error Resume Next
mySheet.Range("A" & iRow) = Node.SelectSingleNode("Fil
mySheet.Range("B" & iRow) = Node.SelectSingleNode("Loc
mySheet.Range("C" & iRow) = Node.SelectSingleNode("Sho
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.