Access to Excel Slow

[Guest]

I have several complicated queries that I want to export to Excel. My
business never tends to do the things straightforward, and asks for all kinds
of crazy requests like the following (assume that a "portfolio" is a subset
of a "business"):

Sheet: Vendor Items
Header
Portfolio 1

Header
Portfolio 2

Header
Rollup of Business X (a series of portfolios on another sheet)

Header
Portfolio 3

Header
Grand Total of all of the above
....

It gets to be a nightmare, and it necessitates having to do code in VBA in
order to bring in the logic, since they are trying to get information rolled
up into the sheet with other data that is just individual portfolios. One
way that I've tried to tackle the issue in the past is to use all sorts of
union queries in order to get the data into one "final" query, export that to
excel and try to run a pre-made VBA macro I've made in Excel to format and
put all of the headers in place and what not.

It would be much easier if I could just do the following in VBA:
....
rs.movefirst
while rs.EOF = false
call WriteHeader
if rs.field("RollupType") = "Portfolio" then
call WritePortfolio(rs.fields("EntityName").value)
else
call WriteBusiness(rs.fields("EntityName").value)
end if
rs.movenext
wend
rs.close
set rs = nothing

However, this method tends to be slow, and I've tried any and all speed-up
tricks that I know of like naming things rs.fields(0).value and what not. It
seems that every time I am in Access and trying to send data to Excel in a
row-by-row fashion it is ungodly slow and could take upwards of an hour to
process production-level data by doing the DAO/ADO method, I have pinpointed
the bottleneck to not just moving from record to record, but also any OLE
Automation call to Excel, doing a call like "mysheet.Cells(y,x).Value = blah"
can take forever to run per call when you're looping even a few times through
information. Is there ANY method out there to speed up the interprocess
communication between Access and Excel in VBA?

 

[John Nurick]

Often it's possible to save time by using Excel's
Range.CopyFromRecordset method to paste all the columns and rows of a
recordset into a specified position on a worksheet in one go.

Maybe you could start with an Excel template with all the header items
in place, the cell formats you need, and a named range for the first
data cell in each block. Then

- open a recordset for Portfolio1

- get the recordcount and insert rows below the relevant named cell to
make room for the data

- use CopyFromRecordset to paste the data into the worksheet, starting
at the named cell.

- do any formatting that's still needed.

Repeat for the remaining blocks.

 

[Guest]

Hi

John is right CopyFromRecordset is much faster. If you cannot use this
approach then a few other tips are
1) Where possible use a with statement eg With Worksheet("Sheet1") to speed
up excel
2) Turn of recalculation and screen refreshing in Excel
3) Try using the getrows method of recordset to put the data into an array
this seems to be faster to loop through

 

[Guest]

Thanks for the hints guys! I never thought about doing it the other way
(getting rows from Access via Excel rather than pushing rows out to Excel via
Access). I will definitely give that a shot. I knew about turning off
screen updates and setting the calculation to manual, but didn't know about
GetRows. I will try all of these out. Is it possible to start up a workbook
in Excel and run a macro from the command-line? I'm worried that opening up
an Excel workbook from Access and using it to retreive recordsets from the
Access database I'm currently working on may cause locking issues.

Again thank you both immensely for the help!

 

[John Nurick]

I've never had any problems using Range.CopyFromRecordset when
automating Excel from Access.

 

[Guest]

Hi John

You are right, mostly CopyFromRecordSet works well, but in Office 97 I have
found issues. Also where I have connected directly to SQL Server I have not
always been able to use the command, VBA is not able to resolve all data
types.