[Solved] Office Script to Print Range of Cells to PDF in Excel

[MichaelP]

I have been struggling with a MS Office Script to Print a range of cells in excel to PDF without using VBA coding or Macro's.
I have previously written VBA Script to perform this operation by clicking on a button on the page, but would rather apply a non-macro solution.
Any help would be appreciated.

 

[shamimarita]

Hello MichaelP,

If you’re looking for a manual solution to print a range of cells to PDF without using VBA or macros, you can use Excel’s built-in features.

1. Highlight the cells you want to export (e.g., A1).
2. Click File in the top left corner.
3. In the left pane, choose Export.
4. Click on Create PDF/XPS.
5. In the dialog box that appears, click on Options.
6. Under the Publish what section, select Selection (this will ensure only the selected range is exported).
7. If needed, adjust any other settings, such as page orientation or print quality.
8. After setting the options, click OK, and then choose where you want to save the file.
9. Click Publish to export the selected range as a PDF.

This method doesn't use any VBA or macro coding and allows you to export specific ranges to PDF manually. It’s a quick and straightforward solution.

 

[MichaelP]

Thank you Shamimarita for your quick response, however, I am looking for a Office Script to perform the action with a single button click. Just as you can assign a macro to a button on a Macro Enabled Workbook, I would like to stay away from a xlsm as our company frowns on these and is transitioning away from macros and adopting Office Scripts in standard xls workbooks for network security reasons. As I mentioned, I have written VBA code to enable a button to Print to pdf a range of worksheet cell values, however, this would require the workbook to be macro enabled.
An Office Script to perform the same function is the desired process.

 

[shamimarita]

Hello MichaelP,

To print a range of cells in Excel to PDF using Office Scripts (without macros), with a button to trigger the Office Script that automates the PDF export you can use the following Office Script

function main(workbook: ExcelScript.Workbook) {
  // Select the worksheet and range you want to print
  let sheet = workbook.getWorksheet("Sheet1"); // Replace with your sheet name
  let printRange = sheet.getRange("A1:D10");   // Replace with your desired cell range
 
  // Set print settings
  let printSettings: ExcelScript.PrintSettings = {
    scaling: {
      scaleType: ExcelScript.PrintScaling.fitSheetOnOnePage
    },
    fitToWidth: 1,
    fitToHeight: 1,
    paperSize: ExcelScript.PrintPaperSize.a4,
    orientation: ExcelScript.PrintOrientation.landscape
  };

  // Save the range as a PDF file
  sheet.getRanges().getRangeAreas([printRange.getAddress()])
    .printToPDF(printSettings, {
      pdfFileName: "ExportedRange.pdf"
    });
}

 

[MichaelP]

Thank you Shamimarita for your response, however, When entering the script into Excel (Microsoft 365) Code Editer the following is returned.

function main(workbook: ExcelScript.Workbook) {
// Select the worksheet and range you want to print
let sheet = workbook.getWorksheet("Sheet1"); // Replace with your sheet name
let printRange = sheet.getRange("A110"); // Replace with your desired cell range
// Set print settings
let printSettings: ExcelScript.PrintSettings = {
scaling: {
scaleType: ExcelScript.PrintScaling.fitSheetOnOnePage
},
fitToWidth: 1,
fitToHeight: 1,
paperSize: ExcelScript.PrintPaperSize.a4,
orientation: ExcelScript.PrintOrientation.landscape
};
// Save the range as a PDF file
sheet.getRanges().getRangeAreas([printRange.getAddress()])
.printToPDF(printSettings, {
pdfFileName: "ExportedRange.pdf"
});
}

ExcelScript reports Errors in the Highlighted Underlined Commands as not existing.

Here is a link to my ExcelScript.
PRINT RANGE TO PDF.osts

 

[shamimarita]

Hello,

Office Scripts contains some methods that might not exist or may be incorrectly used. try this modified code but ensure that your Excel version supports these features, and consult the Office Scripts documentation for more updates.

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getWorksheet("Sheet1");  // Your worksheet name
    let printRange = sheet.getRange("A1:D10");  // Your range

    // Set print settings
    let printSettings = {
        scaling: {
            scaleType: ExcelScript.PrintScaling.fitSheetOnOnePage
        },
        paperSize: ExcelScript.PrintPaperSize.a4,
        orientation: ExcelScript.PrintOrientation.landscape
    };

    // Export to PDF
    printRange.printToPDF(printSettings, { pdfFileName: "ExportedRange.pdf" });
}