In this Article:On WindowsOn MacCommunity Q&AReferences. The spreadsheet's settings in Excel on both Windows and Mac computers. Double-click the Excel file which contains the macro you want to delete. Press Ctrl + S to do so. We can remove or delete checkbox on the worksheet or userform using ‘Go To Special’ or ‘Select Objects’ or by turning on Design Mode in Developer tab. When we don’t want to place checkbox control on the worksheet or userform we removes from there.
Power Spreadsheets focuses on Microsoft Excel. However, I've written several tutorials (such as and ) that have to do with PDF and, more particularly, with the topic of converting PDF files to Excel.
The reason why I write about PDF is relatively straightforward: PDF is one of the most widely used file formats. This particular Excel tutorial also focuses on the topic of working with Excel and PDF files. More precisely, I explain in detail how you can save Excel files as PDF using VBA, and provide 10 examples of VBA code that you can start using immediately.
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:.
Provide a thorough introduction to how you can use Visual Basic for Applications for purposes of adjusting the page setup attributes. Introduce some different ways in which you can specify the filename of the PDF file that results after the Excel to PDF conversion. 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.
This Excel VBA Save as PDF Tutorial is accompanied by files containing the data and macros I use in the examples below. You can get immediate free access to these example files by subscribing to the Power Spreadsheets Newsletter. The basic you use for converting Excel files to PDF is ExportAsFixedFormat. Therefore, let's start this blog post by taking a look at it: How To Save An Excel File As PDF Using VBA: The ExportAsFixedFormat Method 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.
You can apply the ExportAsFixedFormat method to several Excel objects. 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. The, when saving an Excel workbook as PDF. The Worksheet.ExportAsFixedFormat method, if you're saving a worksheet as PDF. The, when working with. The, if saving a chart as PDF.
The basic syntax of the ExportAsFixedFormat method is generally as follows: expression. ExportAsFixedFormat (Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish) For these purposes, “expression” is a variable representing a Workbook, Worksheet, Chart or Range object, depending on the particular version of the method you're working with. In other words, “expression” is the placeholder for the object that you want to actually save as PDF. This particular VBA method has 9 different parameters. Let's take a look at each of them: Parameter #1: Type 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. There are 2 types of file formats you can choose from:. File Type #1: xlTypePDF, represented by the value of 0. XlTypePDF corresponds to PDF files, the subject of this Excel tutorial.
File Type #2: xlTypeXPS, whose value is 1. XlTypeXPS corresponds to XPS files, Microsoft's alternative to PDF. The purpose of this Excel tutorial is to explain how you can use Visual Basic for Applications to save an Excel file as PDF. Therefore, whenever using the Type parameter in this blog post, I only use xlTypePDF. Furthermore, as explained at, XPS never seemed to gain much traction. Therefore, you're unlikely to encounter (or have to work with) many XPS files. Type is the only required parameter of the ExportAsFixedFormat method.
Parameter #2: Filename 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. When specifying the Filename parameter, you have 2 broad options:. Option #1: You can specify the full path and filename of the resulting file. Option #2: You can omit the full file path and, instead, just include the filename.
In this case, Excel simply saves the converted file in the current default folder. Filename is a string. Parameter #3: Quality You have the option of choosing the quality of the resulting PDF file (standard or minimum). For these purposes, you use the Quality parameter of the ExportAsFixedFormat method. More precisely, you can choose 1 of:. Quality Option #1: xlQualityStandard, whose value is 0. This is, simply, standard file quality.
Quality Option #2: xlQualityMinimum (value is 0). This represents minimum file quality.
Parameter #4: IncludeDocProperties The IncludeDocProperties parameter of the ExportAsFixedFormat method allows you to determine whether the document properties are included in the converted PDF file or not. To make this choice, you simply need to set the IncludeDocProperties parameter to True or False as follows:. True: Includes the document properties. False: Doesn't include the document properties. Parameter #5: IgnorePrintAreas 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:. True: Ignore print areas.
False: Don't ignore print areas. Parameters #6 And #7: From And To The From and To parameters of the ExportAsFixedFormat method allow you to specify the pages at which the publishing to PDF should begin and end. More precisely:. From is the number of the page at which Visual Basic for Applications starts to publish. If the From argument is omitted, VBA starts at the beginning. To is the number of the last page that VBA publishes.
If you omit the To argument, publishing goes on until the last page. Parameter #8: OpenAfterPublish By using the OpenAfterPublish parameter of the ExportAsFixedFormat method, you can determine whether the converted PDF file should be displayed in the PDF viewer as soon as the export process is completed. You specify whether the file is displayed or not by setting the OpenAfterPublish parameter to True or False, as follows:. True: Display file in PDF viewer after conversion. False: Don't display file in PDF viewer after conversion.
Parameter #9: FixedFormatExtClassPtr As explained at, the FixedFormatExtClassPtr parameter of the ExportAsFixedFormat method is a pointer to the FixedFormatExt class. How To Save An Excel File As PDF Using VBA: Basic Code Examples (Examples #1 And #2) Now that you are familiar with the ExportAsFixedFormat VBA method, let's take a look at some code examples. For purposes of this example, I have prepared a sample Excel workbook. This workbook contains 2 worksheets. Each worksheet includes a table that lists 100 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.
How To Save An Excel File As PDF Using VBA: Code Example #1 As I explain above when introducing the ExportAsFixedFormat VBA method, the only required parameter is Type. Therefore, strictly speaking, you can create a very simple macro for purposes of saving an Excel file as a PDF.
The following sample macro (named SaveExcelAsPDF1) is, probably, one of the simplest way to save an Excel worksheet as PDF using VBA. This macro consists of a single simple statement: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF This statement, can be separated in the following 3 items: Let's take a closer look at each of these elements: Item #1: ActiveSheet 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.
In the case of the sample SaveExcelAsPDF1 macro above, the Application.ActiveSheet property is used for these purposes. More precisely, ActiveSheet return an object representing the active sheet within the active (or specified) workbook.
You can, as a general matter, also for any of the following:. Make reference to a Workbook object, and use the Workbook.ExportAsFixedFormat method. Use a variable that represents a Range object, and use the Range.ExportAsFixedFormat method. Use a representation of a Chart object, and work with the Chart.ExportAsFixedFormat method. Item #2: ExportAsFixedFormat The ExportAsFixedFormat method is the main subject of this Excel tutorial.
The purpose of this method is to save the relevant object (a worksheet returned by ActiveSheet in the example above) as a PDF file. In this particular case, ExportAsFixedFormat uses a single parameter, which is Item #3: Type:=xlTypePDF 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. XlTypePDF is 1 of the 2 values in such enumeration. For the reasons that I explain above, most (if not all) of your macros whose purpose is to save Excel files as PDF will set the Type parameter to be xlTypePDF (or 0). Therefore, you're likely to use this particular line of VBA code often when creating such. Excel To PDF Conversion Results: Example #1 The sample SaveExcelAsPDF1 macro is very simple and relatively easy to follow. 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.
For purposes of this example, I have executed the SaveExcelAsPDF1 macro to save 1 of the worksheets within the sample Excel file that accompanies this tutorial as PDF. The following image shows the resulting PDF file: As first glance, the resulting PDF files looks OK. However, there are several things that can be improved. The following are 2 examples:. The resulting page layout is not accurate.
Notice how there's 1 column (corresponding to the Favorite Food Ingredient) missing in the image above. Within the resulting PDF, this column appears by itself in separate pages. The following screenshot shows how this looks like:. The converted PDF file is saved automatically to the default local file location (in this example, the Documents folder) and the file name is the same as that of the original Excel workbook (in this case, Book1). 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: How To Save An Excel File As PDF Using VBA: Code Example #2 The sample SaveExcelAsPDF1 macro above used the Worksheet.ExportAsFixedFormat method with its only required parameter (Type). However, as I explain further above, the ExportAsFixedFormat method has 9 parameters that allow you to further specify how Visual Basic for Applications carries out the conversion from Excel to PDF.
Therefore, in this second example, I include most of the parameters that you can use when working with the ExportAsFixedFormat method. I love reading and sharing success stories from amazing members of the Power Spreadsheets community, like you. If this (or any other) Tutorial has helped you, please share your success story below. This only takes few seconds and, by doing it, you help the future development of Power Spreadsheets. Any improvements I make to this or the other free Tutorials in Power Spreadsheets based on your feedback will benefit you too.
To share your success story, please do the following:. Fill the form below (required fields are marked with an asterisk (.)); and. Click the Share my success story button.
Bottom line: Learn how to quickly attach Excel files to your emails. These techniques and keyboard shortcuts can work for any email client including Outlook, Mac Mail, and Gmail. Skill level: Beginner If you work with a lot of Excel files, then chances are you also email a lot of Excel files. There are probably a million different ways to attach a file, and some of those ways can be painfully slow. You can end up spending a lot of time navigating through folders to find the file(s) you want to attach. Then repeat that process if you realize you need to make a change to the file, or you're not sure if you saved it.
If that sounds familiar, then this article should help speed up this process. ? Method #1: I Don't Use “Send as Attachment” Excel has a built-in feature called Send as Attachment that will attach the current file to a new email. This email can be created in Outlook, or your default email application. You can find the Send as Attachment button on the File menu, or add it to the Quick Access Toolbar (QAT). This is a quick way to attach the file to an email, but I don't use it.
Well, there are a few reasons I avoid it:. You cannot make changes to the file after you have attached it. In some versions of Office you cannot edit the file in Excel either. I always find myself needing to make changes to the file or take screenshots before I send it. It doesn't work if you are using an email application in your web browser like Gmail or Yahoo mail.
It only works for attaching one file to an email. It only works for new emails. Often times I will be attaching a file to a reply.
Based on those limitations, I never use the Send as Attachment feature. It's not a bad option, I just find it too limiting for everyday use. So let's look at more flexible solutions. Method #2: Use the Recent Items Menus Typically you will be attaching a file that you are currently working on. Both Windows and Mac have ways to view your most recent items in the Windows Explorer or Finder windows.
The first step is to click the Attach button in your email program. Here are the keyboard shortcuts to attach files for some common email clients. Outlook: Alt, H, A, F. Or use the Quick Access Toolbar shortcut I explain below. Gmail: From the email body, hit Tab twice to highlight the Attach button, then Enter. The number of times you hit Tab may vary depending on other extensions you have installed. The next step is to use one of the following methods to quickly locate the recent file.
If you are using Windows 10 then you can see the list of recent items by selecting Quick Access on the Navigation Pane, then scroll down to the Recent Files section. On a Mac you can view all your files and sort the Date Modified column to show the most recent items first.
New Recent Items feature in Outlook 2016 Outlook 2016 also has a new Recent Items feature in the Attach File menu. I really like this new feature because it allows you to see, and quickly attach multiple files that you are working on. You can still access the files in Excel and work on them. If you do make any changes to the file you will need to delete the attachment and re-attach the most recently saved version. The keyboard shortcut to attach the most recent file in Outlook 2016 is: Alt, H, A, F, Enter Alt, H, A, F will bring up this new menu in 2016.
Hit Enter to attach the most recent file. In older versions it will bring up the Insert File menu to select a file. You can then use the Recent file list or the copy/paste technique (method #3 below) to quickly attach a file.
A FASTER Way: Add the Attach Button to the Quick Access Toolbar The quickest keyboard shortcut for attaching a file in Outlook is to add the Attach button to the Quick Access Toolbar (QAT), then press Alt+location number to open the Insert File menu. You can add both the new and old attach icons to the QAT in 2016. So with the setup in the screenshot above the following keyboard shortcut will attach my most recent file to the email: Alt+2, Enter This is probably the fastest way to attach the latest saved version of the file you are working on. Here is an article on how to use the.
If you don't have the latest version of Office then the next method works universally on all apps and operating systems. Method #3: Copy & Paste the File Path My preferred method for attaching files is to copy & paste the file path. This method works with any email client and also any file type, not just Excel files. The process is simple. You just need to copy the file's path (location) to the clipboard, then paste it in the File name field of the File Explorer (Finder) window in the email attachment window. Let me break that down into steps.
Step #1: Copy the file path to the clipboard The file path is the full file location of the file including the drive letter, folders, and full file name. It will look something like the following. C: Users jon Documents Excel Campus Book1.xlsx So how do we copy it to the clipboard?
You can actually do this from the FileInfo menu in Excel. In Excel 2013 and 2016 you can left-click on this field and select Copy link to clipboard. In 2010 you use Ctrl+C to copy the path.
The keyboard shortcuts for copy link to clipboard (copy the file path) are:. Excel 2010: Alt, F, I, G, Ctrl+C. Excel 2013, 2016: Alt, F, I, G, C You can also use a macro to copy the path to the clipboard, and add a button to the Quick Access Toolbar.
Here is a post by Dick Kusleika from Daily Dose of Excel on. I have this setup on my QAT and it allows me to copy the file path with one click or keyboard shortcut. Leave a comment below if you would like me to explain more about this setup. Another method (submitted by Salvatore in the below) is to add the Document Location command to the Quick Access Toolbar.
This will add a box to the QAT that contains the full file path of the active workbook. You can click the box (or press the Alt+number keyboard shortcut) to select all the text, then press Ctrl+C to copy. Click here the image below to watch a quick animated screencast on how to add the Document Location command to the QAT. Step #2: Paste the file path to the attachment window Open the email in your email client (either new or reply) and press the Attach button. Now that the Insert File window is open, you just need paste (Ctrl+V) the full file path in the File Name box and press Enter. That might seem like a lot of steps, but it is actually pretty fast once you practice it a few times.
How Do You Attach Files to Emails? Well I hope one of those methods helps you save a little time with attaching emails. Like I said, there are probably a million different ways to go about this. Another popular method is to drag and drop the files from Explorer or Finder, into the body or attachment section of the email. (Thanks to Charlie for suggesting this in the!) Please with your preferred method. I would love to learn some new ways to do this.
Wayne - November 9, 2018 Hi there. I have created a excel book with a few sheets (+-25). My first few sheets contain date which I update monthly.
Then I have all the other sheets pulling information from the data sheet to display information in certain formats etc. Each sheet is the information per client – so each client’s data is displayed on it’s own sheet.
Now I want to email each client his sheet with the data on – kind of like a statement. How will I go about doing this automatically? So I want to press a button or follow a process that will automatically take e.g.
Sheet 1 and send it to client 1, and sheet 2 send it to clinet 2 etc. Will this be done through a macro? I can add the client’s email adress on the sheet as well. So the process would pick up the email adress on the sheet (refer to a specific cell that contains the email address) then attached that particular sheet in a email and the email will input the adress shown in the spreadsheet.
But this must be done automatically for all the sheets in the woorkbook i.e. 20 different emails created with each having it’s unique excell sheet attached.
Is this possible? Sam - April 14, 2017 I like using Send as Attachment to quickly send out files/reports that I just finished. In Excel/Outlook 2013, I was able to alt-tab between the excel file and the newly created email with the attachment. I can copy and paste some quick information from the excel file and paste it onto the body of the email. I figured this was a new feature with Office 2013.
I was able to do this until recently. I can no longer alt tab back to the excel when I use the Send as attachment option. I’m not sure what changed; I don’t remember making any changes in options with either program. Is there a way to go back to what it was? I have been trying to look in options of excel and outlook and can’t find it. Josh - March 8, 2017 I use the Email icon in the Quick Access Toolbar above the ribbon to send an excel file daily.
After I click on the Email icon, it opens a new message in Outlook with the attachment. It used to put the cursor in the To field automatically, but now my company has switched to Office 365. Now, it just opens up the new message and I have to click in the To field to type in the recipient’s name. How do I make it default to the To field, when opening a new message. This only happens when I send the file from Excel as an attachment.
Michelle - March 3, 2016 In your Method #1: I Don’t Use “Send as Attachment,” you claim that you “cannot make changes to the file after you have attached it. In some versions of Office you cannot edit the file in Excel either. I always find myself needing to make changes to the file or take screenshots before I send it.” Sure you can — all you need to do is close the email it has created and say “yes” when Outlook asks if you want to save the draft. Then open the draft and you can do whatever you want, just like when you attach the file any of the other ways you described. March 2, 2016 Hi Karen, The real file will be sent to the receiver. When you paste the file path into the attachment window, the email program (Outlook,Gmail) will attach the actual file.
Pasting the file path is just a shortcut that prevents you from having to navigate through folders to find the file you want to attach. Instead of drilling down through folders, you are basically telling the file browser window exactly where to go and which file to choose. Let me know if that makes more sense. Maybe I need to update the article to be more clear. Thanks for the great feedback Karen! Salvatore - March 2, 2016 Hi Jon, I have 2 ways to send emails.
(At work I use MS Outlook). From the file in excel, I use shortcutkeys – Alt F, D, A (F= File; D = Save & Send; A = Attachment) 2.
From the excel file I have the FILE LOCATION located above the formula bar. I copy that (it has path and file name) open up Email, Click attachment and paste the path & file name in the File Name box. I have done this for a long time, but I will try your methods too!
You will need to add the File Location to your QAT (see below) (I’m sorry I am unable to attach a screen shot at this time – I’m trying to send to Jon the jpg file) Thank you for your site! March 2, 2016 Thanks Salvatore! These are great suggestions! I received your image and recreated it to fit in the post.
I put a section above that also includes a screencast animation of how to add the Document Location command to the Quick Access Toolbar. The Document Location command contains the full file path/location of the active workbook. You can click on it to select all the text, then press ctrl+C to copy. You can also access it with the Alt+number key keyboard shortcut for the QAT. Thanks again!
March 3, 2016 Hi Nate, Yes, I actually have a link to a macro that does that in the article. But it became kind of hidden with all the other options for copying the file path. I actually use the method you are describing. Here is a screenshot of the button I setup in the QAT that calls the CopyFilePath macro. See, I do use Excel 2010. ? Here is the code: Sub CopyFilePath Dim DataObj As New MSForms.DataObject Dim sName As String sName = ActiveWorkbook.FullName DataObj.SetText sName DataObj.PutInClipboard End Sub You will also need to add a reference to the Microsoft Forms 2.0 Object Library to get that code to work. Tools References in the VB Editor window.
When I get a chance I’ll make a video that shows how to set it all up. It’s the method I use the most, and should have been more clear on that.