Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

How to create Multiple PDFs from Excel (Office Scripts + Power Automate)

Creating multiple PDFs based on a list is a common use case in Excel. I’m asked about it regularly.

A few weeks ago, I received this question about our Office Scripts course:

Before I start the course on Office Scripts, I was wondering if it is possible to loop through a spreadsheet and with each iteration of the loop save the spreadsheet as a PDF?

I thought it would be helpful to share how to do this. It is super easy with Office Scripts and Power Automate.

If you’re looking for a VBA solution, you can find that here:

Download the example file: Click the link below to download the example file used for this post:

Watch the video

Watch the video on YouTube


We have two Excel files for our example: Reporting Example.xlsx and PDF Save List.xlsx.

Save both files in a OneDrive folder (OneDrive for Business, not OneDrive personal).

Report Example.xlsx

Reporting Example.xlsx is the information we wish to save as a PDF; it is included in the Report sheet.

Full Report view - Multiple PDFs from Excel

Only the print area of the report will be saved as a PDF.

Changing the values in cells J4 (Region) and J6 (Date) causes the report to recalculate to display different views of the data.

Selection Parameters
  • Valid regions in cell J4 are North, South, East, West, Central, or * to include all regions.
  • Valid dates in cell J6 are any month ends from 31 July 2023 to 31 December 2023

PDF Save List.xlsx

PDF Save List.xlsx includes a Table called ReportList.

ReportList Table with items to create multiple PDFs for

The Table contains all the information we need:

  • Sheet: The name of the sheet with the parameters to change for each loop iteration.
  • Cell List: The list of cells we need to change for each loop. These could be named ranges if we had set them up.
  • Value List: The list of values to apply for each iteration of the report. The values must correspond to the same positions in the Cell List.
  • PDF Name: The name we wish to give to the saved PDF.

There are a few things to make you aware of here:

  1. I used US dates in the mm/dd/yyyy format because UK dates of dd/mm/yyyy caused calculation issues (not sure why).
  2. The separator between the Cell List and Value List is a pipe symbol ( | ). You can use other separators if you prefer.

Office Script

As part of our Office Scripts course, we give our Power Automate Ready Code library as a bonus. We are going to use one of the scripts from that library for this project.

In Excel, click Automate > New Script. Then copy and paste the following code into your Script Editor. Make sure you save the script. I have called it PA – Change Cell Values.

The script simply changes values on a worksheet, based on a list of cells and values.

function main(workbook: ExcelScript.Workbook, wsName: string, cellList: string, valueList: string, separator: string) {

  Purpose: Power Automate script to change cell values on a worksheet

    - wsName - The name of the worksheet
    - cellList - String value of cell references or named ranges 
        separated by a comma
    - valueList - String value of values separated by a comma
    - separator - The character(s) separating each item in the 
        cellList and valueList parameters

   - Multiple cells can be changed at the same time.
   - The cellList must be the same length as the valueList


  //Script variables
  let ws = workbook.getWorksheet(wsName);
  let cellListArr: string[] = cellList.split(separator);
  let valueListArr: string[] = valueList.split(separator);

  //Loop through all cells and apply new value
  for (let i = 0; i < cellListArr.length; i++) {

Office Scripts Code - in Code Editor

The comments at the top of the code detail the arguments we must pass from Power Automate.

Building the Power Automate Flow

Now let’s head over to Power Automate. Build the following flow.

There are additional notes below the image. Watch the video to see the flow created from the start.

Power Automate Flow


  • Manually trigger a flow: I have used a manual trigger for simplicity of demonstration, but this could be any relevant trigger.
  • List rows present in a Table:
    • File: This is the full file path to the PDF Save List.xlsx file noted above
    • Table: This is the name of the Table containing the data we need (ReportList)
  • Apply to each: Don’t worry about this part initially; Power Automate adds this when you try to use any values from the ReportList table.
    • Value: This is added automatically to represent each row of the Table.
  • Run script: This calls our script to change the cell values for the specified workbook.
    • File: The file to run the script on (e.g., our Reporting Example.xlsx workbook)
    • Script: Find the script we saved earlier. After selecting the script, the script parameters appear
    • wsName: Select the Sheet from the Dynamic Content pop-up
    • cellList: Select the Cell List from the Dynamic Content pop-up
    • valueList: Select the Value List from the Dynamic Content pop-up
    • separator: The charactor used between the Cell List and Value List in the ReportList (a | symbol in our example)
  • Convert file using path: This step creates a PDF from our Excel File. The file is converted in memory; we need to use the Create File step to save the file.
    • File Path: The name of the file to convert to a PDF
    • Target Type: The file type we wish to generate
  • Create file: This step saves the PDF file
    • Folder Path: The folder to save the PDF
    • File Name: The name of the file. Select the PDF Name from the Dynamic Content pop-up
    • File Content: The content to be included in the file. Select the File Content from the Convert file using path step section of the Dynamic Content pop-up

Running the Flow – Create multiple PDFs

Now let’s test the flow and see if this works.

Click the Test button in the top right corner and run the flow manually.

If we set up everything correctly, the individual PDFs will generate in the specified Folder Path. Select one of the PDFs to check; you know you want to.

Now change the date in the PDF Save List and rerun it. The PDFs are created with the new data.

Pretty neat, right?


Power Automate and Office Scripts provide a modern alternative to VBA for creating multiple PDFs based on a list. With one simple reusable script and a Table of parameters to loop through, we can quickly create multiple PDFs.

Related Posts:

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *