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

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