Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Excel Reading
Hi there,
I am trying to write a simple program to do excel files data comparison. I use Excel DOM to read through the cells and determine whether the data on both files differs. However, the performance is not to my expectations. For a file of 504 rows and 256 columns, I find myself processing row 60 within 2 mins. Below is the snippet of my code on the logic. Please do comment if I have missed something or advise in order to improve the performance of the program. Dim l_src_rowcount, l_tgt_rowcount As Long Dim l_src_cellcount, l_tgt_cellcount As Long Dim ls_val_src, ls_val_tgt As String Dim l_row, l_cell As Long 'io_src_xcel,io_tgt_xcel are variables for Excel Workbooks On Error Resume Next l_src_rowcount = io_src_xcel.Worksheets(1).UsedRange.Rows.Count l_tgt_rowcount = io_tgt_xcel.Worksheets(1).UsedRange.Rows.Count l_src_cellcount = io_src_xcel.Worksheets(1).UsedRange.Columns.Count l_tgt_cellcount = io_tgt_xcel.Worksheets(1).UsedRange.Columns.Count If (l_src_rowcount < l_tgt_rowcount) Or (l_src_cellcount < l_tgt_cellcount) Then Return 0 End If 'This portion is the one taking alot of time For l_row = 1 To l_src_rowcount For l_cell = 1 To l_src_cellcount ls_val_src = io_src_xcel.Worksheets(1).Cells(l_row, l_cell).value ls_val_tgt = io_tgt_xcel.Worksheets(1).Cells(l_row, l_cell).Value If ls_val_src < ls_val_tgt Then 'Mark as different End If Next Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Excel Reading
Assigning range values to arrays and comparing the arrays is generally
significantly faster. Obviously both arrays should be same size. Sub test2() Dim rng1 As Range, rng2 As Range Dim vArr1, vArr2 Const SEP As String = " < " sAddr = "a1:IV504" Worksheets(1).Range(sAddr).Value = 1 Worksheets(2).Range(sAddr).Value = 1 Worksheets(2).Range("c1:c504").Value = 2 Set rng1 = Worksheets(1).Range(sAddr) rng1.Interior.ColorIndex = xlNone Stop ' look at first sheet vArr1 = rng1 vArr2 = Worksheets(2).Range(sAddr) For r = 1 To UBound(vArr1) For c = 1 To UBound(vArr1, 2) If vArr1(r, c) < vArr2(r, c) Then vArr1(r, c) = vArr1(r, c) & SEP & vArr2(r, c) rng1(r, c).Interior.ColorIndex = 6 End If Next Next Worksheets(1).Range(sAddr).Value = vArr1 End Sub Regards, Peter T "Kwan Thean Keong" wrote in message oups.com... Hi there, I am trying to write a simple program to do excel files data comparison. I use Excel DOM to read through the cells and determine whether the data on both files differs. However, the performance is not to my expectations. For a file of 504 rows and 256 columns, I find myself processing row 60 within 2 mins. Below is the snippet of my code on the logic. Please do comment if I have missed something or advise in order to improve the performance of the program. Dim l_src_rowcount, l_tgt_rowcount As Long Dim l_src_cellcount, l_tgt_cellcount As Long Dim ls_val_src, ls_val_tgt As String Dim l_row, l_cell As Long 'io_src_xcel,io_tgt_xcel are variables for Excel Workbooks On Error Resume Next l_src_rowcount = io_src_xcel.Worksheets(1).UsedRange.Rows.Count l_tgt_rowcount = io_tgt_xcel.Worksheets(1).UsedRange.Rows.Count l_src_cellcount = io_src_xcel.Worksheets(1).UsedRange.Columns.Count l_tgt_cellcount = io_tgt_xcel.Worksheets(1).UsedRange.Columns.Count If (l_src_rowcount < l_tgt_rowcount) Or (l_src_cellcount < l_tgt_cellcount) Then Return 0 End If 'This portion is the one taking alot of time For l_row = 1 To l_src_rowcount For l_cell = 1 To l_src_cellcount ls_val_src = io_src_xcel.Worksheets(1).Cells(l_row, l_cell).value ls_val_tgt = io_tgt_xcel.Worksheets(1).Cells(l_row, l_cell).Value If ls_val_src < ls_val_tgt Then 'Mark as different End If Next Next |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
optimizing excel work sheet | Excel Programming | |||
MapPoint Optimizing | Excel Discussion (Misc queries) | |||
Optimizing Code | Excel Programming | |||
optimizing a macro | Excel Programming | |||
Optimizing in VB | Excel Programming |