sankar1v

I am reading Excel File in Vb6.0, which has 2 sheets. Sheet1 contains 30,000 records and Sheet2 contains 30,000. I want to compare records present in both sheets.

Query1 = "SELECT * FROM [Sheet1$] order by LOV_TYPE,LOV_VALUE,LIC,PARENT_LIC,LANGUAGE_ID" 'Sorting Order
sconn1 = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & ExcelFileName
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
rs1.CursorLocation = adUseClient
rs1.CursorType = adOpenKeyset
rs1.LockType = adLockBatchOptimistic
rs1.Open Query1, sconn1
sheet1Array = rs1.GetRows

Query2 = "SELECT * FROM [Sheet2$] order by LOV_TYPE,LOV_VALUE,LIC,PARENT_LIC,LANGUAGE_ID" 'Sorting Order
sconn2 = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & ExcelFileName
Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenKeyset
rs2.LockType = adLockBatchOptimistic
rs2.Open Query2, sconn2
sheet2Array = rs2.GetRows
dim matchedflag as integer
for Sheet1row=0 to rs1.recordcount then
matchedflag=0
for sheet2row=0 to rs2.recordcount then
if sheet2array(0,sheet2row)=sheet1array(0,sheet1row) and sheet2array(1,sheet2row)=sheet1array(1,sheet1row) and sheet2array(2,sheet2row)=sheet1array(2,sheet1row) and sheet2array(3,sheet2row)=sheet1array(3,sheet1row) and sheet2array(4,sheet2row)=sheet1array(4,sheet1row) then
MatchedFlag=1
end if
next sheet2row
if matchedflag=0 'sheet1 Record not found in sheet2
'coding to write this record into new excel sheet
end if
next sheet1row


Both Excel sheet(sheet1, sheet2) contains same column names, but data might be different. I have to find out the mismatched records and update the same in new excel sheet. While running the above code it is not responding and showing white(blank) screen.After 15 minutes the output is coming. I want to improve the performance. Please help ASAP. There may be more than 30,000 records in each sheet. Please provide the code to retrieve the data from excel sheet by specifying path of the excel file name(without creating DSN), sheet name and how to compare two records.

TJ Belfiore

How many modules are in your program?