When distributing reports to your audience, consider providing a PDF rather than an Excel file. Three reasons why:
- Consistency and Formatting: PDFs maintain formatting across devices and platforms, ensuring your report looks the same for everyone.
- Integrity: PDFs are typically read-only, safeguarding the integrity of the document.
- Convenience: PDFs are widely accessible and can be easily viewed on mobile devices without requiring Excel.
On your journey to automate your analyses using VBA, there’s an easy way to save an Excel Sheet as PDF.
SaveSheetAsPDF
Sub SaveSheetAsPDF(SheetName As String, OutputPath As String, OutputName As String)
Sheets(SheetName).ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=OutputPath & Application.PathSeparator & OutputName & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub| Parameter | Description |
|---|---|
| SheetName | The name of the sheet (“tab”) in Excel you’d like to save as a PDF. Note: Make sure you complete Page Setup on the sheet you’re saving as PDF – just like if you were to print it. |
| OutputPath | This is the folder in which to save the PDF. Can be on your device or a network path. |
| OutputName | Name of the PDF. No need to include .pdf in the name. This overwrites a PDF with the same name in the OutputPath if it already exists. Hint: Consider using a timestamp suffix if you don’t want to overwrite an existing file. |