Slow Code

[dmoney]

Hello gang -- I am writing an app in vb.net to manipulate some excel files.
the following code works fine on small files (1200 lines) but either takes
forever or fails on large files (67000 lines). im using excel 2007. for each
excel file, i create and instance of excel and leave open for saving
purposes. All this code does is trim the sheet.

AppExcel.Application.ScreenUpdating = False
Dim rng As Excel.Range
Dim wsh As Excel.Range

wsh = AppExcel.Application.ActiveSheet.UsedRange

For Each rng In wsh
rng.Value = LTrim(rng.Value)
Next rng
AppExcel.Application.ScreenUpdating = True

Any help is appreciated.

 

[Gary Keramidas]

try setting calculation to manual before your code and then set it back to
automatic when it's finished.

 

[Jim Cone]

I know that MS's latest fad language doesn't require the use of "Set" statements.
However, give it a try and see if it makes a difference...
Set wsh = AppExcel.Application.ActiveSheet.UsedRange

You should also turn off calculation and reinstate it at the end of your code...
AppExcel.Calculation = xlCalculationManual
'other code
AppExcel.Calculation = xlCalculationAutomatic

Also, the UsedRange can often be much larger than the actual area containing data.
Futhermore, there is no "ActiveSheet" if you are automating Excel from another application,
unless you specifically make the Excel application visible.
--
Jim Cone
Portland, Oregon USA



"dmoney" <[email protected]>
wrote in message Hello gang -- I am writing an app in vb.net to manipulate some excel files.
the following code works fine on small files (1200 lines) but either takes
forever or fails on large files (67000 lines). im using excel 2007. for each
excel file, i create and instance of excel and leave open for saving
purposes. All this code does is trim the sheet.

AppExcel.Application.ScreenUpdating = False
Dim rng As Excel.Range
Dim wsh As Excel.Range

wsh = AppExcel.Application.ActiveSheet.UsedRange

For Each rng In wsh
rng.Value = LTrim(rng.Value)
Next rng
AppExcel.Application.ScreenUpdating = True

Any help is appreciated.

 

[dmoney]

Calc mode did not help -- there are no formulas in the data but i appreciate
the attempt.

 

[dmoney]

Calc mode did not help -- there are no formulas in the data and the set
command is automatically removed after i type it in the editor -I appreciate
the attempt - any other ideas - perhaps another method to remove leading edge
spaces from all cells in a sheet.

 

[Martin Brown]

Calc mode did not help -- there are no formulas in the data and the set
command is automatically removed after i type it in the editor -I appreciate
the attempt - any other ideas - perhaps another method to remove leading edge
spaces from all cells in a sheet.

It might be worth printing out the actual bounds of the range, and a
progress counter every 100 or so rows processed. Excel 2007 can be
glacially slow for moderately sized datasets under some circumstances.

But usually it requires charts and formulae to be present.

Try doubling the size of the file and see if you can predict how long it
should take to handle 67000 lines.
BTW does it work OK for 65535 or fewer lines ?

Regards,
Martin Brown

 

[dmoney]

the time it takes to process in excel running vba is 1.5 minutes. vb.net
takes 14 using the same test file. same results with 65k lines

 

[Tim Williams]

VBA runs in the same (Excel) process as the files it's accessing, so
it has a big advantage over an "out of process" approach like VB.NET
(the old VB had the same issue). Every time you make a call out to
Excel from VB, data has to be "marshalled" between the two processes,
and this is what causes the slowdown.

The only fix really is to structure your code to make the minimum
number of between-process calls. So - for example - instead of
operating on each cell individually, read all the data into your VB
app, process it there, then write it back in one operation. That
said, I'm not up to speed on the interop stuff so i can't be more
specific...

It's really much easier to do the whole thing in VBA.

Tim

 

[Martin Brown]

the time it takes to process in excel running vba is 1.5 minutes. vb.net
takes 14 using the same test file. same results with 65k lines

In that case I think you can safely conclude that vb.net is over hyped
garbage. Complain to the manufacturer and do not hold your breath.

I predict that they will say its only an order of magnitude slower and
business users do not care.

Your best bet is to figure out a way to change focus from vb.net to the
Excel application and execute a macro there.

Regards,
Martin Brown

 

[Charles Williams]

You need to read the data into a 2-dimensional array in one statement,
process the data in the array then put it back, something like this
Dim vArr As Object(,)
vArr = DirectCast(wsh.Value2, Object(,))