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

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
</bill></request="abc"></requests>
 
Last edited:

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Watch MrExcel Video

Forum statistics

Threads
1,113,776
Messages
5,544,160
Members
410,595
Latest member
Tatum2020
Top