PowerShell from Excel (oh oh, VMware again)

VMware are a competitor and so when things go wrong for them I'll point it out (and to answer Nick, a regular commenter: No, that's not FUD. Saying "VMware had a failure here, so you can extrapolate from that to unspecified future failures" would be spreading Fear Uncertainty and Doubt. I might lapse into that now and then, but aspire not to. If I do then I expect to get a tough time).

But competitors do good things sometimes, and I've praised them for their use of PowerShell before now. Using PowerShell was good for them (it's much easier for them to develop a PowerShell snap-in than a raft of command line tools), and it's good for customers (no need to learn special purpose tools).

Now they've done another clever thing with PowerShell: not rocket science, but great application of sense. Sometimes it makes sense to have your user interface in an Office application, like Excel (with the business logic implemented with Macros).  But sometimes this gives rise to tasks to be carried out from PowerShell. How do you knit together PowerShell scripts and Excel Macros (or any other scripting language).  Provided that a language can write files and invoke other programs you can do it. Someone pointed out this page on the VI Tool kit blog , it says

If an ESX host you want to manage doesn't appear in VirtualCenter, you need to add it. This is a bit tricker than reconnecting since there's no inventory in VirtualCenter to tell you the IP addresses of all the hosts you use, and you also need to know a host's password in order to add it. This is another case where entering things in a spreadsheet can really speed things up

Not only is there a video of the spreadsheet in use but it's available for download as well, so I had a look and (like so many good ideas) the code is remarkably simple, start by opening a file

     Handle = FreeFile    Open "script.ps1" For Output Access Write As #Handle

Then use Print # to output the lines of script to it

     Print #Handle, "add-pssnapin VMware.VimAutomation.Core" & vbCrLf

And when you've print #ed the whole script, close the file and run it

     Close #Handle    Call Shell("powershell -command .\script.ps1", vbNormalFocus)

Not exactly Rocket science, but smart use of the right tool for the job; I can't find a name on the blog post, but whoever you are a tip of the hat is due to you.

 

Technorati Tags: Excel,PowerShell,VMware