BobXFett
11-11-2008, 01:13 PM
Hi all. This will be my first post on these forums.
I've browsed around a bit and not found anything that provides a solution to my issue in the depth that I need, so I am hoping that somebody on here might be able to help.
My situation is as follows:
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, but that hasn't worked out either. Then it hit me that Excel has VBA support built right in, and there is probably some way to script a solution within the VBA editor in Excel. I believe that this is probably the best direction to take for this, but unfortunately I have zero experience with VBA and am not sure where to begin.
If anybody can help shed some light onto this problem, I would be most appreciative.
Thanks in advance.
I've browsed around a bit and not found anything that provides a solution to my issue in the depth that I need, so I am hoping that somebody on here might be able to help.
My situation is as follows:
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, but that hasn't worked out either. Then it hit me that Excel has VBA support built right in, and there is probably some way to script a solution within the VBA editor in Excel. I believe that this is probably the best direction to take for this, but unfortunately I have zero experience with VBA and am not sure where to begin.
If anybody can help shed some light onto this problem, I would be most appreciative.
Thanks in advance.