Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Macro to retrieve information from xml file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Macro to retrieve information from xml file
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) On Wed, 4 Nov 2009 10:25:25 -0800, Pawan wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieve information from one workbook to another | Excel Discussion (Misc queries) | |||
Macro to retrieve information | Excel Discussion (Misc queries) | |||
Macro to retrieve user information | Excel Programming | |||
Retrieve Information based on Condition | Excel Discussion (Misc queries) | |||
macro to retrieve path to opened file | Excel Programming |