Excel Automation speed (version + OS)

R

Richard Bond

I wonder if anyone can have an educated guess at this question (if not the
corrrect answer).

Every month I am going to have to open ~100,000 excel workbooks copy some
arrays of data to some ranges then save and close them so that they are
readable in Excel 97. I would like to do this as quickly and stabley as
possible.

Assuming money is not an issue............

Which version of office should I use?
How should I automate the process - VB6, .NET, something else ???
What Operating System should I use?
What are the attributes of PC hardware that will affect this process the
most?

any tips/pointers to info would be greatly appreciated.

regards,

Richard
 
F

Frank Kabel

Hi
really 100000 workbooks? To be honest if you need so many
files Excel is probably not the best application for this.

You may explain with some more detail what you're doing
with these files. Maybe a database application is a better
way to do this.
 
J

Jan Karel Pieterse

Hi Richard,
Every month I am going to have to open ~100,000 excel workbooks copy some
arrays of data to some ranges then save and close them so that they are
readable in Excel 97. I would like to do this as quickly and stabley as
possible.

Assuming money is not an issue............
Click to expand...

...Then I could do this as a paid project <smile> ?

(see website below)
Which version of office should I use?
Click to expand...

Excel XP or 2003 seems most sensible, stability-wise
How should I automate the process - VB6, .NET, something else ???
Click to expand...

Excel's own VBA is most appropriate.
What Operating System should I use?
Click to expand...

Whatever suits you best.
What are the attributes of PC hardware that will affect this process the
most?
Click to expand...

RAM and Disk speed I guess.

But the design of your routine will have the biggest impact.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
C

Charles Williams

Hi Richard,

If you really mean 100K workbooks you will certainly have severe
difficulties (memory leaks etc) using Excel to accomplish this task.

The simplest solution would probably be to make sure that the 100K workbooks
are written in CSV format and use VB or some other suitable language to read
them, and write out the result as a CSV as well:
basically simplify the file format as much as possible.


regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com
 
R

Richard Bond

Thank you for your replies - I think I need to clarify some information.

Firstly producing 100,000 workbooks wasn't my idea at all - I don't have a
choice.

Secondly theses workbooks are essentially template sheets with linked
charts - so csv output insn't an option. Nor is using office web components
(as only the spreadsheet component supports xlfileformat saving??). I have
tried using softartisans Excel writer - but this was rather slow.

I'm going to to settle for automating excel I think from vb.Net app.

So I was thinking of using:

Windows XP
Excel XP
VB.Net (hopefully the com interop layer doesn't slow things down too
much???)

Regarding Charles's question about excel memory leakage - should I restart
Excel periodically to sort this out?

regards,

Richard
 
F

Frank Kabel

Hi
still not that much information :)
In total this sounds like a medium scale project (so you
probably end up hiring someone developing this). But maybe
you can give some more information like:
- how are you using these 100K spreadsheets
- who and where do you enter information#
- how should these charts/files be linked
- WHY do you need 100K spreadsheets (I really can't think
of a business reason for this)

so in total outline your business specification first. To
be honest I still believe Excel is not the right
application for this. This sounds like a database
application with strong reporting needs. and there're
better solutions for this (KMHO) than Excel
 
M

mjclare

Richard,

This sounds like a *huge* project...

Here are a couple of thoughts...

Using VB.Net (or FoxPro or C#) is definately the way to go but I'm no
sure about 'automating Excel'. Using MS Excel as a com object wil
slow things down and add a layer of complication that I would try t
avoid.

Have you considered just opening the sheets in VB as a ODBC table? Yo
could then write your revised data to the xls file and close it. Whe
the user later opens file in excel97, the linked charts would updat
reflecting the revised data.

Another option might be to publish the sheets with embedded links to
web accessable database (MS Sql or MySQL). When the user opens th
sheet it could automatically update itself from centraly stored data.
If you could take this route then your task would just be to get th
data into your database. You could avoid the complications o
shuffling and email ing 100000 sheets every month. The downside woul
be the support issue. It caould be a daunting task to get the correc
ODBC drivers installed on all your user's machines.

Good luck,

Mike Clar
 
R

Richard Bond

Okay If we work from the back end

I have say ~1000 clients globally
Each client subscribes to ~5 Markets of Data
These ~5 Markets will be aggregated to a specific level per client
The aggregated data format is going to be an MS OLAP cube.
As soon as an OLAP cube is produced every month, reports that require this
datasource will be produced, This is done per user, This is the Excel
workbook stage.

These reports are then going to be emailed as attatchments or sent out via
cd (sounds unlikely to me however I am told there are clients without email
access?? commercial dept being over-cautious)

I think we can ignore the set up of these templates - The main problem is
the production process:

I HAVE to produce Excel files (Nothing i can say or do is going to affect
the commercial teams requirements in this matter) - so my question is still
valid - and not undoable. I would just like some advice on speed and
stability - I am reasonably well experienced in Excel programming/automation
via vb6 early and late binding - but at a client rather than a server level.

So there are now things I can control to a greater degree in terms of OS
version, Office Version etc - and I was looking for guidance on this (and
other production tips), as I don't have the facilities to test different
scenarios in time.

regards,

Richard
 
F

Frank Kabel

Hi Richard
thanks for the info. Now this makes more sense :)
Though sending out Excel files still sounds very strange for me but if
your clients want this... (I would prefer getting raw data as a
client -> did something similar for a German stock exchange).

But now to your questions:
- I would use Excel 2003 (seems to be more stable than the previous
versions)
- OS -> I wouldn't really care. Windows 2000 / Windows XP
- Creating this many files via automation you may run into the troubles
Charle's pointed out (memory leaks). So maybe closing the Excel object
would be a good idea after you've created some files

Note: Not sure if creating an XML file would also be possible 8using
Excel 2003).
 
C

Charles Williams

Hi Richard,

I thought you were reading 100K workbooks, but I guess you are actually
writing them.

I would have thought Excelwriter was a product to look at, I would have
expected it to be a lot faster than Excel at writing a large number of
spreadsheets.

Otherwise I think the overhead and memory handling problems of using
anything other than VBA- driven Excel is probably going to be large.
Charts are likely to make matters worse.

Personally I would use Excel 2002 and XP since in my experience its a bit
more stable than excel 2003 at the moment.

But you are almost certainly going to have to process in very small
(relative to 100K) batches closing excel completely after each batch, so
probably how you automate the batches is going to be the critical element.

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com
 
P

Philip Mayfield

Richard,


You are certainly going to need to close and reopen the Excel object periodically. The number of workbooks you can make will probably be dependent on...

1. The version of Excel you are using. Based upon my experience, Excel 97 will make about half as many as Excel 2000 or later. Excel 2000 and 2002 are about the same and Excel 2003 is slightly more stable.

2. If you create charts you definately want to keep the Autoscale font attribute set to false. This is a huge resource hog if you leave it on.

3. The number of fonts in the workbook, keep to a minimum.

Finally, I think that Excel Writer was designed to do this kind of thing and would be much faster than automation. There are a couple of new vendors selling .NET Excel writing tools such as Syncfusion with Essential ExcelRW.
If you must automate, then VB 6.0 would probably be faster than going through the PIO in .NET. Althought I haven't speed checked the new Office 2003 .NET system.
 
R

Robin Aerts

I was working on an excell automation task that had to copy data ranges.
Just make sure you get the data range in 1 range as an array and not
cell by cell. Pehaps this is very obvious but in my case the thing
worked 100 times faster if I got all the data of a row as an array in
stead of picking the cell data of that row cell by cell.

In C#

Application App = new Excel.ApplicationClass();
// Open Workbook ...
// Get Sheet ...
int RowIndex = 1;
// Get Row Range Object
Range ThisRow = (Excel.Range)ThisWorkSheet.Rows[RowIndex,
System.Reflection.Missing.Value];
// Get Array of data (2 dimensional Array, in this case 1 by [column
count], with VB style 1 based index)
Array RowData = (Array)ThisRow.Value;
// Loop over all Data
for (int ColIndex = 1; ColIndex <= RowData.Count; ColIndex++)
{
object CellData = RowData[ColIndex];
}
// This works MUCH faster then
for (int ColIndex = 1; ColIndex <= RowData.Count; ColIndex++)
{
object CellData = ((Excel.Range)ThisWorkSheet.Cells[ThisRow,
ColIndex]).Value
}



Microsoft Reference (in C++): Use MFC to Automate Excel and Obtain an
Array from a Range (url:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;186122)



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Top