Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
try setting calculation to manual before your code and then set it back to
automatic when it's finished. -- Gary Keramidas Excel 2003 "dmoney" 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
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" 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
Calc mode did not help -- there are no formulas in the data but i appreciate the attempt. "Gary Keramidas" wrote: try setting calculation to manual before your code and then set it back to automatic when it's finished. -- Gary Keramidas Excel 2003 "dmoney" 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
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. "Jim Cone" wrote: 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" 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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
dmoney wrote:
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 "Jim Cone" wrote: 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" 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. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
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 "Martin Brown" wrote: dmoney wrote: 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 "Jim Cone" wrote: 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" 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. . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
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 On Apr 14, 10:54*am, dmoney wrote: 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 "Martin Brown" wrote: dmoney wrote: 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 "Jim Cone" wrote: 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" wrote in ... 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. . .- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
dmoney wrote:
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 "Martin Brown" wrote: dmoney wrote: 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 "Jim Cone" wrote: 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" 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. . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Code
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(,)) 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with some slow code | Excel Programming | |||
help with some slow code | Excel Programming | |||
Fast code in 2003 = agonizingly slow code in 2007 | Excel Programming | |||
Slow code when used as VBA code instead of macro (copying visible columns) | Excel Programming | |||
Slow code | Excel Programming |