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 |
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 |