Macro or VBA to import specific data from xml file

fcfcadd

New Member
Joined
Aug 17, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
I have several xml files that I would like to be able to import specific data from into an excel workbook and I am not sure where to start to get this to automated instead of dragging and dropping each time from the XML Source area. The image attached is just the first portion of the xml file. What I am trying to do is extract very specific lines of that data only to be imported into excel. The lines of data will be the same all the time for each xml file I am trying to import.
 

Attachments

  • Screen Shot 08-17-20 at 11.36 AM.PNG
    Screen Shot 08-17-20 at 11.36 AM.PNG
    67.6 KB · Views: 45

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,709
Welcome to MrExcel forums.

This can be done with a VBA macro which uses the MS XML class library, for example as a starting point:


Change the .Load line to your local file: .Load "C:\path\to\your file.xml"

And the For Next loop would need changing for your specific data. The macro requires a reference to Microsoft XML, v6.0, set via Tools -> References in the VBA editor.
 

fcfcadd

New Member
Joined
Aug 17, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
John,

I get an error when trying to run that sample code
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,709
You may need to change these lines:
VBA Code:
    Dim objDOMDocument As DOMDocument
    Set objDOMDocument = New DOMDocument
to:
VBA Code:
    Dim objDOMDocument As DOMDocument60
    Set objDOMDocument = New DOMDocument60

Post your code if you still get errors and tell us the exact error and on which line it occurs.

Remember, the example link is for that specific question and the code for your data will need extensive changes. The example was just to show the basic idea, with SelectNodes and SelectSingleNode.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,161
Messages
5,623,096
Members
415,953
Latest member
Balloonman4343

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top