ADO Append 4 Excel Sheets of Data to one Recordset

[philipq]

Greetings Friends,

I am in between developing a small project for the place I work at.
Currently I am helping out the call center gang with automating their reports.
There is a huge report that they spool off a web site at the end of each month...
They obtain it in the form of an excel file with 97 format, which means each sheet is limited to 65535 rows only.
So therefor the report spans to 4 sheets and could be more...
I have completely automated this report into various pivot format for them per their requirement.
However the code is slow.
There are filtering out of blanks off 2 columns, unwanted rows from the other and pivoting to obtain 4 reports with different criterias each.
I am talking about 260000+ records analysed to about 72000+ actual meaningful data for the report.

Now, I thought maybe ADO could work out the trick more efficiently and faster.
I have worked with ADO before in access/excel and know how to on the basics of connection etc.

I need to know 2 things at this point:

1) Is ADO method faster than using excel automation via variant and/or range methods combined with loops?
2) How do I append data from 4 sheets into 1 recordset to later analyse it with various select statements?

Thanks,
Philip

[June7]

You are currently doing everything within Excel? You are considering using Access to process the data?

I don't know if importing raw data into Access and then doing the 'pivot' manipulation will be faster.

There are several ways to bring the Excel data into Access.

Is the Excel data structure simple - single line of column headers with simple names followed by continuous records (no subheaders, subtotal rows)?

If that is the case, import to Access could be relatively simple. Complexity could be in the unknown number of sheets for each download.

[philipq]

Yes, I am automating a report for the call center manager and working within excel only as that's what she is comfortable with.
I am well versed with MS Access workings within its interface. However, here what I am trying to do is only utilize the ADO or ADODB object for data manipulation.

[June7]

Not sure what you mean by 'utilize the ADO or ADODB object for data manipulation'. Exactly what do you want to do with ADO? How will this produce the output report? How do you plan to connect Access to the Excel data?