How To Automatically Import Excel File To Access

[simoran2]

So I will have a weekly set of files dumped into a folder. These then need to be appended to three separate tables in access. Is there a way of automating this procedure?

 

[Joe4]

Sure. If you create a Macro in Access and name it AutoExec, it will automatically run upon opening the database. So you can create a macro to import/append your files. You can use Windows Scheduler to open your database at the designated time/day, hence automating the process.

 

[simoran2]

Sure. If you create a Macro in Access and name it AutoExec, it will automatically run upon opening the database. So you can create a macro to import/append your files. You can use Windows Scheduler to open your database at the designated time/day, hence automating the process.

Ok thanks. How do i create a macro to append the files?

 

[villy]

Do you mean Export from Excel into Access?

 

[Joe4]

Ok thanks. How do i create a macro to append the files?

You would just create an Append Query and then open it in your Macro using "OpenQuery".

Macros are pretty easy. Here is some information on how to create them: http://office.microsoft.com/en-us/access-help/create-a-macro-HA010030811.aspx

 

[villy]

Try this if it can help:

Private Sub FromExcelToAccess()
' 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:\IW6_Upload_Files\Working_Directory\Personal Files\LogTimeDatabase.mdb")
    ' open the database
    Set rs = db.OpenRecordset("tableLogTimeSheet", dbOpenTable)
    
    'While rs.RecordCount > 0
    'MsgBox rs.RecordCount
    '    rs.Delete
    '    rs.MoveNext
    'Wend
    ' get all records in a table
    R = 2 ' the start row in the worksheet
    Do While Len(Range("A" & R).Formula) > 0
    ' repeat until first empty cell in column A
    If rs.RecordCount = 0 Then GoTo jump:
        'MsgBox rs.Fields("LoginName")
        For i = 1 To rs.RecordCount
        On Error GoTo jump
        If Range("A" & R) = rs.Fields("LoginName") Then
            'MsgBox "Existing Name"
            With rs
                .Edit
                .Fields("Date") = Range("B" & R).Value
                .Fields("Login") = Range("C" & R).Value
                .Fields("Logout") = Range("D" & R).Value
                ' add more fields if necessary...
                .update ' stores the new record
            End With
            R = R + 1
        Else
jump:
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("LoginName") = Range("A" & R).Value
                .Fields("Date") = Range("B" & R).Value
                .Fields("Login") = Range("C" & R).Value
                .Fields("Logout") = Range("D" & R).Value
                ' add more fields if necessary...
                .update ' stores the new record
            End With
            R = R + 1 ' next row
            Exit For
        End If
        rs.MoveNext
        Next
        On Error GoTo 0
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Just change the ranges to suit your requirements

 

[simoran2]

You would just create an Append Query and then open it in your Macro using "OpenQuery".

Macros are pretty easy. Here is some information on how to create them: http://office.microsoft.com/en-us/access-help/create-a-macro-HA010030811.aspx

Thanks. I am using 2003 version of both excel and access. How to i tell the append query to point to the excel files?

 

[Trevor G]

Ok thanks. How do i create a macro to append the files?

You need to create a macro to use TransferSpreadsheet.

Select the Macro section in the database window, Create New Macro, then in the first action select TransferSpreadsheet follow the rest of the steps at the bottom. Add the other actions if doing this multiple times for the other workbooks.


Save the Macro as AutoExec.

 

[simoran2]

You would just create an Append Query and then open it in your Macro using "OpenQuery".

Macros are pretty easy. Here is some information on how to create them: http://office.microsoft.com/en-us/access-help/create-a-macro-HA010030811.aspx

Ahh think i got it - build a macro to import then build the macro to append... ill try that before the VBA option but thanks to you both

 

[Joe4]

Ahh think i got it - build a macro to import then build the macro to append... ill try that before the VBA option but thanks to you both

You got it. The good thing about Macros is you do not need to know the first thing about VBA to use them. The one drawback is that you really cannot make them dynamic (i.e. if the file names change). However, another good tool is the "Convert Macros To Visual Basic" functionality. This will often give you much of the VBA code you need in the event that you need to convert your Macros to Visual Basic to make things more dynamic. It gives you a great starting point.