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 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-08-16 15:51:27 CET):

Re: Get Data in Memory First instead of printing it out
The procedure RS2WS shows how you can manually loop through a recordset.
As long as the recordset is open, you have access to the values returned from the database in memory.
You can also transfer the recordset values to e.g. an array variable if you want to.
You will find information on how to use array variables under the Misc. VBA tips menu.

Ole P. from Norway wrote (2006-08-16 15:47:53 CET):

Re: Procedure Does not work when file was closed and reopened
The first line in the procedure checks if the variable TargetCell contains a valid cell reference, if not the procedure is aborted.
You have to pass a valid target cell reference to the procedure if you want it to run properly.

Lester wrote (2006-08-16 10:32:10 CET):

Get Data in Memory First instead of printing it out
Hi!

The code works perfectly fine on me however, I do not want to print it on the worksheet right away. I'm trying to put a little bit of design on the worksheet.

How would I put the data returned by the sql on the memory first and just call it later following the worksheet design that I created?

Thanks!

Aga wrote (2006-08-16 02:33:27 CET):

Re: Procedure Does not work when file was closed and reopened
I analyzed it per line and found out that nothing was passed on to the TargetCell on the GetWorksheetData(strSourceFile As String, strSQL As String, TargetCell As Range). Running the script, strSourceFile has value, strSQL has value but the targetCell does not have a value.

Aga wrote (2006-08-16 02:15:23 CET):

Re: Procedure Does not work when file was closed and reopened
Hi!

It does not return any error messages. It's just doesn't work when I try to run the macro. It does not refresh when you change the SQL statement.

Ole P. from Norway wrote (2006-08-16 00:15:24 CET):

Re: Procedure Does not work when file was closed and reopened
You have to locate the line of code where the error occurs to be able to fix this problem.

Aga wrote (2006-08-15 22:28:34 CET):

Procedure Does not work when file was closed and reopened
When I used the procedure it worked on the first time. But when I close the file and reopened it, the procedure does not work. How do I fix this problem?

Ole P. from Norway wrote (2006-06-07 10:51:10 CET):

Re: Field headers on 2nd row
The easiest solution would be to move the field headers to the first row.
An alternate solution is to give your data source starting at row 2 a defined name and use the optional method suggested in the code example to retrieve your data.

Genevieve from Singapore wrote (2006-06-07 08:33:47 CET):

Field headers on 2nd row
I have a workbook in which the field headers are all on the 2nd row and ADO cannot open the recordset because of that I think. I don't think it is any other problem because I've checked all the variables entered and they're all correct. How do I ensure that in opening up the database excel reads the field headers from the 2nd row?

Ole P. from Norway wrote (2006-05-30 09:56:43 CET):

Re: Updating excel as a database
You can add records to a worksheet using ADO like this:
cn.Execute "insert into [Sheet1$] values (999, 'lastname', 'firstname', 1234, 'address'), , adCmdText

You can edit records in a worksheet using ADO like this:
cn.Execute "update [Sheet1$] set [fieldname] = 'newcontent' where id = 999, , adCmdText

Deleting records is not supported, this will not work:
cn.Execute "delete from [Sheet1$] where id = 999, , adCmdText