Read XML from Excel

smak

New Member
Joined
Mar 18, 2022
Messages
34
Office Version
  1. 2010
Platform
  1. Windows
I have an XML document with data provided by a client. Using my excel sheet, is there a way to search the XML to see if a value in the excel sheet is on the XML document. (example XML file name: ImportData.xml)

I know if I have a value in D5 that I want to search for in another excel sheet I can use the formula =COUNTIF(Sheet2!$1:$1048576,"*"&D5&"*").

instead of referencing all the cells in sheet2, Can I reference/search the XML file?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
One way might be to use the Open statement or FileSystemObject object to read the data from the file and assign it to a variable, so that the desired data can be extracted from that string.

However, another more efficient way would be to use the DOMDocument60 object from the MSXML2 library. This would allow you to easily access the XML content from the file.

I would suggest that you post a small sample of your XML file that includes a few records, maybe 2 or 3 records would suffice. Then someone here on the Board will likely be able to provide you with the necessary code. Of course, replace any confidential information with fake data.

Cheers!
 
Upvote 0
Solution
thanks I will try that.

they found a different way to get the raw data instead of XML so at least my immediate issue is 'resolved'
 
Upvote 0
That's great, glad you have an alternate solution.

Cheers!
 
Upvote 0

Forum statistics

Threads
1,224,243
Messages
6,177,385
Members
452,773
Latest member
D P

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