Importing excel spreadsheet

[nayfeh]

Hi all,

New to programming in access. I would like to import data from an excel spreadsheet into an access table. The file is e-mailed to us on a weekly basis and is in the same format each week. Ideally I would like to have a form with a text field for the path and filename and an "IMPORT" command button. Right now I have to manipulate the file in
excel the use the import wizard which is a pain.

Does anyone have a module that can do this?
The data in the spreadsheet start from column 1, row 12. It goes up to column 19 and can have different number of records each week.

The fields in the spreadsheet are (in order):
Date|Invoice#|Description|Card|Site|Address|Grade1|Litres1|Price1|Amount1|Gr
ade2|Litres2|Price2|Amount2|GST|Misc|GrossAmt

In Cells:
A9|B9|C9|E9|F9|I9|J9|K9|L9|M9|N9|O9|P9|Q9|R9|S9

The table in access is called tbl_Transactions and have the same field names as the spreadsheet. I thought this would make it easier.

Your help is much appreciated.

Thanks,
TN

 

[Chrisbi]

I have used this code in the 'on click' event of command button. Since I want to replace the existing tables, I delete them first with

DoCmd.RunSQL "DELETE FROM tblCase;"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblCase", "(your path & filename",True

VB Help on docmd & transferspreadsheet can give you details on all the arguments. If you use a variable for path/file (like me.yourtextbox) just watch quote marks....as thepath/file have to be a string.

Also, when you import data it matches validation rules in underlying table thus if you set one field as required & a record that you are trying to import does not have that required field completed, it will not import that part. record.

I am also a newbie at this so I suspect there might be a better way but this seems to work OK for me... I use it for weekly replacement of 5 tables.

 

[BillPower]

Hi AccessRook and Chrisbi,

I have a demo of how to import almost any variation of Excel Workbooks/Worksheets.

The file to look at is Import Multiple Excel Worksheets into Access at

http://www.wpower.fsbusiness.co.uk/

Might give you an idea or two.

Bill