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: http://stackoverflow.com/questions/15930832/import-dynamic-and-static-ranges-from-excel-in-to-ms-access-that-do-not-start-at — Wayne G. Dunn, Feb 10 '14 at 17:05

pteranodon · Answer 1 · 2014-02-11T16:32:00.160

0

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

edited Feb 11 '14 at 16:32

answered Feb 10 '14 at 17:48

pteranodon

2,037
1
13
20

Import data in access from excel

1 Answers1