Continually updated Excel Files (1 Viewer)

Hi, Thanks in advance for help any of you could provide!
To start off, I am fairly new to Access but have been working with Excel for years. I am also a mechanical engineer so problem solving is right up my alley!
The issue I have is that I want to create an Access database from multiple excel files so that I can use queries and create reports.

At our company we have multiple designers as well as the purchasing department. The designers will create an Excel file, for each NEW project, which contains two sheets. One sheet is for Purchased items and another sheet is for Manufactured items.
Each sheet will be fully formatted so all columns,data types, etc will match the Access tables. Also, all excel files can be saved in the same folder.
The data from these two sheets needs to be added to the access database.
I will then create a User Interface where the Purchasing department can perform queries and reports.
My major obstacle (from my limited knowledge of Access) is the fact that new jobs are created up to 5 or 6 times a month AND (to make things more difficult) existing excel files need to be periodically updated with new data .(existing data never changes, only new data is added) but that data also needs to be updated in the database.
So to sum up my questions:
1 - How would you approach this issue?
2 - How would you modify my existing process to simplify the solution?
I am very open to suggestions here.
The important part is that I need to make the process as easy as possible for the designers and purchasers. If not, they will never use the system or worse yet, screw it up! lol
Again, thanks is advance for any help! I know you are all very busy with your own projects so any answers are VERY appreciated!

1 - How would you approach this issue?

Click to expand...

Option 1: Move everything to Access. No more creating spreadsheets and data going hither and yon.

Option 2: Leave everything as is in Excel.

With your hybrid proposal you will creating more work for everyone and it will be rife with headaches. Move this entire system to Access or don't use it at all.

Thanks Plog,
I definitely thought about that but the reason that excel is used is because our design software exports excel files. And there is quite a bit of cleaning up and formatting that needs to be done before it can be added to the database.
Also, the designers use excel for other parts of the process (they actually create different sheets for the machine assembly process).
If there was an easy way to get around this problem, I would be glad to skip excel altogether, but I just don't see how that would be possible for our designers.
For now it seems I am stuck with trying to get data out of Excel into Access.

Also, I should add, that leaving everything in excel leaves us with the current problem where all the data is saved in so many different places/files that it creates major tracking issues for the managers, designers and purchasers. Without getting too in detail with our company process, we have had MANY issues lately that have cost us quite a bit of money.
Having all data included in a database would make it easier for all departments to track all items at any time.

Move it all Access.
Is there data they currently create in excel from the design software ? or is it just customer facing - like a proposal?

You have answered your own question really;

Without getting too in detail with our company process, we have had MANY issues lately that have cost us quite a bit of money.

Click to expand...

Your process needs changing.

Purely out of interest what is the design software that uses Excel as an output?

Minty,
Solidworks is the software the designers use. You can directly export the Bill of Materials from each sub-assembly into excel files.
They then create an compiled BOM excel file which includes a sheet for each Sub-assembly. This is then used during the assembly process.
I wanted to add two sheets to this BOM files that uses macros to separate and organizes the data into "purchased" and "manufactured" items. These sheets are what I wanted to drop into a database.

Now instead of linking the excel sheets, could it be possible for the designer to simply copy and paste the data as a whole (NOT line-by-line, which would take forever) into a Form that is accessed in the custom UI that we create?

Looks like SolidWorks has an API and a technical forum:

https://forum.solidworks.com/

Didn't look at it too hard, but I bet you can either use the API to extract your data to Access or you can establish some sort of database connection (ODBC, ADO, etc.) from Access. I'd poke around there.

Look like there is a few methods;
http://docs.driveworkspro.com/Topic/BOMDatabase

All help is very much appreciated!
The more I think about it, going directly from the design software into access worries me.
Excel was always a tool we used to double and triple check the designers work. Not all purchased items are included in the design assemblies (ie. cables, pneumatic fittings, bolts, screws, etc..)
The excel BOM was a great place to go through whole job and add any missing items, etc.. I would hate to lose that functionality. Honestly, I just don't trust that the data coming out of the design software is complete.

That being said. I am open to only using excel as a way to "clean" the data if you know what I mean. I don't necessarily need any continuous link to the database.
I just need a fairly simple way of getting the data in.

Once the data is in a database, the idea was that a Custom User Interface would be created to allow the users to perform queries and reports but searching job number, part numbers, etc..
Is it possible to add a Form in this user interface where to user would click a button, input the job number, then have a form pop up that would allow them to copy and paste the data from whatever source (excel in this case)?

I would import the data as is from the exported BOM into a staging table - then perform your sanity checks in access, then "commit" the order once correct.

This means you are only dealing with the data in one place, not multiple versions. Assuming that each BOM has a unique reference it would be easy to ensure it only gets uploaded once to the staging zone.

Importing the data to Access is a pretty simple task, particularly if the data is sensibly formatted, and can be automated to a large degree, by simply selecting the import file, or specifying that it is stored in a specific folder and importing it from there.