Transform Excel files to the existing database via the form in Access.

Question

Hello,

I has not a lot of experience in VBA programming. What I need - is the possibility to create a simple form to extract some Excel file to the defined table by adding the records. Could you please tell me what function or a set of functions can be used in this case: to open the Excel file, define the spreadsheet, extract data insert them to the db table.

Thank you in advance,

Best regards, Greg.

Answer 1

Check out this web site to get a good start.

Answer 2

KevenDenen,

Thank you a lot for your answer.

Sincerely, Greg.

Answer 3

In your access database, create a module with the following code to import a spreadsheet:

Function import()

basedir = "INSERT YOUR SPREADSHEET HERE"

DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel9, _
            "TABLENAME", _
            basedir, _
            False
End Function

VBA Code to select and define a range should be fairly simple, and I do not have it to hand, if you still need help with this let me know and I will edit this in when I have more time.

Answer 4

There is a book that I still find useful called 'Integrating Excel and Access' published by O'Reilly ((c) 2006; ISBN 0596009739). Still in publication (check Amazon) and worth the price if you are doing a lot of interaction between Excel and Access.