VBA excel: export all type of sheet into pdf

Hello experts,

I have the following procedure which allows me to export activesheet to a pdf.
Sub Pdf_ActiveSheet()
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        fileName:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

Open in new window


The procedure works for sheet however when I have a chart sheet it doesn't work.

Do you know what should I add to make it work for all of kind of sheet chart, table etc..?

Thank you in advance for your help.

I attached dummy file.
Export-Pdf.xlsm
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Isn't the issue that it is a Chart and not a Sheet, so you'd need to use Chart.ExportAsFixedFormat ?

https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.chart.exportasfixedformat?view=vsto-2017

Something more along the lines of
Sub Pdf_ActiveSheet()
10        On Error GoTo Error_Handler
          Dim wsA                   As Excel.Chart 'Worksheet
          Dim wbA                   As Excel.Workbook
          Dim strTime               As String
          Dim strName               As String
          Dim strPath               As String
          Dim strFile               As String
          Dim strPathFile           As String
          Dim myFile                As Variant

30        Set wbA = ActiveWorkbook
40        Set wsA = ActiveChart 'ActiveSheet
50        strTime = Format(Now(), "yyyymmdd\_hhmm")

          'get active workbook folder, if saved
60        strPath = wbA.Path
70        If strPath = "" Then
80            strPath = Application.DefaultFilePath
90        End If
100       strPath = strPath & "\"

          'replace spaces and periods in sheet name
110       strName = Replace(wsA.Name, " ", "")
120       strName = Replace(strName, ".", "_")

          'create default name for savng file
130       strFile = strName & "_" & strTime & ".pdf"
140       strPathFile = strPath & strFile

          'use can enter name and
          ' select folder for file
150       myFile = Application.GetSaveAsFilename _
                   (InitialFileName:=strPathFile, _
                    FileFilter:="PDF Files (*.pdf), *.pdf", _
                    Title:="Select Folder and FileName to save")

          'export to PDF if a folder was selected
160       If myFile <> "False" Then
170           wsA.ExportAsFixedFormat _
                      Type:=xlTypePDF, _
                      Filename:=myFile, _
                      Quality:=xlQualityStandard, _
                      IncludeDocProperties:=True, _
                      IgnorePrintAreas:=False, _
                      OpenAfterPublish:=False
              'confirmation message with file info
180           MsgBox "PDF file has been created: " _
                     & vbCrLf _
                     & myFile
190       End If

Error_Handler_Exit:
200       On Error Resume Next
          If Not wsA Is Nothing Then Set wsA = Nothing
          If Not wbA Is Nothing Then Set wbA = Nothing
210       Exit Sub

Error_Handler:
220       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: Pdf_ActiveSheet" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
230       Resume Error_Handler_Exit
End Sub

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The issue is, ActiveSheet can be either a Chart Sheet or a Worksheet and both are different sheet objects.
The solution is, declare wsA as object so that it can hold both the Chart Sheet and the Worksheet as well.

Sub Pdf_ActiveSheet()
Dim wsA As Object
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

Open in new window

Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Nice solution to make the code adaptive.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Thanks Daniel!
LD16Author Commented:
Thank you very much for this proposal. Unfortunately I test the procedure with two charts and it exports just one. Do you know how can we manage this case related to one sheets containing multiple  charts?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Unfortunately I test the procedure with two charts and it exports just one.

By two charts, do you mean two charts on the same sheet or two sheets with one chart each?

The existing procedure will export the ActiveSheet as pdf file not all the sheets? What's your requirement exactly?

Also, you will need to change the page layout of the sheet depending on the data/chart present on the sheet so that the data/chart appears correctly on the pdf file.
LD16Author Commented:
Hello Subodh,

1-Two charts in one sheet, The procedure should exports just active sheet.
I just defined the layout of my sheet and both charts are properly exported. Sorry for that. Do you think that it is straightforward to finish the procedure by opening folder location in which pdf file has been generated?
Thank you in advance for your help.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
One thing you can do is, check if the user hit the cancel button on SaveAs Window and inform that the pdf file was not saved.

Like this...
Sub Pdf_ActiveSheet()
Dim wsA As Object
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
Else
    MsgBox "You cancelled saving PDF File.", vbExclamation, "PDF File Not Saved!"
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
You are right, thank you very much for your help. however the idea is to open windows folder in order to get quickly the file location and send it if necessary. Do you think it we can add the condition to open folder if the file has been properly exported?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To open the folder where the pdf file was saved successfully, just add the following line after the line#50.
Shell "C:\WINDOWS\explorer.exe """ & strPath & "", vbNormalFocus

Open in new window

LD16Author Commented:
Noted, Thank you very much for your help!!!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.