Export Excel into PDF

How to Change or Export Excel Files into PDF?

Below are the examples of exporting excel files into PDF.

You can download this Export Excel to PDF Template here – Export Excel to PDF Template

Example #1

Save Spreadsheet as PDF File!

For example, look at the below data or chart, which shows the hierarchy of the department in an organization.

Export Excel into PDF Example 1.1

We can just choose Save As from the File option and choose the Save as type as “PDF.”

Export Excel into PDF Example 1.2

This would still the file as PDF but look, how it looks when you open it.

Export Excel into PDF Example 1.3

In PDF, it has come in two different sheets, which absolutely make no sense at all. If you are just using Save As and choosing the file type as PDF, this is what happens when the file or spreadsheet content is more than the fitting area of a single PDF sheet.

For this, what we need to do is select the content area that we are trying to save as a PDF file. Then press Ctrl + P to open below the print preview window.

Export Excel into PDF Example 1.4
Example 1.5

Under Settings, choose “Fit Sheet on One Page.”

Example 1.6

Now click on Save As in the same window and choose PDF as the file type.

Example 1.2

This will fit the entire data into a single PDF page.

Example 1.7

This is the common technique we all have used regularly.

However, what if a button can produce a PDF file of the excel spreadsheet??? Yes, this can be done by using VBA Coding; follow the below steps to create VBA codeSteps To Create VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more to convert the excel sheet as PDF.

Example #2 – Using VBA Code

Steps to convert an excel sheet to PDF using VBA Code.

Step 1: Declare variables in VBA, as shown below.

Export Excel into PDF using VBA Example 2.1

Step 2: Now set Workbook and Worksheet reference to variables “Ws” & “Wb.”

Export Excel into using VBA PDF Example 2.2

Step 3: To give the file a name, we need to record the current time, i.e., time at the time of running the code. Set the time to the variable “SaveTime.”

Export Excel into PDF using VBA Example 2.3

Step 4: Now, we need to save the PDF file to the exact location of the workbook that we are working on. Assign this value to the variable “Save Path.”

Export Excel into PDF using VBA Example 2.4

Step 5: Now, we need to combine the file names and time to create a unique file name.

Export Excel into PDF using VBA Example 2.5

Step 6: We need to give the option of choosing the destination path to save the file.

Export Excel into PDF using VBA Example 2.6

Step 7: Now, we need to create a PDF file.

Export Excel into PDF using VBA Example 2.7

In case of any error comes, we need to handle this as well. So below code will do the same.

Example 2.8

Below is the full code for you.

Code:

Sub Excel_To_PDF()

   'Declare Variables
   Dim Ws As Worksheet
   Dim Wb As Workbook
   Dim SaveTime As String
   Dim SaveName As String
   Dim SavePath As String
   Dim FileName As String
   Dim FullPath As String
   Dim SelectFolder As Variant

   'Set Variables
   On Error GoTo EH
   Set Wb = ActiveWorkbook
   Set Ws = ActiveSheet

   'Record Current Time
   SaveTime = Format(Now(), "yyyy mm dd  _ hhmm")

   'Record Current Workbook Folder Path Address
   SavePath = Wb.Path
   If SavePath = "" Then
       SavePath = Application.DefaultFilePath
   End If
   SavePath = SavePath & ""

   'Give File a Name
   SaveName = "PDF"
   FileName = SaveName & "_" & SaveTime & ".pdf"

   'Instruct Where to save
   FullPath = SavePath & FileName

   'Enable folder picker to choose where to save the file
   SelectFolder = Application.GetSaveAsFilename _
       (InitialFileName:=FullPath, _
           FileFilter:="PDF Files (*.pdf), *.pdf", _
           Title:="Select Folder and FileName to save")

   'Create PDF File
   If SelectFolder <> "False" Then
      Ws.ExportAsFixedFormat _
         Type:=xlTypePDF, _
         FileName:=SelectFolder, _
         Quality:=xlQualityStandard, _
         IncludeDocProperties:=True, _
         IgnorePrintAreas:=False, _
         OpenAfterPublish:=False
    End If

exitHandler:
       Exit Sub
EH:
       MsgBox "Not Able to create PDF file"
       Resume exitHandler
End Sub

Copy this code and paste in the module of the Visual Basic Editor to open visual basic editor press Alt + F11. When we run this code by pressing the F5 key, we get the following option.

Example 2.9

You can see that your excel file is now converted into PDF.

Example 2

Things to Remember

  • If the data is not fit enough for a single page, then PDF will push the extra content to the next sheet.
  • This code will create the entire worksheet content as a PDF file.
  • Once you copy the macro code, you need to save the workbook as Macro-Enabled Workbook.

Recommended Articles

This has been a guide to Export Excel into PDF. Here we discuss how to change/convert excel file into PDF using simple Excel & VBA examples and downloadable excel sheet. You can learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>