Saving a chart to .pdf

[krishnak]

Hi All,

I am working on creating charts for monthly metrics. I have to create over 300 charts and publish them in pdf files.

I automated the task of creating the charts to a large extent from the tables.

After the chart is created, I am selecting each chart manually and then "Save As" pdf file. As I have the option of publishing the file checked, it will show me the chart, I name it and close it. I am using Excel 2007.

I want to automate this function by including the appropriate code with the VBA code I already have for creating the chart.

I looked around the forum for a suitable solution but could not find one. Can someone please suggest the code to be added?
Thanks in advance

[krishnak]

I want to add that I have Acrobat 9 Standard version on my computer.

[Bob Phillips]

If you have the charts on separate sheets, you could just loop the sheets and use

[vba]

ActiveSheet.ExportAsFixedFormat xlTypePDF, Filename, xlQualityStandard
[/vba]

[Bob Phillips]

Actually, I just tried it, and if the chart is selected it just PDFs that even if there is other stuff on the sheet.

[krishnak]

xld,

Thank you very much for the quick help. I was looking at some other options in the Google, but your solution is the simplest and best - as always.

After saving the file, I want to view the chart in pdf. I combined the code and here it is.

The code works but I do not understand the Shell object. Is there any alternate code?

[vba]Private Sub btnSavePDF_Click()
Dim Filename As String, myShell As Object
Filename = Application.InputBox("Enter the pdf file name", Type:=2)
ActiveSheet.ChartObjects("RefChart").Activate
ActiveSheet.ExportAsFixedFormat xlTypePDF, Filename, xlQualityStandard
Set myShell = CreateObject("WScript.Shell")
myShell.Run Filename
End Sub[/vba]

[Bob Phillips]

krishnak,

You could probably just shell it directly, but I can't see any great advantages, other than avoiding the overhead of instantiating the script object.

If your charts are all named ina structured manner, you could loop the sheets and do it all in one pass.

[krishnak]

xld,

Thanks for the help.

[krishnak]

I would like to add that the part of the code below does not work on Excel 2007, but it works on Excel 2010.
So when I went back to my computer with Excel 2007, I can only save the file to .pdf, but cannot open it through the code. I tried to get the References for Acrobat, Distiller etc in the Tools - References box, but to no avail.
Any thoughts that will make it work on Excel 2007?
[VBA] Set myShell = CreateObject("WScript.Shell")
myShell.Run Filename
[/VBA]

[Bob Phillips]

If I fully qualify everything, scripting works for me

[vba]

Private Sub btnSavePDF_Click()
Dim Filename As String, myShell As Object
Filename = Application.InputBox("Enter the pdf file name", Type:=2)
Filename = "C:\" & Filename
ActiveSheet.ChartObjects("RefChart").Activate
ActiveSheet.ExportAsFixedFormat xlTypePDF, Filename, xlQualityStandard
Set myShell = CreateObject("WScript.Shell")
myShell.Run Filename & ".pdf"
End Sub
[/vba]