Opening a Microsoft Excel Spreadsheet

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.

Sub Open_ExcelSpread()

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

Part II

0 0

Post a comment