how to use ADO to read and write data in Excel workbooks?

How to Use a closed workbook as a database (DAO) using VBA in Microsoft Excel ?



Dim db As DAO.Database
Dim rs As DAO.Recordset

strPath = "D:\Gopi\Automation Stuff\"
intColARws = Worksheets(1).UsedRange.Rows.Count
intColBRws = Worksheets(2).UsedRange.Rows.Count
gSheet1 = "[Sheet1$A1:A" & intColARws & "]"
gSheet2 = "[Sheet2$A1:A" & intColBRws & "]"
Set db = OpenDatabase(strPath & "CompareExcel.xlsm", False, True, "Excel 8.0;") ' read

If db Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If

Set rsLeft = db.OpenRecordset("SELECT " & gSheet1 & ".ColA" & " FROM " & gSheet1 & " left outer join " & gSheet2 & " on " & gSheet1 & ".ColA <> " & gSheet2 & ".ColB" & " where " & gSheet2 & ".ColB is null")
i = 1
While Not (rsLeft.EOF)
'MsgBox rs.Fields("ColA")
Worksheets(3).Range("A" & i).Value = rsLeft.Fields("ColA")
rsLeft.MoveNext
i = i + 1
Wend