Access and Excel quandry

[BobXFett]

Hi all. This will be my first post on these forums.
I've searched and browsed through a number of really detailed support threads, most of which were far over my head, and I haven't been able to find a solution that I can apply to the specific issue I am facing.

Here is the situation I am in. If anybody can help, it will be most appreciated.

I am working with an external client who currently uses Microsoft Excel (2000) to manage a series of spreadsheets for their own customer accounts. They have a few hundred of these documents where they track the specific items a customer has purchased, and the most current prices of these items. The spreadsheets are printed and sent out to the customer on a monthly basis so they have a record of the most recent prices for each item.

Currently, this client spends many hours at the end of each month manually going through all these hundreds of Excel spreadsheets and updating the items with the increased pricing. There is a lot of redundant clerical work involved, and that's where I come in.

I have been asked to provide an automated solution to help cut down on the amount of work needed every month to update these existing spreadsheets. The criteria for updating the spreadsheets is pretty simple, and I have the logic of the updates all planned out in my head. However, the actual implementation of this is beyond me.

Here's what I need to do:

1) Take two sources of input data; one is the spreadsheet to be updated, the second is another spreadsheet with a master list of all the items and their price increases for the current month.
2) If an item on the first spreadsheet exists on the second sheet, then it has a price increase and the first spreadsheet needs to be updated based on the increase listed in the second sheet.
3) After repeating this for all the items listed on the first sheet, the sheet should be saved (either as the same file or a new file derived from the original file name) and its formatting preserved.
4) This should be repeated in an automated fashion for all the customer spreadsheets.

My first thought was to program something in C++, but that was beyond my experience.
My second thought was that Microsoft Access might be able to perform this work for me, and I am pretty sure that it can. Here is how I am envisioning the process:

1) The original spreadsheets are imported to temporary transactional tables in the Access database.
2) The necessary comparisons and updates are made within Access.
3) An updated Excel spreadsheet is exported from Access with either the same file name as the original sheet, or a new file name derived from it.
4) Automated rinse and repeat through all the source sheets.

The three problems I'm running into in designing this solution are
1) Access strips off the formatting of the Excel spreadsheet when I import/export. I need the formatting of the original source sheet to be retained.
2) Access removes the image that is imbedded in the original Excel spreadsheet when import/exporting.
3) I have no idea how I would automate it so that the user can tell it to run and it automatically opens their 200+ source sheets and updates them.


If anybody can help shed some light onto this problem, I would be most appreciative.


Thanks in advance.

 

[MarkK]

Welcome to forum.
- Why use Excel at all? Access is a way better place to store data, and it's way easier to get at and process and report on and everything.
- I think the biggest headaches in what you are proposing is moving all your data back and forth all the time.
- 200+ source sheets!!! There's your trouble. That would give me nightmares.

 

[chergh]

Sounds like they are using excel as a database which is a bad idea. I'd agree with lagbolt and move them over to access and things will be easier.

If they MUST use excel then I would suggest beside each item in the master spreadsheet you have a hidden column which contains the path and name of the corresponding workbook. Then as long as the sheets are layed out conistently you can just use a piece of code that opens the workbook compares the prices and updates the price if it has changed.

They would still be better off if they moved over to access for this but it's quite possible to do what you want entirely within excel and then you don't have the formatting issues.

 

[DCrake]

Following on from the previous replies, if the customers are only using these spreadsheets for reading purposes then why not send them reports as opposed to spreadsheets?

Also what happens in the case where you have one product that is sold to different clients all of which you give them a different cost price. (Discount).

In Access you would need the following

Master product table

Master Client table

Junction table that joins the two using the primary keys from both tables

This is known as a many to many relationship as many customers can have many products and many products can have many customers.

Then if you add a price field in the junction table you can state the cost of each product to specific customers.

You would then create reports grouped by client to distribute via whatever method suits best.

All you need to do in the way of maintenance is to keep the junction table up to date and that's it.

One further consideration would be that if the cost of the product only varies for the odd few customers then leave the field in the junction table blank and let Access decide that if there is no value in the junction table then use the value in then master table.

Also if your prices are increased by a percentage each time then simply run an update query to increase the price by a certain percent.

David

 

[Rabbie]

Further to the advice already posted I would add that I had a similar situation where the end users do not have Access installed but do have Excel. I found it simple to transfer the output from a query to a new Excel spreadsheet which they could open. It just took a simple piece of VBA code to implement this.

 

[BobXFett]

All,

Thanks for the replies. I agree with each of you that migrating them to a full Access database system would be preferable, but unfortunately they work with remote sales people who have to have the data in an excel spreadsheet so they can make changes on-the-fly.
Based on the way their work flow is designed, there's no good way to centralize all their data into a single location (the remote sales people don't have any way to connect into the network, and they don't have access installed).

The solution I have to create must fit within the constraints of using excel sheets as a feed-in data source, and then outputting excel sheets with the same formatting.

 

[Rabbie]

Assuming the Excel spreadsheets are sent to you in electronic format there should be no problem in having them as linked access tables so you can process the data. I find the easiest way is to have a permanent Excel file that is linked into the Access DB and copy each input Spreadsheet to this location as it arrives so you can extract the data easily.

As said in my earlier post you can write an excel woorkbook to hold your output for sending back to the remote sites.

 

[DCrake]

If this is the only path you can take then what you need to due is...

Create a Personal.xls workbook in Excel. See help in Excel on creating this. Next make a folder on your pc/server that is dedicated to store the incoming/outgoing spreadsheets.

In your master spreadsheet that contains all the products and their respective prices you will need to create a macro.

You macro will do the following actions

Loop the folder containing the incoming spreadsheets creating a list of file names on a worksheet in your master workbook.

Once done it will go to the first one in the list and grab the name
Open up the named spreadsheet

Go to the first product item in the list and get the product code
toggle to the master worksheet and trawl through the products until it finds the corresponding code, when found, it gets the price and copies to the corresponding cell in the incoming spreadsheet.

It should perform this for all products in the incoming spreadsheet. once it reaches the end you need to save the spreadsheet and close the instance.

Then move to next spreadsheet in the list and repeat the above actions.

Thats the logic.

If you need further info let me know.
CodeMaster:

 

[BobXFett]

If this is the only path you can take then what you need to due is...

*snip*

If you need further info let me know.
CodeMaster:

CodeMaster,

Thanks for your guidance. I actually arrived at a very similar process myself through trial and error and realizing that using Access or C++ to put this together is needlessly complicated. Since I do need to keep my outputs as Excel sheets, using Access really isn't necessary.

I am in the process of learning the VBA code for putting this together, but I expect I will need more assistance as I go.
If and when that is the case, I expect I will reply here again with more details on what I need help on.

I greatly appreciate everyone's time and effort so far, and look forward to getting a working solution to this problem =)

 

[DCrake]

Thanks for your guidance. I actually arrived at a very similar process myself through trial and error and realizing that using Access or C++ to put this together is needlessly complicated. Since I do need to keep my outputs as Excel sheets, using Access really isn't necessary.

My earlier proposed solution did not involve Access at any point the fact that you would need to use VBA to automate it is essential. However I would like to see a solution without the use of VBA