Importing from Excel..

[UT_Lab_Sup]

I need to import information from a Excel spreadsheet into a Table.. The
Excel files are of test results and I would like to be able to store all the
test results in one Access Table instead of having to create another table
for each test that I run...

 

[John Nurick]

Link the Excel sheets instead of importing them. Then use an append
query to move the data from the linked tables into your one main table.

 

[UT_Lab_Sup via AccessMonster.com]

Everytime the test is run a new Excel Spreadsheet is generated.. Which would
mean that I would have to create a new linked table for ever test that is run.
..... Plus I would also like to be able to automate the process with mayeb a
Command button that would just run the process from start to finish ...

 

[John Nurick]

If the Excel worksheets always have the same name, column headings etc.
you can use one linked table. Just put each successive new workbook in
the same location with the same name, and the linked table will get the
data from the latest version.

Alternatively, you can use a SQL append query that gets its data
directly from a workbook, using syntax like this:

INSERT INTO MyTable
SELECT FieldOne, FieldTwo, FieldThree
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet1$]
;

This assumes that the column headings in Excel match the Access
fieldnames. If not, you can alias them

SELECT XL1 AS Acc1, XL2 AS Acc2...

or if there are no column headings specify HDR=No and use

SELECT F1 AS Acc1, F2 AS Acc2...

So you can simply write VBA code that builds the SQL statement,
incorporating the name and location of the file you want, and then use
CurrentDB.Execute to append the records.