Automatically produce Multiple PDF files from a single chart

[Guest]

Hi,

I have a single chart in excel which is indexed to a table so that I can
select via a combo box which range of data I require to chart.

Currently I can print out every variation of the chart via a simple macro,
by looping to change the index row value.

My question is, I need to produce these charts in PDF format. But when I
print to Acrobat Writer is uses the excel filename as the PDF filename, how
can use the index value as the PDF Filename.

Thus allowing me to add this code to my existing macro to allow me to print
every variation of the chart to a single directory without having to rename
the pdf files as they are printed.

By the way I have 660 different index values / charts to produce on a weekly
basis.

Thanks
Stuart

 

[Guest]

After each file is created by printing it, add code like this:
'Thepath is where the path to copy the file to.
'ToFile is the name of the spreadsheet
'ext would be .pdf
'Fname would be the name the pdf program creates the file
'the kill statement removes the old file name before you print the next one
in case adobe makes you reply yes to overwrite it.

Dim Fname as string
Dim ThePath as String
Dim ToFile as String
Dim Ext as String

'place this in a loop of 660 to go through all your scenarios.

'set the chart
'print the file..... then:
FileCopy FName, ThePath & Trim(ToFile) & Ext
Kill FName
'then loop back through