ISBN ISBN Library of Congress Cataloging-in-Publication Data: Jelen, Bill. VBA and macros: Microsoft Excel All the Office applications allow users to create their own Visual Basic code to VBA controls Microsoft Excel by means of macros which are also called. Copyright © by John Wiley & Sons, Inc., Hoboken, New Jersey. Published by . Part I: Getting Started with Excel VBA Programming 9. Chapter Part II: How VBA Works with Excel. .. Creating complete, macro-driven applications.
|Language:||English, Spanish, French|
|Genre:||Business & Career|
|Distribution:||Free* [*Registration needed]|
Workshop Resources aracer.mobi~gz/aracer.mobi Lesson materials: Learning Slides[.pdf]. Exercises - Blank [.xlsx]. Exercises - Filled. In Excel, macro code is a programming code which is written in VBA (Visual Basic Don't Forget to download this free PDF checklist where I've added all these auto Start Name xls in XP Excel to vba code in Excel Windows. Excel macro saves active sheet or sheets in PDF format, prompts for file name and folder. See macro code Excel VBA - Save As PDF Files. In Excel and later, you can export a sheet, or a group of sheets, as a PDF file.
The folder is filtered, to show only the PDF files that it contains. 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, if the file was created, the macro shows a confirmation message with the file path and name. 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. 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. By default, the tab for developers is not displayed in excel.
You will need to display it via customize report Macros can be used to compromise your system by attackers. By default, they are disabled in excel. Always fill in the description of the macro when creating one.
This will help you and others to understand what the macro is doing. Step by step example of recording macros in Excel We will work with the scenario described in the importance of macros excel. We will work with the following CSV file. You can download the above file here We will create a macro enabled template that will import the above data and format it to meet our business reporting requirements.
Enable the developer option as shown below and pin it into your main ribbon in Excel. Create a folder in drive C named Bank Receipts Paste the receipts. In fact, in this blog post, I go much further than simply showing you how to simply convert Excel files to PDF. Among others, I also:. You can get immediate free access to these example files by subscribing to the Power Spreadsheets Newsletter. Therefore, let's start this blog post by taking a look at it:.
The main purpose of the ExportAsFixedFormat method is to export a particular Excel object such as a file, worksheet, cell range or chart to another file format , usually PDF. To be more precise, you'll generally be working with 1 of the following versions of this method , depending on which particular object you want to save as PDF. This particular VBA method has 9 different parameters.
Let's take a look at each of them:. You use the Type parameter to specify the type of file format to which the Excel file must be exported to. In other words, you determine in which file format the new resulting file is saved by specifying it in the Type parameter. In order to specify the type of file you want to convert the Excel file to, you use the xlFixedFormatType enumeration.
There are 2 types of file formats you can choose from:. Therefore, you're unlikely to encounter or have to work with many XPS files.
As implied by its name, you use the Filename parameter of the ExportAsFixedFormat method for purposes of specifying the filename of the new converted file. You have the option of choosing the quality of the resulting PDF file standard or minimum. More precisely, you can choose 1 of the 2 xlFixedFormatQuality options:. To make this choice, you simply need to set the IncludeDocProperties parameter to True or False as follows:.
By using the IgnorePrintAreas parameter of the ExportAsFixedFormat method, you can specify whether Visual Basic for Applications should ignore or not the print areas that have been set for the relevant Excel file. To specify whether the print areas should or shouldn't be ignored, set the IgnorePrintAreas parameter to True or False as follows:. More precisely:. You specify whether the file is displayed or not by setting the OpenAfterPublish parameter to True or False , as follows:.
For purposes of this example, I have prepared a sample Excel workbook. This workbook contains 2 worksheets. Each worksheet includes a table that lists persons and their contact details, along with i their food preferences, and ii their favorite animal and the name of their pet.
I generated all of the data in this Excel file using random-name-generator. Therefore, strictly speaking, you can create a very simple macro for purposes of saving an Excel file as a PDF. As I explain when introducing the Worksheet. ExportAsFixedFormat method above, the reference to the method must be preceded by a variable representing a Worksheet object.
ActiveSheet property is used for these purposes. More precisely, ActiveSheet return an object representing the active sheet within the active or specified workbook. The purpose of this method is to save the relevant object a worksheet returned by ActiveSheet in the example above as a PDF file. Type is the only required parameter of the ExportAsFixedFormat method. Its purpose is to specify the type of file format to which the relevant worksheet should be exported to.
You specify the relevant file type by choosing the appropriate value from the XLFixedFormatType enumeration. However, this doesn't mean that you should be using this macro to convert all of your Excel files to PDF. The reason for this is that, as I show below, the macro literally just saves the active worksheet to a PDF file.
Since it doesn't make any adjustment prior to the conversion, the results aren't necessarily the best. The following image shows the resulting PDF file:. However, there are several things that can be improved. The following are 2 examples:. I show you how to solve several of these issues in the other VBA code examples within this Excel tutorial. Let's start to tackle some of these problems by using the additional arguments of the ExportAsFixedFormat method:.
ExportAsFixedFormat method with its only required parameter Type. Therefore, in this second example, I include most of the parameters that you can use when working with the ExportAsFixedFormat method.
These first 2 lines are the whole body of that previous macro. In this particular case, they have the same purpose as that which I explain above. As I explain above, the previous sample macro simply used the name of the active Excel workbook to name the PDF file that was created after executing the ExportAsFixedFormat method.
By using the Filename parameter, you can choose a different filename. In this particular case, the filename that I'm using is quite simple and only includes the actual filename. Therefore, the PDF file is saved in the default file location. However, as I explain above, you can use this same parameter for purposes of determining the full path of the converted PDF file.
IncludeDocProperties can be set to either of the following 2 values:.
The purpose of the IgnorePrintAreas parameter of the ExportAsFixedFormat method is to d etermine whether any print areas that are set when publishing should be ignored or not. Had I omitted any of these parameters, the effect would have been as follows:. You can also set OpenAfterPublish to False. In this case, the published PDF file isn't displayed. However, notice some of the effects of the additional parameters that I included in this second macro:.
Overall, the explanations and examples in the sections above show most of what you need to know about the ExportAsFixedFormat VBA method. You may notice, however, that there are a few things that you can't fix with the ExportAsFixedFormat method alone. This includes, among others, one that I mention above:.
The page layout isn't properly adjusted. Therefore, the resulting PDF file only shows the first 4 columns of the table within the original Excel source file.
The last column which corresponds to Favorite Food Ingredient in the example above is missing. There are many ways in which you can use other VBA constructs to create more complex and sophisticated macros to save Excel files as PDF.
I show you some of the constructs you can use in the following sections, along with some further examples. Let's start by taking a look at how you can solve the issue I mention above: Within Excel, whenever you need to manually adjust the page setup before printing an Excel file or saving it as PDF, you go to the Page Setup dialog box. Whenever you're working with Visual Basic for Applications, you can use the Page Setup object for purposes of modifying any page setup attributes.
The reason for this is that, as explained at the Microsoft Dev Center in the webpage I link to above , the Page Setup object:. As a consequence of this, the list of properties that are members of the Page Setup object is quite extensive. Let's take a look at it:. By my count, the PageSetup object has the 48 properties that I introduce in the table below. The purpose of this table is simply to introduce these properties and their main purpose. However, I explain some of the properties you may want to explore when working with the ExportAsFixedFormat method below.
You can find this and more information about these properties at the Microsoft Dev Center. Note that, in my opinion, the information at the Microsoft Developer Network as of the time I'm writing this blog post has some contradictions. Anyway, this shouldn't stop you from working with the PageSetup object to improve your macros that convert Excel files to PDF.
This third example builds on those 2 examples in particular example 2 above to show how you can improve the results you obtain when carrying out an Excel to PDF conversion using VBA. Therefore, in this particular section, I only explain line-by-line the first section of the macro. In other words, the With… End With block. The With statement allows you to execute a series of statements on a particular object ActiveSheet.
PageSetup in this case without having to repeat the object qualification every single time. In other words, all of the statements within the With… End With block rows 2 to 8 below make reference to the object that appears on the opening statement ActiveSheet. The object that appears on the opening statement is the page setup description of the active sheet. The object is obtained by using the Worksheet. PageSetup property. In this particular situation, the Worksheet object is the active sheet ActiveSheet.
The PageSetup. CenterHeader property allows you to set what appears in the center section of the page header. You specify the text that you want to appear in this section as a string.