If you need to automate a PowerShell script, either with a SQL Agent Job or the Windows Task Scheduler. you will properly run into the issue of: running the script via ISE or PowerShell works. But when you try to automate it running it fails. I found the solution near the end of this post

http://social.technet.microsoft.com/Forums/windowsserver/en-US/aede572b-4c1f-4729-bc9d-899fed5fad02/run-powershell-script-as-scheduled-task-that-uses-excel-com-object?forum=winserverpowershell

Solution Summary:

On a 32 bit systems Add a folder called “Desktop” to:

C:\Windows\System32\config\systemprofile\

On a 64 bit systems Add a folder called “Desktop” to:

C:\Windows\System32\config\systemprofile\

C:\Windows\SysWOW64\config\systemprofile\

Advertisements