One popular way to gather and analyze both system configuration and system performance data is by using Microsoft Excel. Regular tables can be used to order and filter data and pivot tables and graphs can be used to analyze further any kind of collected data and find trends. This month we’ll see how to leverage PowerShell to gather system information data and consolidate it in Excel.

Excel automation basicsThe ability to build automation tasks by integrating different products has been available since a long time. If you’ve been around long enough, you may recall in the AmigaOS world the ARexx scripting language that could be used to automate tasks with products that exposed specific messaging ports. In Microsoft world, task automation has been traditionally done using DDE and OLE Automation which is based on COM.

Before PowerShell, automation in the scripting world could be done using VB Script scripts to interact with applications through COM. PowerShell is just a modern and more convenient way to do it.
Starting to interact with Excel is as simple as creating a COM object which represents the Excel application like in the following code example:
Excel-new-object-comobject-excel.application
 
You are basically using the familiar new-object cmdlet with the –comobject parameter that let you specify the ProdID (programmatic identifier) of Excel. By setting the Visibile property of the main application object, you can actually see Excel’s User Interface. Please note that while displaying the GUI may be a good idea when developing a script, to see what happens or to debug, it is usually better to leave it hidden to avoid interactions by mistake and because it’s also faster.

At this point you should have Excel running and you can start interacting with it. For example, the following code adds a new WorkBook:
WorkBook-Excel.WorkBooks.Add_
Please note that, depending on your environment, you may run into a problem documented in Knowledge Base article KB320369. If this is the case, you should be able to work around it by using the following code:

Listing 1 Code to work around the automation bug in Excel
Listing 1 Code to work around the automation bug in Excel
You should also note that sometimes you may get back error 0x80010001 (RPC_E_CALL_REJECTED) while trying to add the workbook. This is due to the fact that Excel is able to suspend its Object Model when doing some operations while still accepting calls. Responses to calls while the Object Model is in this state get converted by COM interoperability mechanisms to the above kind of exceptions. As a matter of fact, you can get this exception for any kind of call, not only when trying to add a new workbook. Because proper handling of this problem its outside the scope of this article, and because it usually happens if you try to add a new workbook before Excel has finished to load, we’ll take the practical approach to simply wait for a couple of seconds in between the calls.
Once you have a new WorkBook open you can start interacting with it. For example, because the WorkBook already contains a WorkSheet you can rename it as follows:
WorkBook.Worksheets.Item1_.Name-MySheet

More interestingly, you can start adding your own data to the sheet as in the following example:

Listing 2 Code to add data to the worksheet
Listing 2 Code to add data to the worksheet

In the following picture you can see the result:

Figure 1 Result of the code that added data to the worksheet
Figure 1 Result of the code that added data to the worksheet

Once you’ve added data, you can start manipulating it. For example, let’s say you want to add some borders around the cells containing the data you just added. You can start by creating a Range object representing a range of cells for convenience as follows:

Range-WorkSheet.Range_

Then you change the size so that values fit in cells width and add a dotted border line for each cell in the range by simply referring to the Borders collection of the range as in the code that follows:

Listing 3. Code to change the size and add dotted borderlines
Listing 3. Code to change the size and add dotted borderlines

Finally, you add a thicker border to the whole range of cells by invoking the BorderAround method of the range as follows:

Listing 4 Code to add a thicker border
Listing 4 Code to add a thicker border

You can see the final result in the following picture:

Figure 2 Results of the data manipulation commands
Figure 2 Results of the data manipulation commands

Adding borders, colors and all other visual tweaks to make the data more readable is quite boring and distracting especially when you’re trying to get some reporting done. Luckily, Excel packages some nice style that you can apply to ranges that represent tables of data. You start by clearing the border lines added previously and then continue by adding to the worksheet a ListObject object derived from the previously defined range as follows:

Listing 5. Code to clear the borderlines and add a ListObject object
Listing 5. Code to clear the borderlines and add a ListObject object

At this point you should already see the range formatted as a table with headers and one row using the default style. You can change the table style through the TableStyle property directly from the ListObject object or by its reference through the Range object as the following code shows:

ListObject.TableStyle-TableStyleMedium9

The following picture show the end result which may be different for you depending on the styles defined on your system:

Figure 3 Results of the commands that applied a preset style
Figure 3 Results of the commands that applied a preset style

When you’re done, to save workbook you invoke the SaveAs method after suppressing any application generated warning as follows:

Excel.DisplayAlerts-false

Finally, you should close the application by invoking the Quit method and do some cleanup to release the COM the object as the following code shows:

Listing 6 Code to quit Excel and do some cleanup
Listing 6 Code to quit Excel and do some cleanup

You can find the complete PowerShell code in the Script 1 – Excel Automation Basics

Gathering WMI data

There are several reasons why you want to integrate with Excel within scripts. For example you may want to access WMI API in a simple way by using built-in PowerShell cmdlets and prepare some reports about one or more systems by leveraging the pipe and by feeding collected data to an Excel sheet. So let’s build a script to generate a report about local drives size and space used. You start by specifying some parameters that can be passed to the script as follows:

param-

Then you add to the script a small library of common functions, based on what we introduced in the first part of the article, which can be used also for other scripts in future:

Listing 7 Code to add the library of common functions
Listing 7 Code to add the library of common functions

The name of the functions should be pretty much self-explaining: Excel-Init creates and initializes a new instance of Excel, Excel-NewWorkBook adds a new workbook to a running instance of Excel, Excel-NewWorkSheet adds a new worksheet to a workbook and Excel-SaveAndExit is used to save the workbook to file and properly close Excel.

You then build a function that queries the WMI Win32_Volumes class and populate a worksheet with the returned data as the following code shows:

Listing 8 Code to query the Win32_Volume class and populate a worksheet with the returned data
Listing 8 Code to query the Win32_Volume class and populate a worksheet with the returned data

To increase readability, you can create a function to add some formatting to the data as follows:

function-Excel-PrettifyWorkSheetWorkSheet

You should note that in this case, to size columns and to change the table style, you didn’t define a Range object. Instead, because you know how you built the worksheet, you can use a shortcut: the UsedRange property of the worksheet returns a Range object with all the cells you populated with data. Also, you can omit all parameters when adding a ListObject to the worksheet and it automatically chooses the used range.

Finally, following is the main code for the script that calls the previously defined functions:

Excel-Excel-Init

Running the script creates the desired Excel report as shown in the following picture:

Figure 4 Results of the script that gathers WMI data
Figure 4 Results of the script that gathers WMI data

You can find the complete PowerShell code in the Script-2-Get-WMI-information-Static

Conclusion

In this first part of this mini-series we’ve covered the basics of automating tasks with Excel using PowerShell and we’ve gone through a small example to automatically build Excel reports from a specific WMI query. In the next article we will see how to dynamically build reports from any WMI query and how to create multi-worksheet workbooks from a configuration file. In other words, we’ll build a tool to automate gathering of common data into Excel reports. Until then, we’d love to hear from you—especially about which topics you’d like us to cover next.

Herbert Albert is co-author of this post.