Sunday, March 20, 2011

How to Copy data from an Access database and save in Excel spreadsheet

'How to Copy data from an Access database into an Excel spreadsheet
'Use ADO object to open the database. Using Excel as a server, open the spreadsheet.
'Read the field names from the ADO Recordset data and add them to the first row in
'the spreadsheet to make column headers.
'To enable excel application please add excel object first.

Private Sub Form_Load()
    Dim cSource_Data As String
    Dim cTarget_Data As String
    Dim oCon As ADODB.Connection
    Dim cSQL As String

    cSource_Data = App.Path & IIf(Right$(App.Path, 1) <> "\", "\", "") & "books.mdb"
    cTarget_Data = 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_Data & _
            ";" & "Persist Security Info=False"
        .Open
        cSQL = "SELECT [Title],[URL],[ISBN],[Picture],[Pages],[CD],[Year] INTO " & _
            "[Excel 8.0;Database=" & cTarget_Data & " FROM [Books]"
        .Execute cSQL
    End With
End Sub

2 comments:

mahakk01 said...

This is one of those things which is useful for everyone. This post tells how to copy data from Access database and save in Excel spreadsheet. I find it bit difficult to understand. Is there any other way in which you can explain this?
sap support pack upgrade

Hollywood Fridge Repair said...

Good reading your post

Post a Comment

 
Powered by Blogger