Almost everything you can do in the Microsoft Excel GUI can be done with PowerShell. Thus, you can use essentially use PowerShell to write Excel "macros."

Alex Chaika

Alex Chaika is a Microsoft Certified Solution Expert (MCSE) with more than 15 years of experience in IT systems engineering. He currently focuses on PowerShell and VMware PowerCLI.

It is beyond the scope of this article to explain every available function because you could easily fill a book with this topic. Instead, I'll explain some basic concepts, which should be enough to create, modify, format, and save an Excel file using a PowerShell script.

Notice that Excel has to be installed on the computer because you have to create an Excel.Application object in order to use its properties and methods:

Creating an Excel COM application object

Creating an Excel COM application object

Now I'm going to explore what methods and properties the object provides:

Exploring the Excel COM application object resources

Exploring the Excel COM application object resources

Just to give you an idea how powerful this object is, let's count the properties and methods:

Number of properties and methods in the Excel application object

Number of properties and methods in the Excel application object

To create a new Excel workbook using PowerShell, we'll use the COM object we instantiated above. It is important to note that two methods exist here. Your PowerShell script can work in the background, so the process that changes the Excel sheet isn't visible. Alternatively, you can bring Excel to the screen to oversee all the changes your PowerShell script performs.

The Excel COM object's Visible property is responsible for this setting. If you set this variable to $True, all operations will be visible. If you set it to $False, all operations will occur in the background.

When you run this little script, an Excel window with a new workbook will pop up on the screen.

Since we've already created a workbook, let's add captions to the columns. To do so, we need to know the coordinates of the particular cell we are going to put the value into. To make it easier, let's add two variables to the script: one for the row ($Row ) and one for the column ($Column).

This way, if we need to put a new value into the cell in the very first row and column, we just have to assign 1 to each of these variables. However, because an Excel workbook usually consists of several spreadsheets, we also need to tell our script which spreadsheet we want to change. You can do this by calling the spreadsheet either by name or by number. I'll use the name:

And here is my Excel spreadsheet with the column name created with PowerShell:

An Excel column name created with PowerShell

An Excel column name created with PowerShell

Now let's say I'd like to put the weekday names into the first column and the month names into the second one. Firstly, I need the caption for the second column. Then I'm getting the day and month names from the system.globalization.datetimeformatinfo object. I could create the arrays for the days and the months manually, but why not automate it with PowerShell?

I’m setting my $Column to 1 and the $Row to 2, so I can start putting the day names into the first column starting from the second row. Next, I'm using a ForEach-Object loop to iterate through the $DayNames array. To move down in the sheet, I'm incrementing the $Row value after each iteration. Then I do essentially the same operation with the month names:

And here is what I get:

A list of months and days in Excel created with PowerShell

A list of months and days in Excel created with PowerShell

Now I'd like to beef up my spreadsheet a little by adding column captions with a bold font and changing the background color of the cells. To do that, I'm defining the range of cells I'm going to change first, and then I just apply the font and color change to that range:

As a final step, I'd like to format all my columns according to their width values. Because I'm going to apply this change to all cells in the current spreadsheet, I'll use the UsedRange method:

And finally I'm saving my workbook:

Below is the complete script we've just written and a screenshot of the Excel spreadsheet we've created and formatted with PowerShell.

Excel spreadsheet created by PowerShell

Let's try to access and use the data from the spreadsheet. As in my previous example, we are going to create an Excel COM object and then use its methods and properties. The only difference is that instead of using the Add method, we have to use the Open method to open the workbook we created before. Now I am ready to access and manipulate the date in the spreadsheet.

Next, I'm establishing the variables for the rows and the columns, and then I use those variables for loading the data from the spreadsheet. To make sure that I get all the data from the table, I'm using a do-while loop to test the value in the second column. The loop stops when this value become $NULL.

And here is the output of the script:

Reading data from an Excel Spreadsheet with PowerShell

Reading data from an Excel Spreadsheet with PowerShell

Note that you can also address a particular cell by its coordinates. For instance, if you have to read the contents of the cell in the middle of the spreadsheet, you don't have to go all the way down. Instead, you can just directly access the cell using the row and column numbers.

For example, to read the value in row 3, column 2, the command would look like this:

Sometimes this ability can save you many iterations, as when working with CSV files from PowerShell. The advantage of working with CSV files instead of Excel sheets is that Excel doesn't have to be installed on the computer. However, working with the Excel COM object and PowerShell certainly give you more options, and the most notable one is the ability to target cells directly.

On the other hand, PowerShell is certainly a much more powerful language than Visual Basic for Applications (VBA). If you are familiar with PowerShell you might be faster to automate a task with PowerShell than with a native Excel macro.

In my next post, I will show you to edit Word documents with PowerShell.

Join the 4sysops PowerShell group!

Your question was not answered? Ask in the forum!

2+

Users who have LIKED this post:

  • avatar
Share
1 Comment
  1. David 2 years ago

    Alex:

    I have a quick question. Hopefully, you can assist me or at least point me to the right solution:

    I've an urgent project to complete , where I have to look/search for specific value/number ( =>100) in column "P" in massive multiple excel files that I can point to (data sets). After that, I need to print out/save a report with folder name where that particular value was found. I was wondering if Powergrep can do this type of search. I'm very short on time.I've tried to play with Powergrep around, but couldn't find an option where I can look at specific column in Excel files.Here is an example of folder structure and what I need to look for and what is my output should be:

    Looking at column "P" in excel ( column called "Temperature") for value that is =>100

    C:\Folder\ Folder1 Folder2

    Folder1 Excel1 Excel2 Excel3

    Folder2 Excel10 Excel200

    Output: The value was found @: Folder2

    Any help will be much appreciated!

    Thanks! David

    1+

Leave a reply

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

*

© 4sysops 2006 - 2019

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account