PDA

View Full Version : How to automatically create excel templates and send via email



gmackk
04-02-2012, 04:53 AM
Hello,

Everyday I download an excel file which I use as the basis for my days work- the data in this file is grouped under the following 5 column headings

Entity I Counterparty I Valuation I Balance I Call Amount

There are 3 Entities, and approximately 20 different Counterparties which are contained in this sourcesheet. Altogether there are approximately 40 rows of data in the sourcesheet. As a result, I have created 40 seperate worksheets named eg Entity 1 v Counterparty 1, Entity 2 v Counterparty 1 etc etc.

The information under the column headers

Valuation I Balance I Call Amount

is the only information which changes each day according to market movements.

Currently, I use the code IF('Source Sheet'!A1>1,'SourceSheet'!A1,0) to populate a particular cell in each of the 40 worksheets ( Cell E39) This formula searches for the data contained under the column "Call Amount" in the source sheet, and if the amount is 1,000,000 or more, it will populate this specific cell. If it is less than 1 then the cell will remain empty.

In cell A1, I have another rule created which will look at cell E39, and if it is contains an amount greater than 1, it will populate cell A1 with a specific email address =IF(E39>1,person@person.com,"")

I have a macro set up which will run through all of the worksheets- looking for an email address in cell A1, and it then creates a pdf of this worksheet and automatically sends this pdf document in an email to the recipient.

What I would like to move away from would be having to maintain the 40 different worksheets which are contained in this workbook.

As I only have 3 different entities, would it be possible to create 3 seperate templates, and to have a macro set up which would look at my source sheet, and if for example the call amount for Entity 1 Counterparty A is 1,000,000, then it would create Entity 1's template and populate it with the relevant information. Similarly if it was Entity 2 Counterparty D it would do the same.

Any thoughts on this would be greatly appreciated

Regards
Gavin McCarthy

Bob Phillips
04-02-2012, 05:06 AM
Why have 3, why not just 1?

Use the code to iterate the data for the 3 entity x 20 counterparty combinations and see whether that particular combination has data that matches. If so, populate your template, save it as a PDF and send that. Then process the next.