CodeItBetter Programming Another VB Programming Blog

'Database - How to copy data from an Access database into an Excel workbook using a Recordset
'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
 
ExitHere:
    Screen.MousePointer = vbDefault
End Sub