Multiple charts in one PDF file - Excel VBA

[pedrovrls]

Good afternoon everyone!

I need to export multiple charts from multiple sheets to just one PDF file, but until now I couldn't do that. I only can export PDFs separetedely. Does anyone has a code to do that?? I've searched a lot in google and forums, but I wasn't able to find anything that could be adapted to my specific case.

Thanks in advance!

Pedro

 

[bobsan42]

Maybe use ghostscript as an external cmd to merge the files.
There are too many options but i've done it a long time ago and unfortunately the command and code are sort of lost.

 

[DanteAmor]

Try this:

Sub test()
    Dim sh As Worksheet
    Dim wb1 As Workbook, wb2 As Workbook
    Dim obj As Shape, oName As String
    Dim exists As Boolean
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks.Add
    
    For Each sh In wb1.Sheets
        exists = False
        For Each obj In sh.Shapes
            If obj.Type = 3 Then
                obj.Copy
                oName = obj.Name
                If exists = False Then
                    wb2.Sheets.Add after:=wb2.Sheets(wb2.Sheets.Count)
                End If
                ActiveSheet.Paste
                ActiveSheet.Shapes(oName).Top = obj.Top
                ActiveSheet.Shapes(oName).Left = obj.Left
                
                exists = True
            End If
        Next
    Next
    wb2.Sheets(1).Delete
    wb2.SaveAs Filename:=wb1.Path & "\temp.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Set wb2 = Workbooks.Open(wb1.Path & "\temp.xlsx")
    wb2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wb1.Path & "\" & "charts.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, _
        OpenAfterPublish:=False
    wb2.Close False
    MsgBox "Done"
End Sub

 

[StaceyBNewbie]

I copied and pasted the code provided here but it looks like wb1 isn't being assigned in the "Set wb1 = ThisWorkbook" statement. When it gets to the "For Each sh In sb1.Sheets" it looks like it's referring to the newly created sheet and it errors out because there's nothing in the workbook. The specific error at this point is this:
"Run-time error '1004':

A workbook must contain at least one visible worksheet.

To hide, delete, or move the selected sheet(s), you must first insert a new sheet or unhide a sheet that is already hidden."