Today I needed to make a nice-looking report based on a selection of data in PowerShell. I did a quick google on it and found http://pathologicalscripter.wordpress.com/out-excel/ and some code by my friend Thomas Lee (http://msdn.microsoft.com/en-us/library/bb257024(office.12).aspx) and loads of variants on theese.
However none of them worked. I quickly discovered that the first error was because I have another UI culture in my operating system than I do in Excel, and that was quickly implemented. However then I ran into a wall where the methods I called threw a “non-existing method”-exception. This after some digging was due to RPC_E_SERVERCALL_RETRYLATER error beeing thrown in COM and then generalized in .NET as a “non-existing method”-exception. I had to implement a wrapper around the call to make sure Excel was ready to handle my code. So a fresh and new variant on the Out-Excel was born.
After my Excel adventures I decided to make a similar function for word with more support for formating etc (I hate doing nice looking Excel documents as formating in excel is a bitch), and also a more wide range of formats (I could do this in excel also but why would I make a ugly table and export that as pdf?). Ended up with Out-Word.
Attached you will find my DPSE-OfficeAutomation.txt (rename to psm1) module which you can load into your POSHv2 enviroment. I have tested it only with English Office 2010 in Windows 7. I have no ideas on how it will perform on any other platform and/or office and you may use this module to your own liking. If you develop it more or distrubute it please send me a note. Thank-you!