Hello Excellers and welcome to another #macromonday. Thanks for joining me again and let’s save some more time with a handy Excel VBA macro. Today we will explore how to save a user-selected range of cells as a pdf file.
This macro is truly a great one to have on hand. Need to save that chart or that data table as a pdf to print or issue to users?. This is the easiest way.
What Does The Macro Do?
This macro takes the range selected by the user and publishes it as a pdf document.
Prepare To Write Your Macro
Open the Visual Basic Editor. You can do this either by hitting ALT+F11 or hit the Developer Tab | Code Group | Visual Basic.
Insert a new Module to store your VBA code. You can choose to insert a module in the current workbook or in your Personal Macro Workbook(PMW). If you choose to save your code in your Personal Macro Work book then the code can be use any Excel workbook. If you insert the code into the current workbook then you will be restricted to using the Macro in that work book. In my example I want to reuse the code so it I have chosen to insert a a module into my PMW. From the insert menu select Module. Ensure you have selected Personal.xlb from the project explorer window.
More Information On The Personal Macro Workbook
How Does The Macro Work?
Before the user runs the Macro, they need to select the range of cells that they want to export and save as a .PDF file.
Excel uses the ExportAsFixedFormat method
The PDF file is then automatically opened.
That’ s it. Simple?. All you need to do now is test your macro.
Notes on the ExportAsFixedFormatMethod
This method has a number of parameters associated with it. In this exmaple macro we only used the Type and OpenAfterPublish parameters
type – this values specifies whether to save the workbook in PDF or XPS format.
- filename – this value determines the full filepath of the newly created pdf if omoiited the current folder is used
- quality – the value that specifies the quality of the exported file.
- includeDocProperties – flag as true to include document properties in the exported file; otherwise, false.
- ignorePrintAreas– set to true to ignore any print areas set when exporting; otherwise, false.
- from – The number of the page at which to start exporting. If this argument is omitted, exporting starts at the first page.
- to – The number of the last page to export. If this argument is omitted, exporting ends with the last page.
- openAfterPublish – true to display the file in the viewer immediately; otherwise, false.
- fixedFormatExtClassPtr – A pointer to an implementation of the IMsoDocExporter interface that enables the workbook to be saved in a different fixed format. For more information, see Extending the Office (2007) Fixed-Format Export Feature.
Do You Need Help With An Excel Problem?.
I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.