none
How to insert data from an excel sheet into a database? RRS feed

  • Question

  • I need to insert the data from an excel sheet into a database. and I need this to be done using a MACRO. I have data in excle sheet like this

    Name                            Value              Date

    12345_ABC_DefGeh       12345678        01.11.2018

      ......so on around 15 values.

    and i need to keep a button in the excel sheet and assign a macro to that button so that when i click the button the rows in the excel sheet should be inserted into a database. I already mainained a connection with database. I want to write an sql query to select data from table and put it into database.

    Best Regards,

    Sajjad


    sajjad haider


    • Edited by Sajjad05 Friday, February 1, 2019 9:56 AM
    Wednesday, January 30, 2019 1:10 PM

All replies

  • Export form Excel to Access - ADO:

    Sub ADOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
        ' connect to the Access database
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=C:\FolderName\DataBaseName.mdb;"
        ' open a recordset
        Set rs = New ADODB.Recordset
        rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  
        ' all records in a table
        r = 3 ' the start row in the worksheet
        Do While Len(Range("A" & r).Formula) > 0 
        ' repeat until first empty cell in column A
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("FieldName1") = Range("A" & r).Value
                .Fields("FieldName2") = Range("B" & r).Value
                .Fields("FieldNameN") = Range("C" & r).Value
                ' add more fields if necessary...
                .Update ' stores the new record
            End With
            r = r + 1 ' next row
        Loop
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    End Sub
    Export form Excel to Access - DAO:

    Sub DAOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim db As Database, rs As Recordset, r As Long
        Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb") 
        ' open the database
        Set rs = db.OpenRecordset("TableName", dbOpenTable) 
        ' get all records in a table
        r = 3 ' the start row in the worksheet
        Do While Len(Range("A" & r).Formula) > 0 
        ' repeat until first empty cell in column A
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("FieldName1") = Range("A" & r).Value
                .Fields("FieldName2") = Range("B" & r).Value
                .Fields("FieldNameN") = Range("C" & r).Value
                ' add more fields if necessary...
                .Update ' stores the new record
            End With
            r = r + 1 ' next row
        Loop
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
    End Sub


    MY BOOK

    • Marked as answer by Sajjad05 Friday, February 8, 2019 10:16 AM
    • Unmarked as answer by Sajjad05 Friday, February 8, 2019 10:17 AM
    Saturday, February 2, 2019 2:38 PM
  • I have an excel table with name, value and Date columns. I want to put these values and dates in database. I managed to create connection with Database with the following code. The strategy in this code is to go inside database with the name and then in next step go to underliyng table with the uniqueid of this name. Then with this id update values and date in underlying table. Problem is to link excel table so that should take value and dates from this excel table of the given name.

    Name               Value           Date

    12345_ABC_DefGeh   12345678        01.11.2018

    I spent too much time but still unable to solve the problem.



    • Edited by Sajjad05 Friday, February 8, 2019 10:10 AM
    Monday, February 4, 2019 11:02 AM