link multiple excel files to 1 database

[Lisa]

I have about 100 excel in 1 designated folder. All these files have the same
exact number of columns with the same heading in each column and multiple
rows of data. I would like to build a database that links to every one of
these files so I can run queries based on all these linked files.

What is the best way accomplish this?

 

[John Spencer]

If you want to work with all those Excel sheets at one time, you really
need to find a method to combine the data into one table.

I would create a table with the correct structure and append all the
data into the one table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Piet Linden]

I have about 100 excel in 1 designated folder.  All these files have the same
exact number of columns with the same heading in each column and multiple
rows of data.  I would like to build a database that links to every oneof
these files so I can run queries based on all these linked files.

What is the best way accomplish this?

Since Ken did most of this already, I would start with what he has on
his website and modify to suit...
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

Looks like the first link has what you want...

 

[Lisa]

The data in the folder where the 100 files are located can be constantly
updated. Meaning more files could be added to this folder; files can be
deleted; and files could have maintinance done to these files.

What i am trying to acommplish is to by look at all files in this folder;
then i would combine all the files into one table in access; possibly via a
UNION query. These files have to be linked instead of me appending the data
because the data changes in this folder from week to week.

 

[Douglas J. Steele]

There's a limit as to how many tables may be included in a UNION query.

 

[Lisa]

I have a question about your VBA coding for 'Import Data from All EXCEL Files
in a single folder'. Can these excel files be read only; can they be write
protected; can they be opened by another user while running this macro? If a
run this macro again and any of these excel files have been updated since i
last ran the macro will it remove data that has been removed and append new
data that has been added?