The not. excel db? (1 Viewer)

I'm having quite a dilemma with a project for our Business Dev. folks.
They have this old method of using an excel spreadsheet and calling it a
database when it really isn't. Now these excel files are filled with macros
and so on. There is a ton of math involved and yada yada yada. Real ugly.
Anyhow they have asked that I develop a database application to help them
with their proposals and estimating to our various customers. The problem
is we're a German company based here in the US while our parent company
resides in Bremmen, Germany. Now in Bremmen we must adhere to their "Standards".
They were the ones who created these huge yet ugly excel spreadsheets...and the
business development team HAS to use them to remain consistant with Germany.
(I hate my job..btw...thought I'd just add that in ) Now...I have to create
this database BUT I have to make it work with this excel spreadsheet. After talking
to my boss the first time about how bad this really is...he said see if you can mimic
the functionality of the current spreadsheet into a database. After a few days of getting
absolutely no where..I mean this spreadsheet is literally math 101..but there is just way
too much of it along with conversion of euro dollars and us dollars and so on.
So I was getting no where...so I dont know where to go from here...I mean its kind of like
they have thrown this whole darn thing on me with no resources or help. My knowledge
with the excel object is very limited...but i was wondering would it be possible to
kind of like integrate this spreadsheet into a database...meaning WITHOUT DDE (Dynamic Data
Exchange) be able to send data to excel. For instance let me give a scenerio,
A bd rep gets a customer wanting a proposal on an assembly line. Basically
our assembly line is based on transports, pallets, and stations. Now the
bd rep (our employee) sits on his PC and starts entering (using that excel spreadsheet
and all the macros) what he / she needs to purchase:

T004

The macro in the spreadsheet automatically looks up "Database T" and item 4 and returns
some say mechanical part:

T004 Mechanical Part 0.3 0.2 1 ...... 550Euro 499US
...

Now Database T is another excel spreadsheet which lists all components of type 'T' meaning
transport components. Anything the BD rep enters with a T is automatically checked up in "Database
T" which is an excel spreadsheet. Now originally I said I would try to duplicate
this functionality but it seems to be quite difficult. Besides they need
all these conversions in euro and they also need to run reports.

I have had various meetings with my boss and the bd reps...repeatedly telling them
we need to drop the spreadsheets from Bremmen..only to be STOPPED instantly..its as if
its a holy thing. I have no clue..what im going to do. I was hoping someone would
know if integrating this spreadsheet somehow would help. The actual "Database T".. and
all those I dont have a problem with I can simply load those up into access tables and
allow users to select from a combo box. The problem I have is the main excel file...
would I be able to send records the actual calculations and such from an access db to excel.
In many cases there are 30 components for database t, 30 components for database e...and so
on (total of 5 "Database" excel files and one main file which uses all the macros
to find these components).

Anyone shed some light on a frustrated developer.

Jon

Excuse me for sounding like an accountant (which I am!) but where is the business case supporting this project?

From what you say, the Excel Monster actually works, and keeps the US and German operations in sync. So if you convert all of this to Access, will it:
1) Save time
2) Prevent errors
3) Streamline another part of the operation?

There needs to be a measurable value for doing all of this? So will you:
1) Be able to run with fewer reps, so saving money
2) Win more business, so increasing income
3) Improve the working environment for the staff, so improving staff welfare?

You need to look at the contraints, too.
1) Will Bremen let you get away with this
2) If you produce this new system, is there time to introduce it and tran the staff without impacting on productivity?

When you have all these answers (and others I haven't spelled out) then you can start to decide if the nuts and bolts issues of setting up the database is worthwhile.

Yuck an accountant.

Yes we have considered a majority of the things you have mentioned. The current process involves way too many people (one person to quote, one person to estimate, one person to yada yada) the expected application will handle all of this. It will certainly speed up the business transactions we make to our customers. Definately will streamline the process. Now that I got some swell info on the Excel object model I might be able to proceed.

You said you have 5 "Databases" which correspond to 5 .xls Spreadsheets that you would like to continue using.
Does this mean that you will simply use Access as a "Front End" to these spreadsheets? Or is it the other way around, will Excel be your "Front End" and, thus, need to transfer data to be stored in Access?
If Access is your front end, then you could simply create a table w/ fields that correspond to "(Excel) Databases" and their corresponding paths.
Now you could populate a combo box with the list of "Databases" and open the correct .xls file which is related to whichever "(Excel) Database" has been chosen. This isn't the end to all of your problems by a long shot, but it will at least get you to the correct .xls.

Yes access of course iwll be front end for now (later scaled up to complete vb with SQL Server). But I kind of have 2 data warehouses or backends. One being the back end of the db and one being the actual excel file.

Jon