These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Use a closed workbook as a database (ADO)

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:\Foldername\Filename.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 ' use with Excel 2000 or later
    
    If rs.State = adStateOpen Then
        rs.Close
    End If
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

You'll find the procedure RS2WS by clicking on this link.

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.

Document last updated 2001-11-11 12:46:39