Generate Multiple PDF Files using a cell value

[VinodKrishnappa]

Hi, I've situation like this.

In "Sheet1" I'll have data like below list.


[TABLE="class: cms_table, width: 527"]
<tbody>[TR]
[TD]Employee No
[/TD]
[TD]Name[/TD]
[TD]Category[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]12236[/TD]
[TD]ABCD[/TD]
[TD]Telephone[/TD]
[TD]$2100[/TD]
[/TR]
[TR]
[TD]12243[/TD]
[TD]EFGH[/TD]
[TD]Medical[/TD]
[TD]$4200[/TD]
[/TR]
[TR]
[TD]12256[/TD]
[TD]IJKL[/TD]
[TD]Telephone[/TD]
[TD]$1566[/TD]
[/TR]
</tbody>[/TABLE]



The above data should come in the following format in different .pdf file for each employee based on empployee no. wise.

[TABLE="class: cms_table, width: 221"]
<tbody>[TR]
[TD]Employee No.[/TD]
[TD]12243[/TD]
[/TR]
[TR]
[TD]Employee Name[/TD]
[TD]EFGH[/TD]
[/TR]
[TR]
[TD]Medical[/TD]
[TD]$4,200[/TD]
[/TR]
[TR]
[TD]Telephone[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]LTA[/TD]
[TD]%0[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]$4,200[/TD]
[/TR]
</tbody>[/TABLE]

Amount in Words Four Thousand Two Hundred


The data will be for more than 400 emp. i need to provide individual pdf to individual employee. Please help on this.

 

[hicksi]

It's doable. It just needs a few tricks. And I haven't time right now.

Are there FIXED categories? Ie, what piece of information provides the zero value for telephone and LTA above? Is there a row on Sheet1 with a value ZERO?
And if not, does every employee get to have a value reported?

What the question means is whether you need to de-normalise the data so there's one row per employee and one column per required item, or whether it's freeform.

 

[hicksi]

My method would be to create a new workBOOK, format the cells in that as you would like them to be presented in the PDF (including setting the print area)

Then run through your data top-to-bottom. At each employee:

Clear the data (not the formatting) with '.ClearContents'
Populate the data
Save-As PDF using something you know about the individual employee (maybe even using Employee-number and month to create the desired filename)​

Next