Creating a PDF From a Chart

[Guzzlr]

Hello All,
I'm trying something I've never ever attempted, and don't know where to start. Creating a PDF from a Chart.

I have an excel sheet, with 8 tabs
Each tab has data that is used to make a simple line chart
I want to have code, that takes each tab, one-by-one, and creates a chart in PDF of the chart on the tab.
Then, place the PDF in the same folder the excel sheet is in.
I know it can be done, because I've seen it.
The example I saw, has a typical form control button. when the button is pressed, all PDF's in the folder are deleted, and then replaced by the macro.
Thanks for the help

 

[DanteAmor]

Each PDF file is named with the name of the sheet

Sub Creating_PDF_Chart()
'
' Creating a PDF From a Chart
'
    Dim ruta As String
    Dim h As Worksheet
    Dim ChartObj As ChartObject
    '
    ruta = ThisWorkbook.Path & "\"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each h In Sheets
        h.Select
        For Each ChartObj In h.ChartObjects
            hoja = h.Name
            ChartObj.Activate
            ActiveChart.ChartArea.Copy
            Set l2 = Workbooks.Add
            ActiveSheet.Paste
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=hoja & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
            l2.Close False
            Exit For
        Next
    Next
    MsgBox "End"
End Sub

 

[Guzzlr]

Each PDF file is named with the name of the sheet

Sub Creating_PDF_Chart()
'
' Creating a PDF From a Chart
'
    Dim ruta As String
    Dim h As Worksheet
    Dim ChartObj As ChartObject
    '
    ruta = ThisWorkbook.Path & "\"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each h In Sheets
        h.Select
        For Each ChartObj In h.ChartObjects
            hoja = h.Name
            ChartObj.Activate
            ActiveChart.ChartArea.Copy
            Set l2 = Workbooks.Add
            ActiveSheet.Paste
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=hoja & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
            l2.Close False
            Exit For
        Next
    Next
    MsgBox "End"
End Sub

Thanks for the response. I havent tried this yet, but I do have a couple of questions:
1. Why "Set 12"? What is the 12 for? Could this be another number or text name?
2. When the code copy's and paste, how do we adjust the size of the chart?
Thanks for the help

 

[DanteAmor]

Set l2 (is letter l an number 2), It is only a variable name, it can be any name and it must have letters and numbers.



First test and review the generated pdf files.

 

[Guzzlr]

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=hoja & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

I made a simple chart and ran the code. A couple of things, It will fault if I try Option Explicit as the variable for "h.name" is not defined. If I comment out Option Explicit, it gets to the code above and faults with a run time error: "Document not Saved"
It never makes it to the Export PDF area.
Thanks for the help

 

[Kenneth Hobson]

Some of your terminology is off. Tab can be a Worksheet or a Sheet.

1. Either Dim the variables missing or comment out Option Explicit as you did.
2. I don't see why the code would error. I would have explicitly set the full path in hoja.
3. You did not say that you wanted chart dimensions changed in post #1 .

Here is slightly modified #2 code that I used to test.

Sub Creating_PDF_Chart()
  Dim ruta As String, hoja As String
  Dim h As Worksheet, L2 As Workbook
  Dim ChartObj As ChartObject

  ruta = ThisWorkbook.Path & "\"
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  For Each h In Sheets
    h.Select
    For Each ChartObj In h.ChartObjects
      hoja = ruta & h.Name
      ChartObj.Activate
      ActiveChart.ChartArea.Copy
      Set L2 = Workbooks.Add
      ActiveSheet.Paste
      ActiveSheet.ChartObjects(1).Width = 375
      ActiveSheet.ChartObjects(1).Height = 225
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=hoja & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
      L2.Close False
    Next
  Next
  MsgBox "End"
  
   Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

 

[Guzzlr]

OK. I made some changes and got it to work!
I changed hojo to the excel tab name, and I set open after publish as true.

Sub Creating_PDF_Chart()'
' Creating a PDF From a Chart
'
    Dim ruta As String
    Dim h As Worksheet
    Dim ChartObj As ChartObject
    '
    ruta = ThisWorkbook.Path & "\"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each h In Sheets
        h.Select
        For Each ChartObj In h.ChartObjects
            ABC = h.Name
            ChartObj.Activate
            ActiveChart.ChartArea.Copy
            Set l2 = Workbooks.Add
            ActiveSheet.Paste
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=ABC & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
            l2.Close False
            Exit For
        Next
    Next
    MsgBox "End"
End Sub

A couple of things.
1. After the PDF is published, and showing, it will not save in the same folder the excel file is in automatically. How can I get it to automatically save itself in the same folder?
Also, Is there a way to have it up at 100% when it is published, instead of page width?
Thanks for the help

 

[DanteAmor]

I made a simple chart and ran the code. A couple of things, It will fault if I try Option Explicit as the variable for "h.name" is not defined. If I comment out Option Explicit, it gets to the code above and faults with a run time error: "Document not Saved"
It never makes it to the Export PDF area.
Thanks for the help

The variable h is defined.

[COLOR=#333333]Dim h As Worksheet[/COLOR]

By the way, it is not necessary to use "option explicit"





Only a couple of definitions would be missing. But the objective of the code is not the definition of variables, it is the creation of pdf, do not forget it.

  Dim ruta As String, [COLOR=#0000ff]hoja As String[/COLOR]
  Dim h As Worksheet,[COLOR=#0000ff] L2 As Workbook[/COLOR]
  Dim ChartObj As ChartObject

---

To save in the same folder:

Sub Creating_PDF_Chart()
'
' Creating a PDF From a Chart
'
    Dim ruta As String, hoja as string
    Dim h As Worksheet, l2 as workbook
    Dim ChartObj As ChartObject
    '
    ruta = ThisWorkbook.Path & "\"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each h In Sheets
        h.Select
        For Each ChartObj In h.ChartObjects
            hoja = h.Name
            ChartObj.Activate
            ActiveChart.ChartArea.Copy
            Set l2 = Workbooks.Add
            ActiveSheet.Paste
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
               [COLOR=#0000ff] Filename:=ruta[/COLOR] & hoja & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
            l2.Close False
            Exit For
        Next
    Next
    MsgBox "End"
End Sub

 

[Guzzlr]

So, the code is working now, and PDF's are exporting to the folder.
Thank you

 

[DanteAmor]

Youre welcome.