|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» 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. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
The Sweet Potato Queens' Big-Ass Cookbook and Financial Planner
Financial Peace: Revisited
Real Estate Loopholes: Secrets of Successful Real Estate Investing
Microsoft Access Version 2002 Step by Step
MP Managerial Accounting w/ Topic Tackler, Net Tutor, & PowerWeb
AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|