Read XML from Excel

[smak]

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?

 

[Domenic]

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!

 

[smak]

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'

 

[Domenic]

That's great, glad you have an alternate solution.

Cheers!