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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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,109,448
Messages
5,528,804
Members
409,837
Latest member
karnasrinivas
Top