I was recently asked to automate the process of parsing data from our Liebert UPS Administration web pages and dump it to an Excel spreadsheet so we could do some further analysis. We have a Windows 2008 x64 server with Office 2003 installed (for some legacy scripts). I tested this script on Office 2007 as well as Office 2003 and as it turns out this worked out quite well during the development stage. I create my Excel Com Object, open the spreadsheet, and select the worksheet:
$Excel = New-Object -Com Excel.Application
$Books = $Excel.Workbooks.Open($SpreadsheetLocation)
$Sheet = $Books.Worksheets.Item($WorkbookName)However, once I scheduled it using Scheduled Tasks on Windows 2008 x64 the proverbial you know what hit the fan and I was greeted with this helpful error message when calling the Open method.
Exception calling "Open" with "1" argument(s): "Open method of Workbooks class failed"
Argh. As it turns out scheduling the automation of Excel is not supported by Microsoft. There are workarounds that involve creating the automation scripts as Excel macros, but I wanted to utilize the power of PowerShell and keep the automation files separate from the Excel data.
Doing some searching online led me to the answer. As it turns out, Excel relies on the existance of the Desktop folder and when a Scheduled Task runs it uses the System profile. So, In order to resolve this issue I simply created the following folder:
C:\Windows\SysWOW64\config\systemprofile\Desktop
I suppose on Window 2008 32-bit the folder would be:
C:\Windows\System32\config\systemprofile\Desktop
Once the folder was created my script ran just fine. Hope this helps some you out there!
