Excel macros with PowerShell

• Author
• Recent Posts

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.

Latest posts by Alex Chaika (see all)

• Reading Azure VM name, IP address, and hostname with PowerShell - Fri, Jul 28 2017
• Automating WSUS with PowerShell - Thu, Jul 13 2017
• Disable SSL and TLS 1.0/1.1 on IIS with PowerShell - Tue, Jun 27 2017

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

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

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

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.

$Excel = New-Object -ComObject "Excel.Application"
$Excel.Visible = $true
$workbook = $Excel.Workbooks.Add()

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:

$Excel = New-Object -ComObject "Excel.Application"
$Excel.Visible = $true
$workbook = $Excel.Workbooks.Add()
$Sheet = $Workbook.Worksheets.Item("Sheet1")
$Row = 1
$Column = 1
$Sheet.Cells.Item($Row,$Column) = "First Column"

And here is my Excel spreadsheet with the 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:

$Column++
$Sheet.Cells.Item($Row,$Column) = "Second Column"
$SysDateObject = new-object system.globalization.datetimeformatinfo
$DayNames = $SysDateObject.Daynames
$MonthNames = $SysDateObject.MonthNames
$Column = 1
$Row = 2
$DayNames | %{
   $Sheet.Cells.Item($Row,$Column) = $_
   $Row++
  }
$Column = 2
$Row = 2
$MonthNames | %{
   $Sheet.Cells.Item($Row,$Column) = $_
   $Row++
  }

And here is what I get:

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:

$Range = $Sheet.Range("A1:B1")
$Range.Interior.ColorIndex = 19
$Range.Font.ColorIndex = 11
$Range.Font.Bold = $True

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:

$Sheet.UsedRange.EntireColumn.AutoFit()

And finally I'm saving my workbook:

$Excel.ActiveWorkbook.SaveAs("C:\temp\myworkbook.xlsx")

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

$Excel = New-Object -ComObject "Excel.Application"
$Excel.Visible = $true
$workbook = $Excel.Workbooks.Add()
$Sheet = $Workbook.Worksheets.Item("Sheet1")
$Row = 1
$Column = 1
$Sheet.Cells.Item($Row,$Column) = "First Column"
$Column++
$Sheet.Cells.Item($Row,$Column) = "Second Column"

$SysDateObject = new-object system.globalization.datetimeformatinfo
$DayNames = $SysDateObject.Daynames
$MonthNames = $SysDateObject.MonthNames
$Column = 1
$Row = 2
$DayNames | %{
   $Sheet.Cells.Item($Row,$Column) = $_
   $Row++
  }
$Column = 2
$Row = 2
$MonthNames | %{
   $Sheet.Cells.Item($Row,$Column) = $_
   $Row++
  }

$Range = $Sheet.Range("A1:B1")
$Range.Interior.ColorIndex = 19
$Range.Font.ColorIndex = 11
$Range.Font.Bold = $True   
$Sheet.UsedRange.EntireColumn.AutoFit()
$Excel.ActiveWorkbook.SaveAs("C:\temp\myworkbook.xlsx")

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.

$Excel = New-Object -ComObject "Excel.Application"
$Excel.Visible = $true
$workbook = $Excel.Workbooks.Open("C:\temp\myworkbook.xlsx")
$Sheet = $Workbook.Worksheets.Item("Sheet1")

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.

$Excel = New-Object -ComObject "Excel.Application"
$Excel.Visible = $true
$workbook = $Excel.Workbooks.Open("C:\temp\myworkbook.xlsx")
$Sheet = $Workbook.Worksheets.Item("Sheet1")
$Row = 2
$1stColumn = 1
$2ndColumn = 2
Do {
$Day = $Sheet.Cells.Item($Row, $1stColumn).Value()
$Month = $Sheet.Cells.Item($Row, $2ndColumn).Value()
Write-Host "$Day,  $Month"
$Row++
} While ($Sheet.Cells.Item($Row,$2ndColumn).Value() -ne $null

And here is the output of the script:

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:

$Value = $Sheet.Cells.Item('3', '2').Value()

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.