Posey's Tips & Tricks
Launching a PowerShell Script From Excel, Part 2: Order to Act
Learn how to pass spreadsheet values as parameters to a PowerShell script for more dynamic and functional automation.
In my previous post, I showed you how to create a clickable button in Excel. That button displayed a simple message box. Now, I want to show you how to use the button to kick off a PowerShell script.
To get started, put Excel back into Design Mode and view the code that is associated with the button. You will want to replace the existing code with this:
Private Sub CommandButton1_Click()
strCommand = "Powershell -File ""C:\scripts\hello.ps1"""
Set WshShell = CreateObject("WScript.Shell")
Set WshShellExec = WshShell.Exec(strCommand)
strOutput = WshShellExec.StdOut.ReadAll
MsgBox strOutput
End Sub
This code, which I found here, will cause Excel to launch the specified PowerShell script and then display the output in a message box. For the purpose of this article, I am calling a simple Hello World script. You can see what it looks like when Excel runs the script in Figure 1.
[Click on image for larger view.]
Figure 1. Excel has run my Hello World script.
Now that I have shown you a basic technique for calling a PowerShell script from Excel, let's see if we can pass a value from a spreadsheet to PowerShell and have PowerShell act on that value. To do so, we will have to create a PowerShell script that accepts parameters. Here is the code that I am using for my PowerShell script:
param($OriginalValue)
$NewValue=$OriginalValue + 2
Write-Host 'Original Value: ' $OriginalValue
Write-Host 'New Value: ' $NewValue
I saved this code to a file that I am calling Plus2.ps1. The file simply accepts a numerical value as a parameter, adds 2 to whatever value you entered, and then displays the sum total. You can see what the script does in Figure 2.
[Click on image for larger view.]
Figure 2. I supplied 2 as a parameter to the script and the script added 2 to it, resulting in a value of 4.
Obviously, this script doesn't really do anything useful. I simply created it as a way of illustrating the fact that it is possible to pass a value to a PowerShell script by treating the value as a parameter.
So now that we have a sample script to work with, the next step in the process is to see if Excel can pass a parameter to a PowerShell script. For this, I am going to use the same code as before, but several changes will need to be made to the line that calls the script. Here is the new command:
strCommand = "Powershell -Command ""C:\scripts\Plus2.ps1 2"""
As you can see, this command is now calling Plus2.ps1 instead of Hello.ps1. I have also added a parameter (2 in this case) immediately after the name of the script. One more change that is worth noting is that because I am using parameters instead of just opening a script, I had to remove the word File and replace it with the word Command. You can see the full script in Figure 3 and its output in Figure 4.
[Click on image for larger view.]
Figure 3. This is the macro that passes a value of 2 to my PowerShell script when the button is clicked.
[Click on image for larger view.]
Figure 4. Here is the output that is generated when the button is clicked.
The last step in the process is to make it so that pressing a button captures a value that is stored in the spreadsheet and then passes the value to a PowerShell script. This is far more useful than simply hard coding a parameter as I did in the last example.
Excel allows you to create variables that point to a range of cells. If for example, you wanted to store the contents of the A1 cell in a variable named Data, you could do so with this line of code:
Data=Range(“A1”).Value
The problem is that you can't include the Data variable in the call to the PowerShell script. Remember, PowerShell variables use a different naming convention. If you were to try using an Excel variable as a script parameter, Excel would pass the variable's name (not its contents) to the script.
The way that you get around this problem is through string manipulation. Here are the lines of code that I have used for this purpose:
Data = Range("A1").Value
Text1 = "Powershell -Command ""C:\scripts\Plus2.ps1 """
Text2 = Text1 & Data
strCommand = Text2
The first line of code captures the contents of cell A1. The next line of code stores the PowerShell -Command statement in a variable called Text1. The third line of code combines the contents of Text1 with the value stored in the Data variable. This combined string is stored in a variable called Text2. The last line of code replaces the normal PowerShell call with the contents of the Text2 variable. By manipulating strings in this way, I am able to pass the variable's contents to PowerShell as a script parameter. You can see the full script in Figure 5 and the output in Figure 6.
[Click on image for larger view.]
Figure 5. This script takes whatever value is stored in cell A1 and passes it to PowerShell as a script parameter.
[Click on image for larger view.]
Figure 6. PowerShell acts on the value that was stored in the spreadsheet.
In the interest of keeping things simple, I used a PowerShell script that simply displayed data in a message box. However, there is nothing stopping you from using a similar technique to take action on data stored in a spreadsheet. You might for example, use this type of automation to provision new user accounts based on data stored in an Excel document.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.