link multiple excel files to 1 database

  • Thread starter Thread starter Lisa
  • Start date Start date
L

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?
 
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
'====================================================
 
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?
Click to expand...

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...
 
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.
 
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?
 
Back
Top