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.
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.
Last edited: