Grabbing data from Excel Order Sheets into Access

[ToddK]

I am considering improving an order process for supplies that our reps currently use.

Right now, they get an Excel worksheet from a Public Folder in our corporate MS-Outlook system, and fill it in to order their "selling supplies".

Problem is, we have no way of keeping track of who is ordering what and how much - even though the system is quite functional for filling orders, there is no history maintained.

I would welcome any general comments on how easy it might be to grab the data from these Excel worksheets and bring them into an Access database...so we can track what people are ordering, etc. I would assume I create a master customer file, and that then import the data - but is there a way to make it easy, or even done in batches?

The Excel sheet is not a simple thing - part numbers and descriptions are in rows, right now the rep enters quantities desired in final cell of rows with description.

Again, any high-level observations about the complexity of the task, or easier approaches, would be welcomed!

 

[Parra]

Why not eliminate Excel altogether and create a form in Access. The database can be put on the network and have the salesrep enter the data directly.

That way you won't have to import anything.

At least that is what I would do. A form can also do the calculations a spreadsheet does.

But it will take some time to set up the form depending how complex it is.

Parra

 

[ToddK]

An interesting idea, but I haven't the foggiest how I would even begin - I know squat about the architecture of our network, etc.

Does anyone have a solution for me per my original question - Excel worksheets needing to be "parsed" to get data into Access???

 

[Parra]

OK ToddK

What I would do is first save the worksheet (always keep the original), then I would do a save as and save it as a comma delimited text file. Then import the file to the database.

This is the basic idea, I did not want to describe more unless you are interested. This has worked for me in the past.

Parra

 

[ToddK]

Parra - I appreciate your answer, but it sure seems like an onerous way of doing it...surely, there must be an easier way to do this (i.e. import Excel files into an Access databse) than CSV and importing fields?

 

[Parra]

The only reason I recommend a text format, is because when I have imported data from an excel spreadsheet, depending on the links and formulas it can take a while to import. A text file imports more quickly.

But it is up to you. So I guess to answer your question, Yes, you can import excel sheets to access.

 

[Parra]

What I would do is add a sheet to the workbook where the salespeople input the data.

This sheet would be linked to the Input Form sheet. But the data would be next to each other, so Cell reference's A1, B1, c1, d1 etc. That way the data could easily be imported to access from this sheet.

I hope I made sense. It would be hard to import data from the form sheet because the data would be ontop of each other. If it is next to each other it is a nicer format.