Here’s the scenario we’re dealing with: I want to create a script that applies special formatting to ALL my Excel sheets.
Not just one sheet, but all the sheets in the Excel file.
But that’s not all. I want this script to run not just on one file, but on ANY Excel file I have in a folder.
Basically, the script will loop through the folder, and if it’s an Excel file, it will run on every sheet in the Excel file.
OBJECTIVE #1 – Looping Through Sheets
Our first objective is to create a macro that processes each sheet in an active workbook.
To start our script, we begin by logging in to office.com and open a data file we have created called “To Format.xlsx”.
The number of records in each file used by this script will change. Some files will have few records while other files will have many records. “Few” & “many” are imprecise terms, but that’s the point.
The few constants we can state about these data files are:
- We will have two columns of data
- The column labels will be on the first row of the data set
- The first column will contain text while the second column will contain numbers
The data in the file will change; there could be Salary information in one file while Profit information in another file.
Our objective is to create a macro that will perform the formatting steps on every sheet in the file.
Featured Course
Excel Essentials for the Real World
Recording the Steps
Begin by selecting the Automate tab and click Record Actions.
You can see in the Record Actions panel (right) that the recording has started.
Clear Existing Formatting
The first thing our macro should do is to remove any existing formatting from the data.
Select columns “A” & “B” and click Home (tab) -> Editing (group) -> Clear -> Clear Formatting.
Format the “B” Column for Large Numbers
The next step is to apply a formatting style to the “B” column that places commas every 3 place values and shows whole numbers only.
Select column “B” and select Home (tab) -> Number (group) -> General (dropdown) -> More Number Formats… -> Number (category) and apply the below settings.
Format the Headings
Next, select the headings on row 1 and format the background color (i.e., cell color) to light gray, bold, and add a thin line to the bottom border.
Resize the Columns to Show All Data
Making sure to not obscure any of the data if the columns are too narrow, select columns “A” & “B” and perform an AutoFit to resize the columns based on the data.
Viewing the Recorded Steps
We can see in the Record Actions panel the list of steps performed during the recording session.
Stop the Recording
Stop the recording of the steps by clicking the Stop button in the Record Actions panel.
Renaming the Recording
Before we test the macro, let’s give the macro a more understandable name, “Formatting”.
Viewing the Recording’s Code
Click the Edit button in the Code Editor panel to reveal the underlying code for the macro.
You don’t need to understand any of this code, but you should be able to use a bit of common sense coupled with experience to deduce the general ideas contained in the code.
The code generator is thoughtful enough to include comments throughout the code. The comments are displayed using a green font color.
Executing the Code for Every Sheet in the Workbook
All the steps are being applied to the active worksheet.
We don’t want to limit the code execution to only the active sheet. We want the code to be executed on every sheet in the workbook.
To figure out how to work with the sheets as a collection, we need to visit the Microsoft documentation website for Office Scripts.
Office Scripts Documentation
Scroll down the webpage to get to the Collections section.
We need to learn a bit about working with Collections since we are needing to manipulate a collection of worksheets.
We can see from the beginning of the “Iterate over collections” section a sample line of code to get all worksheets in the workbook.
Select the code below and copy it to memory (CTRL-C).
let sheets = workbook.getWorksheets();Return to the recorded script code and replace the existing LET statement with the copied code from above.
This modification will permit us to work with every sheet in the workbook, but we still need a line of code to loop through the sheets performing the formatting instructions.
This code is also in that same sample code talking about Collections.
for (let sheet of sheets) {
“Sheets” is the collection of worksheets as defined by the let sheets = workbook.getWorksheets() statement. “Sheet” is each sheet in the worksheet when examining them one at a time.
Copy the above code (for (let sheet of sheets) {) into memory and return to the recorded code.
Add a blank line after the LET statement and paste the copied code onto the new line.
Because we have used an open-brace character (i.e., curly brace) at the end of the FOR line of code, we need to add a closed-brace character to the end of the code for completeness.
Updating the Existing Code Pointers
Our current code is pointing to “selectedSheet” for each recorded action.
We need to replace each instance of a “selectedSheet” reference with a “sheet” reference.
Press CTRL-H to open the Find/Replace option.
Locate all instances of “selectedSheet” and replace them with “sheet”.
Click the Replace All button to update the code.
The updated code appears as follows.
Click the Save Script button to commit the changes to the code.
Testing the Recorded Code
Although the first sheet has the desired formatting updates, we’ll return to the first sheet and run the code.
We see that nothing appears to have changed on the first sheet, but a look at the second sheet reveals a newly revised dataset that mimics the formatting of the first sheet’s dataset.
Featured Course
Black Belt Excel Package
OBJECTIVE #2 – Looping Through Files in a Folder
Our second objective is to use Power Automate to loop through all sheets in a series of files in a folder.
In addition, this automation solution needs to be scheduled to run at a specified time.
We’ve set up a folder named “Reporting” on our OneDrive where we have stored the file “To Format.xlsx” that holds the previously recorded formatting code.
Alongside this file in the same folder is a file named “Salaries.xlsx” that has several sheets with data like the following.
We want to create a Power Automate script that will open the desired files and loop through each sheet applying the desired formatting.
Be Careful! We need to ensure the Power Automate script does not execute on non-Excel files located in the same folder.
Opening Power Automate
Return to the office.com main page and open Power Automate.
In Power Automate, click the Create option (left panel) to begin the creation process.
We will select the option to create a “Scheduled Cloud Flow”.
We name the flow “Monthly Formatting” and set the scheduling option for execution each month. Click Create when finished.
Adding the Steps for Monthly Execution
To start the list of scripting instructions, click “+ New Step”.
Let’s search for all operations that deal with OneDrive by typing “onedrive” in the operation search box.
We’ll select the operation labeled “OneDrive for Business”.
Selecting an Action
In the list of “OneDrive for Business” operations, we’ll select the action labeled “List files in folder”.
Browse to select the “Reporting” folder as the desired scan location.
Click “New Step” to continue the scripting process.
Running the Script on the Selected Folder
The next step is to select the formatting script we created earlier to run on the previously selected folder.
We’ll select the connector labeled “Excel Online (Business)”.
In the list of Actions, select the action “Run Script”.
We define the following arguments for the Run Script panel:
- Location – “OneDrive for Business”
- Document Library – “OneDrive”
- File – see the following note
- Script – “Formatting”
NOTE: For the File argument, select the Dynamic Content category and click the “Id” option.
This will create a new control named Apply to Each that will nest the Run Script step within it.
The script is smart enough to identify that we have the potential for multiple files coming from the previous “List files in folder” step that needs to be processed by the script.
The Run Script panel will appear as follows.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Picking Out the Excel Files
If we stop here with our script, the script will run and execute the formatting steps on each file in the selected folder.
The problem is that the script does not discriminate between Excel files and any other type of files.
We only want to process Excel files (i.e., files with an .xlsx file extension.)
Select Add Action at the bottom of the script and select Control from the Choose an Operation panel.
In the Control panel, select the Action labeled Condition.
Our Condition will be to check to see if the file’s name ends with .XLSX.
The Condition should be checked before the Run Script step is executed.
As it stands, the script is running before the file check condition is executed.
We solve this issue by dragging and dropping the Run Script step into the If Yes box.
Click Save at the bottom to save the script.
Testing the Flow
We can test the newly created Flow by clicking the Test button in the upper-right corner of the window.
We will test the flow Manually and click Test at the bottom of the screen.
Select Run Flow.
If all goes well we will be presented with a success message.
We see that each step ran successfully along with the amount of time (in seconds) to process each step.
Checking the Results
If we navigate to the OneDrive “Reporting” folder and open the “Salaries.xlsx” file, we see that the table formatting has been applied to each sheet in the file.
If a new Excel file is added to the “Reporting” folder, the formatting will be applied to all sheets in the file when the schedule is met.
Currently, we are updating monthly. We can make this update schedule more aggressive to perform updates more frequently.
We can also set this script to run manually allowing us to execute the script on demand from our mobile app.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
