How to Export Multiple Excel Sheets to PDF Using Office Scripts
Question details
The user needs to export multiple Excel worksheets into a PDF file using Office Scripts while maintaining configured print areas and page breaks.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating the conversion of multi-sheet Excel workbooks to PDF format in a cloud environment.
- Observed behavior
- Office Scripts lacks a direct API for PDF export, and standard Power Automate conversion actions fail to preserve custom page breaks and print areas.
Ensure you have access to Power Automate and an active Microsoft 365 subscription, as you will need to combine cloud flows with your scripts to generate the PDF.
Use Power Automate with Office Scripts
Since Office Scripts cannot generate PDFs directly, you can pass a list of worksheets from your script to Power Automate to handle the file conversion.
This method requires creating an Office Script to return the target worksheet names, then using a Power Automate flow to extract the data and convert it into a PDF document.
Open Excel on the web, go to the 'Automate' tab, and click 'New Script'. Write a script that loops through the workbook and returns an array of the sheet names you want to export.
Log into Power Automate and create a new flow. Add the 'Run script' action from the Excel Online connector to execute the script you just created.
Add the 'Convert file' action (via OneDrive for Business or SharePoint connectors) in your flow, targeting the Excel workbook, and set the target format to PDF.
Run the flow and open the generated PDF. Verify the layout, as Power Automate's default conversion engine may alter custom page breaks and print areas.
Implement Third-Party Conversion APIs
If strict preservation of page breaks and print areas is required, replace the default Power Automate conversion action with a specialized third-party connector.
Easily Export Multiple Sheets to PDF with WPS Office
Skip complex scripting and Power Automate flows. WPS Spreadsheet offers a built-in, one-click feature to export multiple worksheets to PDF while perfectly preserving your custom page breaks and print areas.
- 1. Open your Workbook in WPS Spreadsheet: Launch WPS Office and open the Excel file containing the sheets you want to export.
- 2. Access the PDF Export Tool: Click on 'Menu' in the top left corner, then select 'Export to PDF' from the dropdown list.
- 3. Select Sheets to Export: In the export dialog box, choose the 'Entire Workbook' option or manually check the specific sheets you wish to include in the PDF.
- 4. Export and Save: Adjust any necessary export settings and click 'Export' to generate a perfectly formatted PDF document instantly.
Frequently Asked Questions
Why doesn't Power Automate preserve my Excel page breaks?
The default 'Convert file' action in Power Automate uses a generic cloud rendering engine. It extracts the raw data and auto-fits it to a standard page size, completely bypassing custom print areas and page breaks configured in the desktop Excel app.
Is there a direct API in Office Scripts to save a file as PDF?
No. Currently, Office Scripts lacks a native method or API to directly save, export, or convert a workbook into a PDF format. It must be paired with an external automation tool like Power Automate.
Can VBA handle PDF exports better than Office Scripts?
Yes. If you are using desktop Excel, VBA includes a direct 'ExportAsFixedFormat' method. Unlike cloud-based Office Scripts, this VBA method perfectly respects local print settings, including page breaks and predefined print areas.
