Quite a complicated problem

[DaBeef]

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!

 

[tony h]

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

 

[RB Smissaert]

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]

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]

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

 

[DaBeef]

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