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!

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

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!

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
>

In trying to give you an answer the biggest problem is a lack of definition.

Your proposal of having a proforma workbook preformatted with graphs is a good one - something I use regularly. Although I am not sure why you would need one for each supplier. Logic suggests one generic ne for all 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 its entirity" this seems like a bad idea resulting from a lack of imagination. But it might be the right thing to do.

regards

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