CodeItBetter Programming Another VB Programming Blog

'Database - How to Copy data from an Access database into an Excel spreadsheet
'Use ADO to open the database. Using Excel as a server, open the spreadsheet.
'Read the field names from the ADO Recordset and add them to the first row in
'the spreadsheet to make column headers. Read the values from the Recordset and
'add them to the spreadsheet.

'To make things neater, use the Excel server to make the column headers bold.
'Then select the data and make the columns autofit. Finally, select the second
'row and freeze the pane above, making the column headers a non-scrolling
'region.

Private Sub Form_Load()
    Dim cSource As String
    Dim cTarget As String
    Dim oCon As ADODB.Connection
    Dim cSQL As String
 
    cSource = App.Path & IIf(Right$(App.Path, 1) <> "\", "\", "") & "books.mdb"
    cTarget = App.Path & IIf(Right$(App.Path, 1) <> "\", "\", "") & "Books.xls].[Table1]"
 
    Set oCon = New ADODB.Connection
 
    With oCon
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & cSource & _
            ";" & "Persist Security Info=False"
        .Open
        cSQL = "SELECT [Title],[URL],[ISBN],[Picture],[Pages],[CD],[Year] INTO " & _
            "[Excel 8.0;Database=" & cTarget & " FROM [Books]"
        .Execute cSQL
    End With
End Sub