Use a closed workbook as a database (ADO)
2001-11-11 ADO 2 350
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 SubYou'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.