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

 

[Guzzlr]

Getting back to this, I'm trying to get it to export Landscape, and aligning the chart object to be centered on the paper by using:

.Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(LM)
        .RightMargin = Application.InchesToPoints(RM)
        .TopMargin = Application.InchesToPoints(TM)
        .BottomMargin = Application.InchesToPoints(BM)

but I'm nopt having any luck, is it possible to incorporate these rules into the current code we discussed over the weekend?
thanks for the help

 

[Guzzlr]

I'm really having a difficult time getting this code to export as landscape, it keeps exporting to portrait.

 

[Kenneth Hobson]

What is your full code?

After the scratch workbook opens, set the ActiveSheet's PageSetup to suit. Of course your pasted chart should be resized too if you want it to fit. Be careful resetting height and width of the chart. That is if you want to maintain the same dimensional ratios.

 

[Guzzlr]

[COLOR=#333333]Sub Creating_PDF_Chart()[/COLOR]  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 [COLOR=#333333]End Sub[/COLOR]

I'm using yours and DanteAmor's code. I'm trying to insert ".Orientation = xlLandscape" as "ActiveSheet. Orientation=xlLanscape" with the other Active Sheet commands, along with setting up margins, before the export as fixed format. I have seen other code for pdf creation using the "ActiveSheet.PageSetup" method, where a multitude of commands can be created, but I'm not sure which is best, for trying to get the chart to export as Landscape, and having control of the margins.

Thanks for the help

 

[Kenneth Hobson]

Use the macro recorder if you don't know.

 

[DanteAmor]

Try with this:

Sub Creating_PDF_Chart()
'
' Creating a PDF From a Chart
'
    Dim ruta As String, hoja As String
    Dim h As Worksheet, h2 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
[COLOR=#0000ff]            Set h2 = Sheets.Add[/COLOR]
[COLOR=#0000ff]            h2.PageSetup.Orientation = xlLandscape[/COLOR]
[COLOR=#0000ff]            h2.PasteSpecial Format:="Picture (Enghanced Metafile)", Link:=False, DisplayAsIcon:=False[/COLOR]
            '
            h2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ruta & hoja & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            h2.Delete
            Exit For
        Next
    Next
    MsgBox "End"
End Sub

 

[Guzzlr]

the code is very close to working, thank you. The only problem is it is not on one page, it hangs over a small amount on the right margin to the second page. I tried right margin = .5, and I tried FitToPages, but it still goes over to the second page just a little. Here is the code I'm using:
Thanks for the help

Sub Creating_PDF_Chart()

'***************** Creating a PDF From a Chart **********************


    Dim target As String
    Dim IND3 As String
    Dim h As Worksheet
    Dim h2 As Worksheet
'    Dim L2 As Workbook
    Dim ChartObj As ChartObject
    
target = ThisWorkbook.Path & "\"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each h In Sheets
        h.Select
        For Each ChartObj In h.ChartObjects
            IND3 = h.Name
            ChartObj.Activate
            ActiveChart.ChartArea.Copy
            Set h2 = Sheets.Add
            h2.PageSetup.Orientation = xlLandscape
            h2.PageSetup.RightMargin = 0.5
            h2.PasteSpecial Format:="Picture (Enghanced Metafile)", Link:=False, DisplayAsIcon:=False
            h2.ExportAsFixedFormat Type:=xlTypePDF, _
            filename:=target & IND3 & ".pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
            h2.Delete
            Range("A1").Select
        Exit For
        Next
    Next
    
Sheets("IND3").Select
Range("A1").Select
MsgBox "PDF Charts Completed"
End Sub

 

[DanteAmor]

Updated macro

Sub Creating_PDF_Chart()
'
' Creating a PDF From a Chart
'
    Dim ruta As String, hoja As String
    Dim h As Worksheet, h2 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 h2 = Sheets.Add
            With h2.PageSetup
                .Orientation = xlLandscape
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            h2.PasteSpecial Format:="Picture (Enghanced Metafile)", Link:=False, DisplayAsIcon:=False
            '
            h2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ruta & hoja & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            h2.Delete
            Exit For
        Next
    Next
    MsgBox "End"
End Sub

 

[Guzzlr]

I used your code, but it still wants to export as 2 pages, with just a little of the chart on page 2. the code I have is below:
Thanks for the help

    Dim target As String    
Dim IND3 As String
Dim h As Worksheet
Dim h2 As Worksheet
Dim ChartObj As ChartObject
    
target = ThisWorkbook.Path & "\"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each h In Sheets
        h.Select
        For Each ChartObj In h.ChartObjects
            IND3 = h.Name
            ChartObj.Activate
            ActiveChart.ChartArea.Copy
            Set h2 = Sheets.Add
                With h2.PageSetup
                    .Orientation = xlLandscape
                    .FitToPagesWide = 1
                    .FitToPagesTall = 1
                End With
            h2.PasteSpecial Format:="Picture (Enghanced Metafile)", Link:=False, DisplayAsIcon:=False
            h2.ExportAsFixedFormat Type:=xlTypePDF, _
            filename:=target & IND3 & ".pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
            h2.Delete
            Range("A1").Select
        Exit For
        Next
    Next
    
Sheets("IND3").Select
Range("A1").Select
MsgBox "PDF Charts Completed"

 

[DanteAmor]

Sub Creating_PDF_Chart()
'
' Creating a PDF From a Chart
'
    Dim ruta As String, hoja As String
    Dim h As Worksheet, h2 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 h2 = Sheets.Add
            With h2.PageSetup
                .Orientation = xlLandscape
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            h2.PasteSpecial Format:="Picture (Enghanced Metafile)", Link:=False, DisplayAsIcon:=False
[COLOR=#0000ff]            With h2.PageSetup
                .Orientation = xlLandscape
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With[/COLOR]
            '
            h2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ruta & hoja & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            h2.Delete
            Exit For
        Next
    Next
    MsgBox "End"
End Sub