Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010
    Posts
    36

    Question Unanswered: export data from excel to access table

    Hi

    i need some example or any kind of help in exporting data from excel table to an ms access table. I want user to click on a button on excel that exports the available data from excel table to access. I can do it easily through access.

    Please help

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:
    Code:
    Sub ExportToAccess()
    
        '
        ' Template for the INSERT SQL statement.
        '
        ' Notes: - Replace TableName by the name of the Destination table.
        ' -----  - Replace Column_1, Column_2, etc. by the name of the columns in the Destination table.
        '        - Remove the single quotes characters when dealing with numeric data
        '          (e.g. '@1' becomes @1 if the first column is numeric.)
        '
        Const c_sql As String = "INSERT INTO Tbl_Import ( Column_1, Column_2, Column_3, Column_4 )" & _
                                "VALUES ( '@1', '@2', '@3', '@4' )"
                                
        Dim acc As Access.Application
        Dim strSQL As String
        Dim lngRow As Long
        Dim lngColumn As Long
        
        Set acc = New Access.Application
        With acc
            .OpenCurrentDatabase "C:\Sinndho\Access\db1.mdb"    ' Open the destination database.
            For lngRow = 1 To 5                                 ' Import from Line 1 to Line 5.
                strSQL = c_sql
                For lngColumn = 1 To 4                          ' Export from Column 1 to Column 4.
                    strSQL = Replace(strSQL, "@" & lngColumn, Range(Chr(lngColumn + 64) & lngRow).Value)
                Next lngColumn
                .CurrentDb.Execute strSQL
            Next lngRow
            .Quit
        End With
        Set acc = Nothing
        
    End Sub
    Note: You must add a reference to the Microsoft Access xx.x Object Library to the VBA project (xx.x = 11.0 for Access 2003, 14.0 for Access 2010, etc.).
    Have a nice day!

  3. #3
    Join Date
    Dec 2010
    Posts
    36
    thanks

    but how can i take data from excel cells and insert them into the table?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    See post#2
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2010
    Posts
    36
    Thanks Sinndho

    it works great now

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •