Import data in access from excel

Question

I'm wrote the following macro which imports data from an excel file into access. The data i'm importing fluctuates however (sometimes A1:B2, sometimes A1:B5 etc...) so what I ideally would want is that it selects all the relevant data. So it should do something like:

Select cell A1
XLtoRight
XLDown
Copy this in access...

Anybody an idea on how I can achieve this?

Sub ImportExcel()

    Set ExcelApp = CreateObject("Excel.Application")
    Set ExcelWb = ExcelApp.Workbooks.Open("C:\Documents and     Settings\aa471714\Desktop\Book1.xls")
    ExcelApp.Visible = True

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Gegevens", "C:\Documents and Settings\aa471714\Desktop\Book1.xls", True, "A1:B5"

    With ExcelApp

    .Quit

    End With

    MsgBox ("De gegevens zijn ingelezen")

End Sub

One suggestion re Dynamic Ranges is covered here: stackoverflow.com/questions/15930832/… — Wayne G. Dunn

pteranodon pteranodon · Accepted Answer · 2014-02-10T17:48:46

Range is an optional parameter. Try skipping it to import the entire worksheet.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Gegevens", "C:\Documents and Settings\aa471714\Desktop\Book1.xls", True

Import data in access from excel

1 Answers