Read XML file into EXCEL 2000 3

[tbl]

Has anyone got code to convert XML to Excel 2000 ? I know that later versions of Excel can do this (a bit), but I'm stuck with different versions where the lowest is 2000. I've got a routine for Excel to XML which I've not yet tested.

Richard

 

[stefen054]

Hi

You can either use xml DOM object to extract the information and put in excel as in link

http://www.tek-tips.com/viewthread.cfm?qid=1214201

or use ADO object as in

http://www.ozgrid.com/forum/showthread.php?t=27818

Regards,
Stefen

 

[tbl]

Thanks Stefen,
The DOM version unfortunately fails to explain how you get the info into Excel, (and I'm not smart enough to work it out).
The ADO version I had already tried and gives a "Recordset cannot be created, Source XML is incomplete or invalid"

Richard

 

[Chance1234]

Hi Richard,

Dim xml_doc As New DOMDocument
Dim nde_Asset As IXMLDOMNode
Dim nde_Dividend As IXMLDOMNode

xml_doc.Loadxml (xml_Http.ResponseText) ' response text is the xml file being returned 

For Each nde_Asset In xml_doc.selectNodes("//Asset")

    str_FundCode = nde_Asset.selectSingleNode("FundCode").Text
    str_distribution = nde_Asset.selectSingleNode("IncomeDistribution").Text

etc etc

What the above code is doing there is moving through the XML File and setting the string values str_fundCode and str_distribution with values from the file,

If i was goign to bring that into a excel spreadsheet i would need to do the following

1. Reference the Worksheet I want to add the data too
2. Use a counter to reference the row where i wanted to add the data

So something like

For Each nde_Asset In xml_doc.selectNodes("//Asset")

with Sheet1 
      .cells(intR,1 = nde_Asset.selectSingleNode("FundCode").Text
    .cells(intR,2) =  str_distribution = nde_Asset.selectSingleNode("IncomeDistribution").Text

end with 


IntR = intR + 1 
Next

Chance,

F, G + Its official, its even on a organisation chart et all

 

[tbl]

Thanks for the reply Chance.
I am trying to read an xml file from my HD into Excel. I see the code ...

>> xml_doc.Loadxml (xml_Http.ResponseText) ' response text is the xml file being returned <<

but don't see where I insert the file path

Richard

 

[Chance1234]

Ah my apologies, that code came from using a http request

you cuold use somethign like


xml_doc.Loadxml (<<Insert path of XML file here>>)

Chance,

F, G + Its official, its even on a organisation chart et all

 

[tbl]

Thanks Chance, making some progress

If I use ...

intR=1
For Each nde_Asset In xml_doc.selectNodes("//Asset")
with Sheet1
.cells(intR,1) = nde_Asset.selectSingleNode("FundCode").Text
.cells(intR,2) = str_distribution = nde_Asset.selectSingleNode("IncomeDistribution").Text
end with

I get "Object variable or With Variable not set"

If I use the original code...

For Each nde_Asset In xml_doc.selectNodes("//Asset")

str_FundCode = nde_Asset.selectSingleNode("FundCode").Text
str_distribution = nde_Asset.selectSingleNode("IncomeDistribution").Text
Next

then the loop is just bypassed without error indicating (I think) that nde_asset is Nothing

Any ideas ?

 

[Chance1234]

can you post a example of your XML file



Chance,

F, G + Its official, its even on a organisation chart et all

 

[tsuji]

>xml_doc.Loadxml (<<Insert path of XML file here>>)
[tt]xml_doc.[red]Load[/red] "d:\abc\xyz.xml" [/tt]

 

[tbl]

Thanks for your input Tsuji. Have tried using xml_doc.load instead of xml_doc.loadXML but it makes no difference. The macro does pause as the file is loaded but the end result is the same.

Sample of the XML file

<?xml version="1.0" encoding="ISO-8859-1" ?>
- <powerdb.testdata version="1.0">
- <form name="5 - 3PH TTR WITH TAP CHANGER" devguid="zzw0046{w)!¦y#~5" eqguid="*¨_002¨1tbwv2)~6">
- <test date="01/10/2008" resultsguid="0000033yagow¨s`7">
- <settings>
<tag name="AssetID_SN" type="string" />
<tag name="IsThreeWinding" type="float">0</tag>
</settings>
- <nameplate>
<tag name="AllowedError" type="string">0.75</tag>
<tag name="Bil" type="string" />
<tag name="COOLANT" type="string">OIL</tag>
<tag name="Gallons" type="string" />
<tag name="MFR_YEAR_STR" type="string" />
<tag name="MiscFld1" type="string" />
<tag name="PriOnOffLoad" type="string">Off Load</tag>
<tag name="PriTapSetting" type="string" />
<tag name="Reason" type="string">Routine</tag>
<tag name="SecOnOffLoad" type="string" />
<tag name="SecTapSetting" type="string" />
<tag name="SerNo" type="string" />
<tag name="TankType" type="string">SEALED</tag>

 

[Chance1234]

Ok To get you started, i changed you xml file sample to the following

<?xml version="1.0" encoding="ISO-8859-1" ?> 
<test date="01/10/2008" resultsguid="0000033yagow¨s`7">

<settings>
  <tag name="AssetID_SN" type="string" /> 
  <tag name="IsThreeWinding" type="float">0</tag> 
</settings>

<nameplate>
  <tag name="AllowedError" type="string">0.75</tag> 
  <tag name="Bil" type="string" /> 
  <tag name="COOLANT" type="string">OIL</tag> 
  <tag name="Gallons" type="string" /> 
  <tag name="MFR_YEAR_STR" type="string" /> 
  <tag name="MiscFld1" type="string" /> 
  <tag name="PriOnOffLoad" type="string">Off Load</tag> 
  <tag name="PriTapSetting" type="string" /> 
  <tag name="Reason" type="string">Routine</tag> 
  <tag name="SecOnOffLoad" type="string" /> 
  <tag name="SecTapSetting" type="string" /> 
  <tag name="SerNo" type="string" /> 
  <tag name="TankType" type="string">SEALED</tag> 
</nameplate>
</test>

</xml>

Then the VBA code is

Sub ReadXML()
Dim xml_doc As New DOMDocument
Dim nde_test As IXMLDOMElement
Dim nde_test2 As IXMLDOMElement

xml_doc.Load "C:\Test\test.xml"




For Each nde_test In xml_doc.selectNodes("//test")

    Debug.Print nde_test.selectSingleNode("nameplate").Text
    
Next


End Sub

If you run the following you should get

0.75 OIL Off Load Routine SEALED

In the intermediate window.

I would recomend you read up on the DOM model as you will not progress much further without understanding elements and nodes.



Chance,

F, G + Its official, its even on a organisation chart et all

 

[tbl]

Thank you very much Chance

 

[tsuji]

[tt][blue]'... etc etc ...[/blue]
dim brtn as boolean
dim onode as ixmldomelement
brtn=xml_doc.Load("d:\test\testdata.xml") 'xml_doc exiting reference
intr=2
if brtn then
with ows 'the existing reference to active sheet
for each onode in xml_doc.selectnodes("//tag")
.cells(intr,1)=onode.getAttribute("name")
.cells(intr,2)=onode.getAttribute("type")
.cells(intr,3)=onode.text
intr=intr+1
next
end with
else
msgbox "loading failed for multitude of reasons..."
end if
[blue]'... etc etc ...[/blue]
[/tt]

 

[tbl]

Thank you very much Tsuji. Your code works perfectly. When I've finished the module I will post it for anyone interested

Richard

 

[tbl]

This is the complete module that reads the XML file C:\Test into an Excel spreadsheet. Many thanks to Chance and Tsuji for their help.

'Include Reference to Microsoft XML (version 4 in my case)
Sub XMLtoExcel()
Dim xml_doc As New DOMDocument
Dim brtn As Boolean
Dim onode As IXMLDOMElement
brtn = xml_doc.Load("C:\test.xml") 'xml_doc exiting reference

intr = 2
If brtn Then
With Sheets("Sheet1") 'where to write the parsed data
For Each onode In xml_doc.selectNodes("//tag")
.Cells(intr, 1) = onode.getAttribute("name")
.Cells(intr, 2) = onode.getAttribute("type")
.Cells(intr, 3) = onode.Text
intr = intr + 1
Next
End With
Else
MsgBox "Unable to open XML File"
End If
End Sub