Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quite a complicated problem
Hopefully someone can help me with this.
I've written a program in C++ which reads spreadsheets in csv / tab delim / slk format and performs reporting tasks on them as specifed through report maps. It has become quite a powerful tool, but I'm missing some functionality which my manager would like included. For the most recent map, I've had to process a list of over 200 suppliers and generate sales and purchase summaries, leaving me with about 5 spreadsheet files for each. My manager would like to have these reports run on a monthly basis, the sheets combined into one workbook for each supplier and have graphs on each sheet. I know this can be automated, but I have practically no knowledge of vba and haven't got a wide enough time scale to learn it. The problem could be solved as follows (see further down if the next two paragraphs seem over complicated): Keep a folder with a separate xls file for each supplier, already filled in with tables and graphs. This would mean that the script could just replace the values in the tables, and the graphs would be automatically updated. This would be perfect for all but one sheet. The final sheet would need to be replaced in it's entirety by another sheet. I need a way of automating this process so it can be run on a list of suppliers (which would match the file names of the files to be editted). It would need to increment through each value in the list, read the newly created file of that name (perhaps appended with a phrase like " Sales Summary", then overwrite the relevant cells in the master workbook's worksheets and replace the last full page. For clarity, my program could generate the following output (named as xls files but actually slk): 3M Purchases and Sales Summary.xls 3M Purchases by Trade Term.xls 3M Sales Summary.xls 3M Sales by Branch.xls 3M Top 50 Sales Products.xls The master file could be called 3M.xls with the following worksheets: Purchases and Sales Summary Purchases by Trade Term Sales Summary Sales by Branch Top 50 Sales Products Four of the sheets would need to have specific cells replaced from the relevant new files. These would be done through direct cell references which I will happily define in the script, so I imagine this would be fairly easy. For example, the script might say to take cell B3 from the source sheet and overwrite cell A3 in the master sheet. I could then simply duplicate this for all the cells that need to be replaced. The other sheet (Purchases by Trade Term) needs to be replaced by the worksheet contained in 3M Purchases by Trade Term. I would need the script to be able to either cycle through a list of file names, or perhaps through all the files in the master file folder and repeat this task for each, updating "3M" with each new supplier name on each pass. I know this is way beyond the scope of help and advice on a forum, but there seem to be several vba geniuses around here, so I'm hoping someone can help. If you can help me with any part of this problem, it would be much appreciated. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quite a complicated problem
I tend to agree with you it is "way beyond help and advice" and all though it is straightforward enough to do - after all the accumulated years of wisdom and heartache. It does make me wonder at what point should we stop giving our skills for free and say fair question but you are going to have to pay for it. I am sure I am not the only contributor whose primary focus in life is to sell their skills. I may shoot myself in the foot and look at it later. cheers -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=561994 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quite a complicated problem
I am sure it is all fairly simple, but it is better to break it down in the
smallest possible tasks you are having a problem with. If you are really pushed for time and know little about VBA you could put it up on RentACoder. RBS "DaBeef" <u24213@uwe wrote in message news:6360adadd643e@uwe... Hopefully someone can help me with this. I've written a program in C++ which reads spreadsheets in csv / tab delim / slk format and performs reporting tasks on them as specifed through report maps. It has become quite a powerful tool, but I'm missing some functionality which my manager would like included. For the most recent map, I've had to process a list of over 200 suppliers and generate sales and purchase summaries, leaving me with about 5 spreadsheet files for each. My manager would like to have these reports run on a monthly basis, the sheets combined into one workbook for each supplier and have graphs on each sheet. I know this can be automated, but I have practically no knowledge of vba and haven't got a wide enough time scale to learn it. The problem could be solved as follows (see further down if the next two paragraphs seem over complicated): Keep a folder with a separate xls file for each supplier, already filled in with tables and graphs. This would mean that the script could just replace the values in the tables, and the graphs would be automatically updated. This would be perfect for all but one sheet. The final sheet would need to be replaced in it's entirety by another sheet. I need a way of automating this process so it can be run on a list of suppliers (which would match the file names of the files to be editted). It would need to increment through each value in the list, read the newly created file of that name (perhaps appended with a phrase like " Sales Summary", then overwrite the relevant cells in the master workbook's worksheets and replace the last full page. For clarity, my program could generate the following output (named as xls files but actually slk): 3M Purchases and Sales Summary.xls 3M Purchases by Trade Term.xls 3M Sales Summary.xls 3M Sales by Branch.xls 3M Top 50 Sales Products.xls The master file could be called 3M.xls with the following worksheets: Purchases and Sales Summary Purchases by Trade Term Sales Summary Sales by Branch Top 50 Sales Products Four of the sheets would need to have specific cells replaced from the relevant new files. These would be done through direct cell references which I will happily define in the script, so I imagine this would be fairly easy. For example, the script might say to take cell B3 from the source sheet and overwrite cell A3 in the master sheet. I could then simply duplicate this for all the cells that need to be replaced. The other sheet (Purchases by Trade Term) needs to be replaced by the worksheet contained in 3M Purchases by Trade Term. I would need the script to be able to either cycle through a list of file names, or perhaps through all the files in the master file folder and repeat this task for each, updating "3M" with each new supplier name on each pass. I know this is way beyond the scope of help and advice on a forum, but there seem to be several vba geniuses around here, so I'm hoping someone can help. If you can help me with any part of this problem, it would be much appreciated. Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quite a complicated problem
You're quite right and I'm going to solve the problem myself. I've ordered a
book on vba and macros in excel and will have to learn that before finishing this project. Thanks for your response. Keith tony h wrote: I tend to agree with you it is "way beyond help and advice" and all though it is straightforward enough to do - after all the accumulated years of wisdom and heartache. It does make me wonder at what point should we stop giving our skills for free and say fair question but you are going to have to pay for it. I am sure I am not the only contributor whose primary focus in life is to sell their skills. I may shoot myself in the foot and look at it later. cheers |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quite a complicated problem
In trying to give you an answer the biggest problem is a lack o definition. Your proposal of having a proforma workbook preformatted with graphs i a good one - something I use regularly. Although I am not sure why yo would need one for each supplier. Logic suggests one generic ne for al suppliers and then customize it on the fly. so ... for each supplier copy proforma workbook to supplier_name.xls open suppliername.xls update supplier data update tables etc next The tables should be easy enough but might (depending on how you do it need the data ranges on the graphs changing. I don't understand why "the last sheet would need replacing in it entirity" this seems like a bad idea resulting from a lack o imagination. But it might be the right thing to do. regard -- tony ----------------------------------------------------------------------- tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107 View this thread: http://www.excelforum.com/showthread.php?threadid=56199 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quite a complicated problem
The reason for the last sheet needing to be replaced is that it doesn't
contain any graphs and the number and size of the tables can change each month, so it's much easier just to overwrite it. With the other pages, the tables would be standard and graphs would be required, so it is better to overwrite individual cells. The reason I need a separate master file for each supplier is that people want to be able to add information to the spreadsheet outside of the tables, such as rebate information and various other discounts. And this information needs to be kept intact throughout the year. If the table was regenerated from a single template, this information would be lost, besides which, as I'm forced to keep a separate book for each supplier, it's more sensible to just use each supplier's work book and overwrite the necessary cells. Thanks for your feedback, it's much appreciated. Keith tony h wrote: In trying to give you an answer the biggest problem is a lack o definition. Your proposal of having a proforma workbook preformatted with graphs i a good one - something I use regularly. Although I am not sure why yo would need one for each supplier. Logic suggests one generic ne for al suppliers and then customize it on the fly. so ... for each supplier copy proforma workbook to supplier_name.xls open suppliername.xls update supplier data update tables etc next The tables should be easy enough but might (depending on how you do it need the data ranges on the graphs changing. I don't understand why "the last sheet would need replacing in it entirity" this seems like a bad idea resulting from a lack o imagination. But it might be the right thing to do. regard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated Sorting Data Problem | Excel Discussion (Misc queries) | |||
complicated problem I think Excel can handle | Excel Discussion (Misc queries) | |||
Complicated Problem | Excel Worksheet Functions | |||
Complicated Problem! | Excel Worksheet Functions | |||
Complicated Vlookup/count problem | Excel Worksheet Functions |