PDA

View Full Version : [SOLVED] Saving a chart to .pdf



krishnak
08-01-2010, 05:37 AM
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
08-01-2010, 05:48 AM
I want to add that I have Acrobat 9 Standard version on my computer.

Bob Phillips
08-01-2010, 07:02 AM
If you have the charts on separate sheets, you could just loop the sheets and use



ActiveSheet.ExportAsFixedFormat xlTypePDF, Filename, xlQualityStandard

Bob Phillips
08-01-2010, 07:04 AM
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
08-01-2010, 07:44 AM
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?

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

Bob Phillips
08-01-2010, 09:12 AM
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
08-01-2010, 11:31 AM
xld,

Thanks for the help.

krishnak
08-02-2010, 07:09 PM
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?
Set myShell = CreateObject("WScript.Shell")
myShell.Run Filename

Bob Phillips
08-03-2010, 12:33 AM
If I fully qualify everything, scripting works for me



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