Excel automation with Powershell

Powershell is Microsoft’s answer of decent shell scripting on the Windows platform. Equipped with the functions you would expect from VBScript, it behaves as an Unix shell script would. All commands can work from the command line, or as a set of instructions in a file.

Adding to the basic shell functions one would expect, such as file and directory manipulation, reading and writing text files, manipulating strings with regular expressions, Powershell also integrates with .NET 2.0 and higher to communicate with other functions, such as accessing the System.Windows.Forms object to make neat forms equipped with all the controls you would expect in a compiled application, or in this case: manipulating Excel workbooks.

Using Excel together with PowerShell can be very easy. Too bad the documentation is sparse on this topic, and the official documentation makes no mention of a workaround that is needed when using Excel with PowerShell on a machine which is not configured with regional settings set to EN-us. Continue reading “Excel automation with Powershell”