You can get immediate free access to these example files by subscribing to the Power Spreadsheets Newsletter. The Workbook. The Worksheet. The Range.
|Published (Last):||18 May 2015|
|PDF File Size:||14.53 Mb|
|ePub File Size:||12.57 Mb|
|Price:||Free* [*Free Regsitration Required]|
You can get immediate free access to these example files by subscribing to the Power Spreadsheets Newsletter. The Workbook. The Worksheet. The Range. ExportAsFixedFormat method, when working with a Range object.
The Chart. The basic syntax of the ExportAsFixedFormat method is generally as follows: expression. This particular VBA method has 9 different parameters.
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. Furthermore, XPS never seemed to gain much traction. 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. This is, simply, standard file quality. Quality Option 2: xlQualityMinimum value is 0. This represents minimum file quality.
To make this choice, you simply need to set the IncludeDocProperties parameter to True or False as follows: True: Includes the document properties. 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.
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. This macro consists of a single simple statement: ActiveSheet. 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. 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. Use a representation of a Chart object, and work with the Chart. The purpose of this method is to save the relevant object a worksheet returned by ActiveSheet in the example above as a PDF file. 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. The reason for this is that, as I show below, the macro literally just saves the active worksheet to a PDF file. However, there are several things that can be improved. The following are 2 examples: The resulting page layout is not accurate. 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. 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.
Worksheet.ExportAsFixedFormat method (Excel)
This is a very simple and quickest method to export your Excel data to a CSV file. No extra coding required in order to maintain the comma delimiter or double quotes etc. Excel does it by itself. At the same time, this method has some short comings or challenges as well. Drawbacks of this Method 1. It ignores rest other sheets and its data. You do not have control over data — which one to be exported or ignored.
Subscribe to RSS
Workbook.ExportAsFixedFormat method (Excel)
ExportAsFixedFormat creates PDF with correct dimensions but wrong size