How to Set PDF Page Size to A4 in Excel VBScript Conversion
Question details
The user needs to force an Excel VBScript to export worksheets as A4-sized PDFs instead of inheriting the default system printer paper size (such as Letter).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the conversion of Excel worksheets to PDF files via VBScript, where the output requires a specific A4 paper size regardless of local printer defaults.
- Observed behavior
- The exported PDF documents use the computer's default paper size instead of the desired A4 size.
Ensure you have a backup of your target Excel files before running batch automation scripts, and verify your system allows the execution of VBScript files.
Use PageSetup.PaperSize Property Before Exporting
Modify your VBScript to explicitly assign the A4 paper size property to the worksheet before invoking the ExportAsFixedFormat method.
By default, Excel uses the system's default printer settings during PDF export. To override this in a VBScript without affecting the system settings, you must assign the numeric enumeration value for xlPaperA4 (which is 9) to the PageSetup.PaperSize property.
Right-click your existing .vbs script file and select 'Edit', or open it using a text editor like Notepad.
Find the section of your code where the workbook is opened and the target worksheet is selected (e.g., xlWB.Sheets.Select).
Insert the following line right before the export command to apply the A4 dimension: xlWB.Worksheets(1).PageSetup.PaperSize = 9
If you are exporting multiple sheets, ensure you loop through each one and apply the property: ws.PageSetup.PaperSize = 9.
Save the script file and double-click to run it. The exported PDFs will now strictly adhere to the A4 page size format.
Easily Export Spreadsheets to A4 PDF with WPS Office
If you want to avoid writing and maintaining complex VBScripts, WPS Spreadsheet allows you to easily format your pages to A4 and batch export your workbooks directly to PDF with its built-in tools.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your Excel workbook.
- 2. Set the page size: Navigate to the 'Page Layout' tab on the top ribbon, click 'Size', and select 'A4'.
- 3. Access the export tool: Click the 'Menu' button in the top left corner and select 'Export to PDF'.
- 4. Confirm and export: In the export dialog box, verify your page range and click 'Export to PDF' to save your correctly formatted document.
Frequently Asked Questions
What is the VBScript enumeration value for A4 paper size in Excel?
In Excel's PageSetup object, the constant xlPaperA4 is represented by the integer 9. Since VBScript cannot read native Excel VBA constants directly, you must use the number 9 (e.g., PageSetup.PaperSize = 9).
Why does my VBScript export PDFs in Letter size instead of A4?
The ExportAsFixedFormat method relies on the default page layout settings of the active system printer. If your local default printer is set to Letter, Excel inherits this size unless you explicitly override it via the PageSetup properties in your script.
Can I apply the A4 setting to all worksheets at once in my VBScript?
You cannot apply it to the entire workbook object simultaneously. You must iterate through the sheets using a loop (like For Each ws In xlWB.Worksheets) and set ws.PageSetup.PaperSize = 9 for each individual sheet before exporting.
Does setting the PaperSize in VBScript change my computer's default printer settings?
No. Altering the PageSetup.PaperSize property within your script's Excel COM object only affects that specific workbook session. Your computer's default printer paper size will remain unchanged.
