Use a closed workbook as a database (ADO)

 2001-11-11    Import & Export    2    126

With the procedures below you can use ADO to retrieve a recordset from a closed workbook and read/write data. In the example below, replace SheetName with the worksheet name you want to retrieve data from.

' call the procedure like this:
' GetWorksheetData "C:\Foldername\Filename.xls", "SELECT * FROM [SheetName$];", ThisWorkbook.Worksheets(1).Range("A3")

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 ' optional 
    ' TargetCell.CopyFromRecordset rs ' available in 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 the Microsoft ActiveX Data Objects x.x Object Library.


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2009-03-22 16:49:40 (GMT)

I am not aware of any ways of retrieving the formula by using ADO.
Why complicate things when you can open the workbook, read the formula/value or whatever you need and then close the workbook again, all this can be done without the user noticing it if you turn the screen updating off.

Gudjon | 2009-03-20 11:17:16 (GMT)

Hi
Is it possible to get a cell formula from closed workbook using ADO?