» Use a closed workbook as a database (ADO) using VBA in Microsoft Excel

With the procedures below you can use ADO to retrieve a recordset from a closed workbook and read/write data.
Call the procedure like this:

GetWorksheetData "C:FoldernameFilename.xls", "SELECT * FROM [SheetName$];", ThisWorkbook.Worksheets(1).Range("A3")
Replace SheetName with the worksheet name you want to retrieve data from.

Sub GetWorksheetData(strSourceFile As String, strSQL As String, TargetCell As Range)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long
If TargetCell Is Nothing Then Exit Sub
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & _
"DBQ=" & strSourceFile & ";"
' DriverId=790: Excel 97/2000
' DriverId=22: Excel 5/95
' DriverId=278: Excel 4
' DriverId=534: Excel 3
On Error GoTo 0
If cn Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If

' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
' rs.Open "SELECT * FROM [SheetName$]", _
cn, adOpenForwardOnly, adLockReadOnly, adCmdText
' rs.Open "SELECT * FROM [SheetName$]", _
cn, adOpenStatic, adLockOptimistic, adCmdText
' rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%'", _
cn, adOpenStatic, adLockOptimistic, adCmdText
' rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%' ORDER BY [Field Name]", _
cn, adOpenStatic, adLockOptimistic, adCmdText

' optional ways of retrieving a recordset
' Set rs = cn.Execute("[A1:Z1000]") ' first worksheet
' Set rs = cn.Execute("[DefinedRangeName]") ' any worksheet

On Error GoTo 0
If rs Is Nothing Then
MsgBox "Can't open the file!", vbExclamation, ThisWorkbook.Name
cn.Close
Set cn = Nothing
Exit Sub
End If

RS2WS rs, TargetCell
' TargetCell.CopyFromRecordset rs ' optional approach for Excel 2000 or later

If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


The macro example assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft
ActiveX Data Objects x.x Object Library.