Hello @Haluk,
Awesome!!!
I was working on similar lines using the XMLDocument and XPath but facing some difficulties w.r.t different XML formats (XML String, XML from Range.Value, XML File from ADO Recordset etc.). :-)
Below i have excluded code for excluding columns having "-Testing" for now.
The interesting thing that i see from your code is `Adding` the temp.xlsx workbook, using on default Sheet1 Range.Value(xlRangeValueXMLSpreadsheet) and grabbing Headers & Rows separately using XPath.
Q. Will the Workbook added give the same XML Element nodes everytime so that XPath does not fail? The reason why i am asking this is:
When i load a Range from an Excel worksheet using Range.Value(xlRangeValueXMLSpreadsheet) to an ADO Recordset and then convert it to XML ( to manipulate its header columns i.e. `rs:name`), the XML format is different than the XML that i get when i `Save` an ADO Recordset (with HDR=No) to an `MSXML2.DOMDocument` object.
Q. A Question that remains in mind: Is there a way to use this "XML Magic" to create an ADO Recordset from 2 different tables in closed workbook using SQL JOINS
OR
an ADO Recordset from 2 different CSV files joined together by an SQL Query JOIN ?
I am asking because most of the times when i create reports using data from a remote workbook or CSV files, i create the report based on multiple data tables joined together using ADO Queries.
How I wish Microsoft had increased the hard limit of 64 characters to atleast 255, as most of our Survey data have huge column names. :-(
Bookmarks