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

 

[Uncle Gizmo]

See this post:-
http://www.access-programmers.co.uk/forums/showthread.php?t=72638

And this one:-
http://www.access-programmers.co.uk/forums/showthread.php?t=142382

 

[philipq]

Hi Gizmo,

Thanks for the response.

However, I am automating a report for the call center manager and working within excel only, as that's what she is comfortable with. No Access here at all.
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 efficient and fast data manipulation purpose. So, from my understanding I need to be using the recordset object. However, my issue is the appending of more than 1 sheet of excel data of the same structure, all into one recordset.

 

[Uncle Gizmo]

Ah I see. Well the simple answer is I don't know.

But I do know you can create a "Persistent Record Set" by using the Save method (.save) So having an educated guess, I reckon yo could add records to this...

See:-
F. Scott Barker’s Microsoft Access 2002 Power Programming
by F. Scott Barker
Publisher: Sams
Release Date: Sept. 13, 2001

Creating Persistent Recordsets --- For more info

 

[Uncle Gizmo]

May be more Here:-


Programming Microsoft® Visual Basic® 6.0
by Francesco Balena
Publisher: Microsoft Press
Release Date: May 26, 1999
ISBN: 9780735605589
Topic: .NET

Chapter:- Processing Data