VB & Excel

[Marc L'Ecuyer]

Hi,
Does anybody know a fastest method to read in a Excel worksheet : I'm on a Pentium 75 and my sheet has approx. 2000 cells and it takes 2 minutes to open excel, add the workbook, open the sheet and read the cells (with wSheet.Cells(y,x)). It's very slooooow!

Thanks,
Marc.

[Chris Eastwood]

Hi

I think you've kind of answered your own question. A Pentium 75 isn't going to be the fastest machine to make OLE/ACTIVEX calls between applications.

There are a few steps you can take to speed it up though :

1. Use the 'With' Statement to reduce COM calls

When you use 'With' statement, VB allocates an object internally to point to the referenced object, if you use a syntax such as

Object.thing.otherthing.wotsit = "blah blah blah"
Object.thing.otherthing.someotherthing = "blah blah blah"

Then COM has to make the call to "Object.thing.otherthing." every time - 3 references each time.

Using 'with' -

With Object.thing.otherthing
.wotsit = "blah blah blah"
.someotherthing = "blah blah blah"
End With

- reduces the number of COM calls and speeds up your application.


2. Not Using Excel at all!

If possible, you may want to have your Excel files saved as Comma-Separated values which you can then read into VB and parse the values yourself - this would be a whole lot quicker than using COM calls, but then you have the hassle of saving each SpreadSheet twice (ie. once as CSV, again as XLS)

Regards


Chris Eastwood


CodeGuru - the website for developers
http://www.codeguru.com/vb

[Kenneth Danner]

Using VB5 SP3

I have made a program that will print *.xls in a given directory. I
have referenced the Excel 8.0 object library. This program works fine on
my machine and any other that has Excel 8.0! Is there any Thing I can
do to make this work on a machine that has say Excel 7.0??? It seems
kinda limited if it will only work for version 8.0.

You referred to "Comma-Separated values" in your reply, would this be the way
I should go? And if so, How would I go about doing this, I mean what is the
structure of this kind of file?

Any Help would be appreciated

Kenny

[Chris Eastwood]

Hi

In regards to Excel 7 backwards compatibility, that really depends on the
object model between the versions (I don't know how much they've changed
as I don't use Excel!).

One way to ensure (?) compatibility is to remove all references to the
Excel 8 Object library and do all COM calls Late bound (ie.

Dim oObject As Object

Set oObject = CreateObject("Excel.Whatever")

- You still need to make sure that the Object Model is the same (or similar)

As for a Comma Separated file, that's pretty easy.

The format tends to be :

value1,value2,value3,value4,"Value ,,5",value6 <c/r>
value7,value8,value9,value10,value11,value12 <c/r>

etc

Where <c/r> is a carriage return.

You should be able to read this file in one line at a time (or in one large chunk if you want), then convert it to a dynamic array.

Any values that contain commas themselves, are usually enclosed by quotes.


Regards

Chris Eastwood


CodeGuru - the website for developers
http://www.codeguru.com/vb