excel automation speed ?

[phinds]

I'm using VB to stuff data into an existing excel spreadsheet, and I can hardly believe how SLOW it is. On a 750Mhz PIII, I get 180 cells per second. Does anyone have any idea why this is so slow?

My code is below if you're interested.

'
' excel data stuff --- shows that on this machine (750Mhz PIII) you can
' stuff about 180 cells per second, which is STUNNINGLY slow based on
' the processor speed; SO ... it must be that there is a connection that
' gets established for every cell stuff & if that connection could be
' kept open, it might take WAY less time to stuff all the data
'
' the doevents does not make any difference in the amount of time the
' whole thing takes, but without the doevents, the timer counts never
' happen
'
Option Explicit
Public xlApp As Excel.Application
Public xlBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Public xlRows As Integer
Public seconds As Integer
'
'
'
Private Sub cmdDoIt_Click()
    Dim ix As Integer, iy As Integer
    Dim row As Integer, col As Integer
    
 
    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open(App.Path & "\Book1.xls", , True)
    Set xlSheet = xlBook.Worksheets("Data")
    
    seconds = 0
    For ix = 0 To 600
        row = 2 + ix
        For iy = 0 To 36
            col = 2 + iy
'            xlSheet.Cells(row, col).Value = CStr(iy + ix * 10) ' as strings
            xlSheet.Cells(row, col).Value = iy + ix * 10    ' as integers
        Next iy
    Next ix
 
    xlApp.Workbooks.Close
    xlApp.Quit
    Set xlApp = Nothing
End Sub

[kropes]

hi
i really dont see anything wrong with teh code...
i wrote one for work, but did it a slight bit differnet.

from looking at the code, i am assuming that when you are inserting data, you an see it doing the inserts... (the sheet is visible)

when i did mine, i had it so that everytihng happened in teh background and after everything was completed i made eh excel application visible. i thnik it will speed up your process a bit.

if you need me to i can log into work and look that code up.

[phinds]

I've done it foreground and background and didn't see any difference in speed. any help you can give would be appreciated.

have you done any speed check on yours? I mean, if you get a result 10% or 30% different than mine, then who cares, but if you get FACTORS of difference, then it would point to something in my technique being more time-consuming that it needs to be, which I certainly hope it is since the current speed is horrible.

thanks for your time.

[yrwyddfa]

Any solution to this one - I am currently trying to read 89000 cells from a spreadsheet, and it runs really slow (3 mins or more)

[si_the_geek]

One major comment based on the code shown above - it is MUCH faster (about 10*) to read/write arrays of cells rather than a single cell at a time.

I think the property is FormulaArray (on a range), eg:

tmp_Array = Array (0, 1, 3.3, 2, 4.2) (or whatever code you want to fill the array!)

xlSheet.Range ("A1:E1").FormulaArray = tmp_Array

and:

tmp_Array = xlSheet.Range ("A1:E1").FormulaArray


(nb: I think you can omit FormulaArray, but the results are slightly different)

[Frans C]

The problem is marchalling. Excel and your app run in seperate memory spaces, so every call to excel needs to be marchalled across processes.
Try to reduce the number of times this is needed. Pass as much data in one call as you can.
When you need to read from excel, you could read an entire sheet into an array (variant) in one call.

Dim varValues as Variant
Dim xlApp As Object
Dim xlBook As Object
 
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(strPath, False, True)
    varValues = xlBook.Sheets(1).UsedRange.Value
    xlBook.Close False
    Set xlBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing

varValues will contain a two dimensional array, containing the complete sheet.

If you need to write data to excel, you can use a similar technique.
You can either use excel's CopyFromRecordset method, to copy a recordset to a range, or you can set a range to a two dimensional array.

[yrwyddfa]

How can you read multiple properties from a cell such as it's value and it's colour without resorting to marshalling.

Forumla Array will return it's values -but how can I get an array of colours?

[Frans C]

I don't think you can do this for properties other then value.