Import Excel data in Access

Question

I have a table in my Access application which needs to be filled with data in a bunch of Excel files. I tried this code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, strTable, strExcelpath, True

But it overwrite the data in my access table each time instead of appending it and it gives absolutely no control over what is being sent.

I need to find a way to simply append the data from an Excel file to my Access table, both files have the same structure so I would like to know if there is a way to import it line by line without specifying the columns. However, for personal knowledge and fear of user uses, I would also like to know how to import it considering the lines and columns.

Thanks !

EDIT: Code with the select on the Excel file:

Dim cn As ADODB.Connection
Dim strQuery As String

Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & Application.CurrentProject.Path & "\Excel\test.xls;" & _
                        "Extended Properties=Excel 8.0;"
    .Open
End With

strQuery = "INSERT INTO tblClients " & _
        "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE =" & Application.CurrentProject.Path & "\Excel\test.xls].[tblImport$]"
DoCmd.RunSQL strQuery

Fionnuala Fionnuala · Accepted Answer · 2012-08-08T14:16:22

You can also refer to an Excel sheet or range in a query:

INSERT INTO Table1 ( ADate ) 
SELECT SomeDate FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$a1:a4]

Or

INSERT INTO Table1
SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$]

In a procedure:

Sub RunThisQuery()
    strQuery = "INSERT INTO tblClients " & _
       "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" _
       & Application.CurrentProject.Path & "\Excel\test.xls].[tblImport$]"

    CurrentDB.Execute strQuery, dbFailOnError
End Sub

Note that you had a space in your code after DATABASE. It must read DATABASE=, no space.

Import Excel data in Access

1 Answers