Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
strNameFile = "D:\Musor\ExcelSQL"
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strNameFile & _
"; Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
cnn.Open strConnect

Примерные варианты:
rst.Open "select f1 from [Лист1$A1:B4] where f1='3'", cnn

rst.Open "select f1,f2,f3, 'Лист1' as f3 from [Лист1$] ", cnn

rst.Open "select f1 from [Лист4$] where f1 in (select f2 from [Лист4$])", cnn

rst.Open "select f1,f2,f3,'Лист1' as f3 from [Лист1$] union select f1,f2,f3,'Лист2' as f3 from [Лист2$] union select f1,f2,f3,'Лист3' as f3 from [Лист3$] ", cnn


ThisWorkbook.Sheets("Лист2").Range("A5").CopyFromRecordset rst