The following VBA code demonstrates how SQL and OLE DB can be used to query data in Excel:
option explicit
sub main() ' {
dim con as adodb.connection
set con = openExcelConnection
dim rs as adodb.recordSet
set rs = con.execute("select * from rng_2") ' rng_2 is the name of a range. Use [sheetName$] (with $) to select from an entire sheet.
sheets("sheet3").cells(1,1).copyFromRecordset rs
end sub ' }
function openExcelConnection() as adodb.connection ' {
dim provider as string
' provider="Microsoft.ACE.OLEDB.12.0"
provider="Microsoft.ACE.OLEDB.15.0"
set openExcelConnection = new adodb.connection
openExcelConnection.open _
"Provider=" & provider & _
";Data Source=" & activeWorkbook.fullName & _
";Extended Properties=""" & "Excel 12.0;HDR=yes" & """"
end function ' }
The workbook, in which this code is executed, needs a reference to ADODB which can be added in the immediate window with