If you often find yourself converting Excel reports to PDF format and manually typing in the current date to keep your file versions in order, you will find this trick pretty useful. We will use VBA (Visual Basic for Applications) to automate this task, saving you time and reducing the risk of errors.
#1: Prepare your Excel workbook
First off, locate the Excel Spreadsheet that you would like to export to PDF. Then make sure that your development tab is enabled, so that you can access the VBA editor.
#2: Add your VBA code
Next, we will add a new VBA module that will include our automation code:
- From your Ribbon, hit the Developer button.
- Then hit ALT + F11 to open the VBA Editor (or press the Visual Basic button)
- Then under the VBAProject hit Insert and then choose Module.
- This adds a VBA module, where you can write your code.
- Paste the following code into your module:
Sub ExportPDFWithDate() Dim Sheet As Worksheet Dim FileName As String ' Replace "SheetName" with your actual sheet name Set MySheet = ThisWorkbook.Sheets("SheetName") ' Get today's date myDate = Format(Now(), "yyyy-mm-dd") ' Format the current date and create the file name FileName = ThisWorkbook.Path & "\" & MySheet.Name & "_" & myDate & ".pdf" ' Save the sheet as a PDF Sheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileName ' File successfully exported MsgBox FileName & " was saved." End Sub
#3: Run your Macro and check your results
- Next, Hit the Save button.
- Then hit ALT + F8 and select the macro named ExportPDFWithDate
- If you received the Success message, navigate to the folder that contains your spreadsheet to find your PDF file.
- From here you can either open your file, or attach it to an email.
#4: Saving multiple Excel sheets to PDF
An additional use case is to save multiple Excel worksheets as pdf files.
Wrap the following code in a VBA subroutine:
counter = 1 ' Get today's date myDate = Format(Now(), "yyyy-mm-dd") ' Loop through your file worksheets For Each ws In ThisWorkbook.Sheets ' Create the filename with the current date FileName = FolderPath & ws.Name & "_" & myDate & "_"&counter& ".pdf" counter = counter +1 ' Save each sheet as a PDF with the filename ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileName