How to convert multivalue XML data to certain column using macro??

K

kkting

I need to import XML file to excel using macro. below are the code that i
write.

Sub makro1()

Dim a As String
Dim connection As XmlMap
Dim str As String
Dim object1 As ListObject
Dim temp1 As ListColumn

a = "C:\Documents and Settings\kting2\Desktop\testing\Alpha
Test\Alpha2\a.xml"
Set connection = ActiveWorkbook.XmlMaps.Add(a)
connection.Name = "connectXML"

Application.DisplayXMLSourcePane connection 'to display source pane
for xml

Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("B1:B1")
Dim xmltag As String

xmltag = "/root/information/name"
myRange.XPath.SetValue connection, xmltag

Set myRange = Worksheets("Sheet1").Range("B20:B20")
xmltag = "/root/information/address"

'i already import the infor "John" and "Malaysia" and then
'i don't know how to place "Paul" to "B25" and "Thailand" to "B32" here

ActiveWorkbook.XmlMaps ("connectXML").DataBinding.Refresh

End Sub

And then below is the XML file that i need to import.
<root><information>
<name>John</name>
<address>Malaysia</Malaysia>
</information>
<information>
<name>Paul</name>
<address>Thailand</address>
</information>
</root>

so anyone can teach me how to display the data?thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top