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
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