For example:. This statement assigns a value to the PdfFilename variable declared in block 1 above. The value that is assigned to PdfFilename is the file name provided by the user through the Save As dialog. This Save As dialog box is displayed by the Application. GetSaveAsFilename method. In this particular example, the GetSaveAsFilename method uses the following 3 parameters :.
The following screenshot shows the Save As dialog box that is displayed by the GetSaveAsFilename method that uses the parameters above. Notice the effect of each of the arguments that I list above:. The If… Then statement within the example macro above can be broken down into the 3 following sections:. The condition tested by this particular statement is whether the variable PdfFilename is different from False.
If the condition is met PdfFilename isn't False , the statements within the If… Then block are executed. These statements are the ones that adjust the page setup as explained in macro example 3 above and export the active worksheet to a PDF file as I explain in macro examples 1 and 2 above. If the condition isn't met PdfFilename is False , the statements within the If… Then block aren't executed. In such a case, execution of the macro ends without the Excel worksheet being converted to PDF.
In such cases, you probably don't want to proceed with the process of saving the Excel file as PDF. ExportAsFixedFormat Method. I explain all of these statements in great detail within the previous macro examples here and here. This is the line that specifies the value of the Filename parameter of the ExportAsFixedFormat method. In other words, this line is the one that specifies the filename of the PDF file that results from the conversion.
In this particular case, the filename that is assigned to the PDF file is the value of the PdfFilename variable. In turn, the value of the PdfFilename is determined by the Application. GetSaveAsFilename method in block 2 above. As a consequence of this, the filename is simply the one that is specified by the user of the application by using the Save As dialog displayed by the GetSaveAsFilename method.
The last row of the If… Then statement and the sample macro in general signals the end of the If… Then statement. As a general rule, whenever you want to save all of the worksheets within an Excel workbook, you can simply use the Workbook. ExportAsFixedFormat method. The explanations I provide throughout this Excel tutorial particularly the first section are generally applicable to the Workbook.
However, when you use the Workbook. This may be what you want in certain situations. However, in other situations, you may want to save each worksheet within a workbook in a separate file.
You can achieve this by working with the Worksheet. ExportAsFixedFormat method and loops. In the context of the ExportAsFixedFormat method and saving Excel files as PDF, the following 2 are the main things you must understand in connection to loops :.
When properly structured, a loop allows your VBA code to go through every single worksheet and saves it as a separate PDF file. Let's take a look at a macro that combines the ExportAsFixedFormat method with loops in order to save each worksheet as a separate PDF file:.
The following screenshot shows the new items, which I explain further below. This statement declares a variable called iCounter. The selected data type is Integer which, as explained in this tutorial , can store integers between , and 32, The iCounter variable is used as a loop counter , a topic I explain further when covering the following rows.
This is the opening statement for the loop. In this particular example, I use a For… Next loop. This is, however, not the only type of loop you can use for these purposes. The end value of the counter, which determines when the looping stops, is specified by the Sheets.
Count property. The value returned by Worksheets. Count is the number of worksheets. As a consequence of the above, the macro loops a number of times equal to the number of worksheets in the active workbook. As I explain above, this is the opening statement of the With… End With block that adjusts the page setup.
All of the statements within a With… End With block rows 4 to 10 make reference to the object that appears in this statement Worksheets iCounter. The difference between both statements is, therefore, on the specific Worksheet object used within the syntax of the Worksheet. The purpose of this block is to adjust the relevant properties of the PageSetup object for the relevant worksheet as determined by the loop.
I explain, in detail, this statement and all the parameters of the ExportAsFixedFormat method used therein in a previous section of this tutorial. There are, however, 2 rows that differ from what appears in the previous examples:. In both cases, the difference is in the use of the Worksheets collection and an index Worksheets iCounter to refer to the relevant worksheet.
After reading this Excel tutorial, you're ready to start creating macros for purposes of saving Excel files as PDF. In addition to the above, you've seen 10 different examples of VBA code that allow you to save Excel files as PDF using several different settings and parameters.
As with most topics related to Excel and VBA, I've not exhausted the topic despite writing this very comprehensive tutorial. You can use the knowledge and ideas you've gained here in order to create different macros that achieve similar purposes.
Here are some of my most popular Excel Training Resources:. Among others, I also : Provide a thorough introduction to how you can use Visual Basic for Applications for purposes of adjusting the page setup attributes. Explain how you can save each worksheet from an Excel workbook in a separate PDF file.
The following table of contents lists the different sections of this blog post. Table of Contents. Most Voted Newest Oldest. Inline Feedbacks. Load More Comments. Specifies whether there are different headers and footers for odd and even pages. Returns a Graphic object representing the picture in the center section of the footer. Use the properties of CenterFooterPicture to set the attributes of the picture.
Returns a Graphic object representing the picture in the center section of the header. Use the properties of CenterHeaderPicture to set the attributes of the picture. Returns a Graphic object representing the picture in the left section of the footer. Use the properties of LeftFooterPicture to set the attributes of the picture. Returns a Graphic object representing the picture in the left section of the header.
Use the properties of LeftHeaderPicture to set the attributes of the picture. Returns a Graphic object representing the picture in the right section of the footer.
Use the properties of RightFooterPicture to set the attributes of the picture. Returns a Graphic object representing the picture in the right section of the header.
Use the properties of RightHeaderPicture to set the attributes of the picture. When you use it with an object qualifier, returns an Application object representing the creator of that object. Returns a bit integer indicating the application in which the relevant object was created.
Returns or sets the text alignment for the even pages of an Excel workbook or a section. I set it false because I create s of files using a loop, and I don't want them to open. If you want to open the file after creating using excel, set it True. One basic use is to print the selected range by just clicking on one button. But the best use is to use it in a loop where you need to create multiple receipts for different clients.
Create a sub for filling data and then use this code to print the selected range in a loop. So yeah guys, this how you can save selected range as pdf. I hope it was useful for you. If you still have any doubt or query regarding print in excel using VBA, ask in the comments section below. Download the working file. Add adds new workbook easily, however Your email address will not be published.
Printing Formula Syntax in Excel Conditional formatting depending on a cell co Print all workbooks in a folder using VBA in Change text in header when printing using VBA Google serves cookies to analyse traffic to this site. Information about your use of our site is shared with Google for that purpose. This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.
Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings. If you disable this cookie, we will not be able to save your preferences. Frede says: Thanks for the guide.
How do I get the same name on the pdf as the workbook? May 7, at pm. Name, ". May 9, at pm. May 14, at am. May 19, at pm. Patridk Murphy says: Good beginner article. Let me know if you take this on. May 20, at pm. Excel Off The Grid says: Thanks for the suggestion. Marco says: Thank you for the very useful post! Please keep going! June 17, at pm. Matt says: Thanks this has helped me save a ton of time! June 21, at am. Value Reply. Matt says: Brilliant; that was exactly the fix.
June 25, at pm. Linda Peppin says: Hi, great article. Thanks Reply. March 25, at pm. Jitender Modgil says: i am using win10 64 bit but error on Selection. July 2, at am. July 2, at pm. Lewis says: Thanks for the article, I do have one question. July 5, at pm. Kiran dupargude says: Hello Sir, I found this article very usefull, it really helped me a lot. July 6, at pm. Ted Whittier says: Great article as always. July 17, at pm. Let me know if you get any success with them.
Happy User says: thank you for this help! July 18, at pm. Alexandra says: Hello. July 31, at am. August 2, at pm. Jeannette says: Information provided is very educative. August 21, at am. August 23, at pm. Rick says: Hi everyone, I am using the looping through sheets example to output several sheets as 1 PDF per sheet and love that I found this macro.
Worksheets ws. October 28, at am. Range "A11". This is a loaded question, but your insights would be greatly appreciated!
November 4, at pm. This will be a great way to learn how to use VBA to automate your processes. Teri Vavricek says: Hello. November 19, at pm. November 21, at pm. Teri Vavricek says: Thank you for the quick response! November 23, at pm. Teri Vavricek says: I think I figured it out. No need to respond. November 24, at pm. November 30, at pm. Aira says: Thank you for the detailed explanation.
Thank you Reply. December 7, at am. Esther says: HellO!! However, would love to receive help on this: i wanna save the pdf as a certain name, referring to a cell in the worksheet that contains a formula referencing another cell How should i code this? December 17, at am. Range "A1". Value The sheet name and cell refence should be changed for your scenario. December 18, at pm. Value Can somebody help me pls?
Thx in advance and happy new year Reply. January 9, at am. Try something like this. Sheets "mySheet". January 11, at pm. Megan says: I love you! January 26, at pm. Andy says: Dear Expert, I would like to request for your help please. March 13, at am. Range "B1". Value However, the challenge really comes if the worksheet name changes. March 13, at pm. Thank you so much for your guidance!
March 15, at pm. Daniel says: Thankyou so much for your code, super useful! March 16, at pm.
0コメント