Excel VBA Programming For Dummies. The model new model of this non-threatening info is your key to getting there. Using step-by-step instruction and the accessible, nice For Dummies sort, this smart book reveals you ways you should use VBA, write macros, customise your Excel apps to look and work one of the simplest ways you want. A excel-vba eBooks created from contributions of Stack Overflow users. Learning excel-vba eBook (PDF) Download this eBook for free Chapters. Chapter 1: Getting started with excel-vba. Chapter 18: Loop through all Sheets in Active Workbook; Chapter 19: Merged Cells / Ranges; Chapter 20: Methods for Finding the Last Used Row or Column in a.
In Excel 2010 and later, you can export a sheet, or a group of sheets, as a PDF file. This tutorial shows sample code for doing this.
Export Active Sheet as PDF File
The following macro code will export the active sheet (or sheets) in PDF format. Copy the code to a regular code module, then select the sheet(s) you want to export, and run the macro.
See the section further down, for details on how the macro works.
NOTE: There are two additional macros below:
- Create PDF file with name from worksheet cells - automatically overwrites existing file, if any
- Create PDF file with name from worksheet cells - check for existing file, prompt to overwrite or choose different name
The Export As PDF Code
Copy the code to a regular code module, then select the sheet(s) you want to export, and run the macro.
How The Macro Works
Before you run the macro, select the sheet(s) that you want to export to the PDF file.
![Excel Vba Book Pdf Excel Vba Book Pdf](https://smtebooks.com/Images/an-introduction-excel-vba-programming-pdf.jpg)
When the macro starts, it sets variables for the active sheet, and the active workbook. Those will be used to set the default file name and folder.
Excel Vba Book Pdf File
A time stamp will be added to the default name, in the format yyyymmdd_hhmm.
In the format string shown below, a backslash is entered before the underscore, to indicate it is a literal character. Otherwise, Excel would interpret the underscore as the spacing character that is used in Excel number formatting.
Next, the macro gets the default path for saving the PDF file. If the active workbook has been saved, its path is used. If the active workbook has not been saved, Excel's default save folder is used.
The name of the active sheet is cleaned up -- spaces are removed, and periods are replaced with underscores.
The file path, revised sheet name, and the '.pdf' extension are combined.
The Save As dialog box opens, with the current folder selected, or the default save folder. The folder is filtered, to show only the PDF files that it contains.
At the top of the Save As window, the customized title is shown, 'Select Folder and FileName to save'
The default file name is filled in, and you can overwrite it, to save the file with a different name. You can also select another folder --just browse to a different location.
Then, click the Save button, or click Cancel, if you change your mind.
![Excel Vba Book Pdf Excel Vba Book Pdf](/uploads/1/3/3/9/133915503/967207073.jpg)
Excel Vba Programming For Dummies Pdf
- If you click Cancel, the value of myFile is 'False', and nothing more happens -- the macro ends.
- If you click Save, the PDF file is created.
Then, if the file was created, the macro shows a confirmation message with the file path and name.
Click the OK button to close the message box.
Macro 2 - No Prompt
The previous macro creates a default name with a time stamp, based on the active sheet name. It prompts you to select a folder for the saved PDF file, and you can change the default name, if you prefer something different.
In the macro below, the default name is based on the values in cells A1, A2 and A3 on the active sheet. The PDF file is automatically saved in the current folder -- you are not prompted to choose a folder, and cannot change the default name.
Macro 3 - No Prompt - File Check
In the macro below, the default name is based on the values in cells A1, A2 and A3 on the active sheet. The PDF file is automatically saved in the current folder, with no prompts.
However, if a file with that name already exists in the current folder, a message asks if you want to overwrite the file. Click Yes or No in the message box.
- Yes - the new file overwrites the old file
- No - you are prompted to choose a folder, and/or enter a different file name.
NOTE: Be sure to copy the bFileExists Function too, below the main macro
Book Pdf Download
Download the Sample File
To see how the macro works, you can download the Export Excel Sheet as PDF sample file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros, if you want to run the macro.