Import xml intro excel

fabesmoros

New Member
Joined
Sep 26, 2014
Messages
3
I am importing an xml file into excel using VBA code. I am able to import the complete file but I would like to only import certain information.

The code I have to import the whole xml:
Sub ImportXMLtoList()
Dim strTargetFile As String
Application.DisplayAlerts = False
strTargetFile = "C:\Users\adrros\Desktop\Practice\TranD.xml"
Workbooks.OpenXML Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList
Application.DisplayAlerts = True
End Sub

This is an example of the mapping of the xml:
<Requests>
<Request="ABC" ReferenceNumber="1234" Date="2014-09-22" ID="123456789">
<BillTo>
<FirstName>JOHN</FirstName>
<LastName>SMITH</LastName>
<Address1>123 JOHN STREET</Address1>
<City>NEW YORK</City>
<State>NY</State>
<Zip>35421</Zip>
<Email>JOHNSMITH@HOTMAIL.com</Email>
<Country>US</Country>
<Phone>3056965225</Phone>
<IPAddress>111.111.111.1</IPAddress>
</BillTo>
<ShipTo>
<FirstName>JOHN</FirstName>
<LastName>SMITH</LastName>
<Address1>123 JOHN STREET</Address1>
<City>NEW YORK</City>
<State>NY</State>
<Zip>35421</Zip>
<Country>US</Country>
</ShipTo>
<Shipping>
<Method>TWO DAY</Method>
<Carrier/>
</Shipping>

I would only like to import the ReferenceNumber and the ID
The complete <Bill To> information
And the Zip Code and State from the Shipping information.

Thanks for your help
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

fabesmoros

New Member
Joined
Sep 26, 2014
Messages
3
< Requests > -< Request Source="ABC" MerchantReferenceNumber="11235456" RequestDate="2014-8-22" RequestID="123456789456123" >
-< BillTo >
< FirstName > JOHN < /FirstName >
< LastName >smith< /LastName >
< Address1 >123 JOHN STREET < /Address1 >
< City >NEW York< /City >
< State >NY< /State >
< Zip >326544< /Zip >
< Email >JOHNSMITH@HOTMAIL.COM< /Email > < Country >US< /Country >
< Phone >30526445698< /Phone >
< IPAddress >111.11.111.111< /IPAddress >
< /BillTo >
< ShipTo >
< FirstName > JOHN < /FirstName >
< LastName >smith< /LastName >
< Address1 >123 JOHN STREET < /Address1 >
< City >NEW YORK< /City >
< State >NY< /State >
< Zip >326544< /Zip >
< Country >US< /Country >
< /ShipTo >
< Shipping >
< Method >oneday< /Method >
< Carrier/ >
< /Shipping >
 

Watch MrExcel Video

Forum statistics

Threads
1,113,775
Messages
5,544,152
Members
410,595
Latest member
Tatum2020
Top