Importing Data - Macro or Script

[Norgbort Machine]

Hello,

I have a series of excel documents being sent to me over a period of
time, and I would like to import this information into one sheet
automatically. The documents being sent to me are all identically
designed, so importing is no problem.

Here is an example of what I am receiving:

ALPHA 1 0 0 1 1

BETA 2 1 0 1 0

OMEGA 0 0 1 0 2

Each line represents a single excel doc I get back. ALPHA, BETA, etc
are the names of the people who sent it in. The filenames are always
ALPHA_01_01_2006.xls or BETA_02_04_2006.xls, etc.

What I would like to do is create a MASTER sheet that contains a macro
/ script to look in my storage folder, identify that a new file has
been placed there, and then import that one line into my MASTER
document in the next available location.

Ideally, it would take the name from the beginning of the file and
fill that into the first cell, and then copy/import the rest of the
data into the appropriate cells. Even better would be to auto-sort it
by name, but I can just sort them manually if necessary.

My difficulty is that Im not sure how to have excel parse the folder
for filenames that have not been covered yet. So if my folder
contains the ALPHA, BETA, and OMEGA files already (since I've already
imported them), and I receive the DELTA file, I would like to just
throw it into the storage folder with the rest, hit a button, and have
it automatically fill in the:

DELTA 2 2 0 2 0

in the next available row on the MASTER sheet.

Obviously, if it was only 3 or 4 documents I was dealing with I would
do them by hand, but I receive dozens every day.

Any assistance would be greatly appreciated.

Thank you

 

[renegan]

Here is an idea which might work with some work on it:

You can create a tracking table on a separate worksheet on your master
workbook and write a macro to save the name of the file you opened on
that table. Assuming the names of the people who send you the files
stay same, create another table on that worksheet with names. Then you
can write a macro which checks the files with names starting with
people names on your name table, and check if the file name exist on
your tracking table.