VBA Macro to retrieve information from xml file

[Pawan]

Hello

I am new to xml. I have one xml file (say C:\test.cnf.xml). When I open it
in Excel, I see several columns in it. Among them, there is one column (say
"CustID") and there are different numbers in that column. I want to count how
many 'unique' entries are there in this column. I do not know how to retrieve
data from xml files using excel vba.

Any help is highly appreciated.
Thank You

Regards,
prm

 

[Chip Pearson]

VBA itself has no support for XML. You can read an XML file as a
simple text file, but then it is taken as just that, plain text, with
no inherent structure. Somewhere on your computer, you probably have a
DLL file that provides the base types and methods for working with
XML. In VBA, go to the Tools menu, choose References, and look for
"Microsoft XML" and choose the latest version (which, I think, is
6.0).

Then, you'll need to learn a bit about the Document Object Model that
is used as the structure model of an XML file, and the XPath query
language that provides data extraction methods analogous to what SQL
is for databases. It isn't rocket science but it isn't trivial either.

If all you want is to get the number of distinct element in a range
once the data is imported to Excel, you can use an array formula like
the following:

=SUM(1/COUNTIF(C3:C12,C3:C12))

Change the two references to C3:C12 to the appropriate range.

Since this is an Array Formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will
not work properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)