Multiple Spreadsheet Importing

[MugWump via AccessMonster.com]

Hiya Folks

So here's the deal:

I've been asked to create a database that can do some basic calculations (i.e.
standard production per hour per employee, etc). The problem is where the
data is stored that Access will need in order to do the calculation.

There's a directory, call it G:\Staff, which has a folder for each employee --
say Bob, Jenny, Sam, and Mike.

In G:\Staff\Bob there are folders for each month Jan, Feb, March, etc.

In G:\Staff\Bob\November there is a spreadsheet for each work week named "Bob
ProdTrac_09306_09311.xls" (the numbers change each week, and the spreadsheet
doesn't exist until that week starts)

And this occurs for each employee listed in G:\Staff

What they want is to be able to enter a date range and maybe some other work-
item specific criteria and hit a "calculate" button that will spit out the
results. Each spreadsheet has the same format/headers so once it's in Access
the rest will be a cakewalk -- but I'm not sure how to automate getting each
of the new spreadsheets loaded into a table...

is this even possible?

And thanks in advance for you help!
-J

 

[Roger Carlson]

Take a look at Ken Snell's page here:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

 

[Klatuu]

There are a couple of design issues that make it more difficult than it
needs to be. First, you don't use a consistent string that indicates the
dates. For example,
09306 may be easy to interpret it as June 30, 2009, but how about 09311?
Is it January 31, 2009 or is it March 30, 2009. So you need to change the
format to be six characters as yymmdd. That will make it easier to parse
out.

And which folder does the sheet go in when a work week crosses a month
boundry?

 

[MugWump via AccessMonster.com]

Sadly I have no control or say over how the spreadsheet portion works.
That's a completely different ball of wax. But the spreadsheet with
"09306_09311" isn't for September of 2006 -- it's for the week starting on
11/02/09 and ending on 11/06/09 -- I believe it has to do with a Julian Date
(but that's just a guess, I've never seen these spreadsheets before).

As for a week that crosses a month, it would get saved in the folder for
whatever month that Monday was on (so if Monday is 09/29/09 the entire week
would be in the September folder)