You can open external data sources supported by the Microsoft Jet database engine by using ADO and the Microsoft Jet 4.0 OLE DB provider. Use the Extended Properties of the Connection object to pass the connection string. Hands-On 10-6 demonstrates how to open a Microsoft Excel spreadsheet named C:\Report.xls by using ADO.
©Hands-On 10-6: Opening an Excel Spreadsheet with ADO
This hands-on uses the Report.xls spreadsheet file included in the book's downloadable files. You can modify the procedure to use any other spreadsheet file.
1. In the Database window, press Alt+F11 to switch to the Visual Basic Editor window.
2. In the Visual Basic Editor window, choose Insert | Module.
3. In the module's Code window, type the Open_ExcelSpread procedure shown below.
4. Choose Run | Run Sub/UserForm to execute the procedure.
Dim conn As ADODB.Connection Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Report.xls;" & _ "Extended Properties=Excel 8.0;" MsgBox "Excel spreadsheet was opened." conn.Close Set conn = Nothing
MsgBox "Excel spreadsheet was closed." End Sub
Was this article helpful?