|
|
|
| Title | Use a Recordset to copy data from an Access database into an Excel workbook |
|---|
| Keywords | ADO, Access, Excel, database |
|---|
| Categories | Database, Office |
|---|
|
|
|
Open the database and build the Recordset containing the data you want to transfer. Then open the Excel workbook, find the worksheet that should contain the data, create a Range on the worksheet, and use its CopyFromRecordset method to load the data. This example also calls AutoFit to make the column widths fit the data.
|
|
Private Sub cmdLoad_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim excel_app As Excel.Application
Dim excel_sheet As Excel.Worksheet
Screen.MousePointer = vbHourglass
DoEvents
' Open the Access database.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & txtAccessFile.Text
conn.Open
' Select the Access data.
Set rs = conn.Execute("Books")
' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
' Uncomment this line to make Excel visible.
' excel_app.Visible = True
' Open the Excel workbook.
excel_app.Workbooks.Open txtExcelFile.Text
' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app
End If
' Use the Recordset to fill the table.
excel_sheet.Cells.CopyFromRecordset rs
excel_sheet.Cells.Columns.AutoFit
' Save the workbook.
excel_app.ActiveWorkbook.Save
' Shut down.
excel_app.Quit
rs.Close
conn.Close
Screen.MousePointer = vbDefault
MsgBox "Ok"
End Sub
|
| |
 |
| |
|
