Excel/Access Import

[mbenton]

One department bent on sticking with Excel wants some contents stored in Access. They give me the Excel workbook daily which contains several sheets...I need to transport contents from one of the sheets (multiple cells on that sheet) to an Access database. I'm not sure how to do this. Plus, the Macro can be on the Excel side to export or on the Access side to import, doesn't matter to me. Has anyone had any experience with this and can give me some suggestions? Thanks.

[ranman256]

my method:
Save 1 old excel file to a generic file like File2Import.xls
LINK this file as an attached table in access...tXLfileToImport
build a query to import the data ...qaImportXLfile
put this query in a macro: mImportXLfile
cmd: openquery qaImportXLfile


now the import process is just 2 steps:


1. Get a new excel file, save it to the same name everytime, File2Import.xls
2. run the import macro.
done


if you have many SHEETS in the workbook, youd:
attach EACH sheet as a table
then make a query for each table, put all in macro.