Anyone care to explore

[stanl]

The XML capabilities, since we are talking ADO. I previously posted about this in the Articles forum, and established a thread with XL_Dennis over a yeasr ago. :dunno Stan

[malik641]

I would like to explore :) But I'll have to do some learning first. It could help me be able to help you with some of your questions that never get answered ;)

All-in-all, I'm game.

[malik641]

So Stan, how do you use XML now? And how do you use it with ADO?

[stanl]

So Stan, how do you use XML now? And how do you use it with ADO?

Where to start; and where not to avoid insulting your intelligence.

Basically I use xml 2 ways (1) with the MDAC mspersist provider for fabricated recordset's, data interoperability, and those without Office or native DB's installed (2) as a standard markup language accessible via the DOM document model/xPath for data parsing of xml documents or http returns.

ADO is directly related to #1 - but can be used with #2. Let me start with the simplest case - the fabricated recordset. I did a stint with non-profits who absolutely scamble for resources. Many didn't have Access or Excel installed, but at least they had MDAC, and that is really all you need for basic data creation and display. The Jet Provider can create xls files on the fly, as well as mdb files, but I went through the OWC route since that is also free. OK... off track here... the fabricated recordset {AKA the in-memory recordset, or in-memory structure}...

MDAC allows creation of an ADO recordset w/out a formal connection string. This permits storing both the data and field definitions. A recordset object has a save() method and 2 options 0=adt binary 1=xml [or at least microsoft's z:row version of XML, not fully accepted by the WC3] -

The most vitriolic objection to xml is it's verbosity, especially when compared to a .csv file. But a fabricated recordset can hold all data types to include memo and binary fields - which makes a .csv suck -

I prefer to run my xml procedures from either a .wsc file or an independent scripting language, but this is a VBA forum, so let's go with a VBA sub to create an xml recordset. [of course, Office 2007 is engineered to separate the code from the data, so maybe I'm on top of the curve].

Place the sub below into an .xls and run it, then open the resultant fabrs.xml file in notepad. You now have a persisted recordset (and let's assume 10,000 entries rather than 3} which can be re-opened, edited, filtered, sorted, used to create/update tables in any ADO compatible DB, easily transferred to Excel w/CopyFromRecordset()... and the list goes on.


Sub fabrs()
cXML = Application.ActiveWorkbook.Path & "\" & "fabrs.xml"
If Dir(cXML) <> "" Then Kill (cXML)
'one of several irritating stops along the way
MsgBox cXML
Set oRS = CreateObject("ADODB.Recordset")
oRS.Fields.Append "name", 200, 50, 2 Xor 32 Xor 64
oRS.Fields.Append "age", 131, , 2 Xor 32 Xor 64
oRS.Fields("age").Precision = 3
oRS.Fields("age").NumericScale = 0
oRS.Fields.Append "isMember", 11, , 2
oRS.Open , , 1, 4, -1
oRS.Addnew
oRS.Collect("name") = "Dick Clark"
oRS.Collect("age") = 101
oRS.Collect("ismember") = False
oRS.Update
oRS.Addnew
oRS.Collect("name") = "Oprah Winfrey"
oRS.Collect("age") = 47
oRS.Collect("ismember") = True
oRS.Update
oRS.Addnew
oRS.Collect("name") = "Tony Blair"
oRS.Collect("age") = 61
oRS.Collect("ismember") = False
oRS.Update
oRS.MoveFirst
MsgBox oRS.Collect("name")
oRS.Save cXML, 1
oRS.Close
Set oRS = Nothing
MsgBox "File Created"
End Sub



Stan

[stanl]

...now assuming the above posted vba code was run from a remote file and the xml sent via email.

And, you wanted it in your Spreadsheet


Sub rs2xl()
cXML = Application.ActiveWorkbook.Path & "\" & "fabrs.xml"
If Dir(cXML) = "" Then Exit Sub
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open cXML, "Provider=msPersist", 1, 4, 256
Application.ActiveWorkbook.ActiveSheet.Range("A1").CopyFromRecordset oRS
oRS.Close
Set oRS = Nothing
End Sub



Again. just a basic example, obviously formatting and other sundry items would come into play...:think: Stan