Export Excel Sheets into PDF File
In general, all contents that we prepare in MS Word is converted to PDF before we share the sheet with anybody. Similarly, how do you export Excel worksheet into PDF document? Saving the excel worksheet data into PDF isn’t that easy as we do with MS Word. It has some limitations. In this article, we will show you how to change/save/convert the excel sheet as a PDF file in detail using VBA Code.
How to Change Excel File into PDF?
Below are the examples to export excel file into PDF.
Export Excel to PDF – Example #1
Save Spreadsheet as PDF File!
For an example look at the below data or chart which shows the hierarchy of the department in an organization.
We can just choose Save As from the File option and choose the Save as type as “PDF”.
This would still the file as PDF but look at how it looks when you open it.
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.
Under Settings choose “Fit Sheet on One Page”.
Now click on Save As in the same window and choose PDF as the file type.
This will fit the entire data into a single PDF page.
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 code to convert excel sheet as PDF.
Export Excel to PDF -using VBA Code – Example #2
Steps to convert an excel sheet to PDF using VBA Code.
Step 1: Declare variables in VBA as shown below.
Step 2: Now set Workbook and Worksheet reference to variables “Ws” & “Wb”.
Step 3: To give the file a name we need to record current time i.e. time at the time of running the code. Set the time to the variable “SaveTime”.
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”.
Step 5: Now we need to combine file name and time to create a unique file name.
Step 6: We need to give the option of choosing the destination path to save the file.
Step 7: Now we need to create a PDF file.
In case if any error comes we need to handle this as well. So below code will do the same.
Below is the full code for you.
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.
You can see that your excel file is now converted into PDF.
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.
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 –