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]

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

I'm really sorry, but the modified code is still exporting with the chart a little on the second page

 

[DanteAmor]

Try reducing the size of your image and try the macro again until the image is on a single sheet.

 

[Kenneth Hobson]

I can't replicate that problem.

Normally, when I resize, I like to maintain the ratio. Otherwise, my chart won't look as I designed it. When I do that sort of thing, I like to set Portrait or Landscape based on proportion/ratio.

This is similar to what was posted earlier.

Sub PDFallChart1sToPDFandSizeToFit()
  Dim p$, ws As Worksheet, wb As Workbook

  p = ThisWorkbook.Path & "\"
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Application.EnableEvents = False
  
  Set wb = Workbooks.Add(xlWBATWorksheet)
  
  For Each ws In ThisWorkbook.Worksheets
    If ws.ChartObjects.Count > 0 Then
      wb.Worksheets.Add
      With ActiveSheet
        With .PageSetup
            .Orientation = xlLandscape
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            '.LeftMargin = Application.InchesToPoints(0.5)
            '.RightMargin = Application.InchesToPoints(10.5)
          End With
          ws.ChartObjects(1).Copy
          .Paste
          'Resize charts, https://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html
          '.ChartObjects(1).Width = Application.InchesToPoints(10.5)
          '.ChartObjects(1).Height = 235
          .ExportAsFixedFormat xlTypePDF, p & ws.ChartObjects(1).Parent.Name & ".pdf", _
            xlQualityStandard, False, True, , , False
      End With
    End If
  Next ws
  wb.Close False
  
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.DisplayAlerts = True
  
   Shell "explorer.exe " & p, vbNormalFocus
End Sub