OWC spreadsheet population & recalculation speeds

[Bill Nalen]

I'm using owc10 in a VB 6 project to do some back end calculations. We
are loading a 25meg xml spreadsheet, turning off the ui portion of owc
and setting the calculation mode to manual. We then populate the sheet
from arrays of approximately 10x5 size. This population process is
pretty slow. After the sheet is populated, we turn on the auto calc
and then turn it off again (to force a calculation). This calculation
is pretty slow too. We then grab a bunch of values from the sheet.

For basic optimization we have the number of recalcs to a minimum. We
populate the sheets in as large a chunk as possible (i.e. the biggest
array we can fit without overwriting calculations) and then read in
the largest chunk we can.

When I say slow, through Excel this calculation is nearly instant. If
I make the same changes through owc and do a calculation=automatic, it
takes nearly 6 seconds to calculate. Am I missing something? Does it
not track which portions need to be recalculated?

Here are the options we are setting after setting the url property:
msowcSpreadsheet.calculation = xlCalculationManual
msowcSpreadsheet.ScreenUpdating = False
msowcSpreadsheet.DisplayOfficeLogo = False
msowcSpreadsheet.DisplayGridlines = False
msowcSpreadsheet.DisplayHorizontalScrollBar = False
msowcSpreadsheet.DisplayPropertyToolbox = False
msowcSpreadsheet.DisplayTitleBar = False
msowcSpreadsheet.DisplayToolbar = False
msowcSpreadsheet.DisplayVerticalScrollBar = False
msowcSpreadsheet.EnableUndo = False
msowcSpreadsheet.EnableEvents = False

It seems like setting values should be nearly instant in manual mode
and the recalc should be as fast as Excel is.

Here is how I'm setting the data:
Book.Worksheets("Daily").Range("h56:h69") = inputs
where inputs is a 14 x 1 array of variants

and this is how we are forcing the recalc
Book.calculation = OWC10.XlCalculation.xlCalculationAutomatic
Book.calculation = OWC10.XlCalculation.xlCalculationManual

I'd appreciate any help.
Thanks
Bill

 

[Alvin Bruney [MVP]]

couple things, instead of recalculating the entire workbook why not try
recalcing only the used range. or load the spreadsheet and trim the range or
move it to a new sheet spefically for recalcing and. with the workbook
recalc you are calculating everyting so you should be able to reduce the
load by recalcing only what needs to be recalced.

 

[Bill Nalen]

couple things, instead of recalculating the entire workbook why not try
recalcing only the used range. or load the spreadsheet and trim the range or
move it to a new sheet spefically for recalcing and. with the workbook
recalc you are calculating everyting so you should be able to reduce the
load by recalcing only what needs to be recalced.

--

I've been doing timing tests all morning and this method seems slower.
I don't know why that is, but without any calc'ing, my run takes about
40 seconds to complete (writing to sheets, reading from sheets,
database access). If I throw in a switch to auto and a switch back to
manual, I'm at about 1:10. If I do a calc on the whole sheet or just
the range I've put in, I'm at about 2:20. Any ideas?

 

[Alvin Bruney [MVP]]

are you up to date on ALL your service packs including those from OWC, IE
and OS?
without seeing the code, that's all i can think of at the moment