19

I wrote a PowerShell script that opens an excel workbook and runs a macro. When I run that script from PS console, or even from command line using powershell.exe script.ps1, it just works. When I set up a task from the windows task scheduler, it raises an exception about that Excel file, saying that it either does not exist or is already in use.

The file exists for sure, as the script ran fine from the command line, and I'm positive it is not in use.

I tried to move the Excel file to a local and non-privileged area, to avoid network trust/admin privilege issues. The task still runs with highest privileges. The path has no spaces, or special characters.

When I try to access to the file using a File system object, there is no errors even when run from the scheduler, so I guess it is specific to Excel.Application.Workbooks.Open("..") method.

What should I consider now?

TylerH
  • 631
zrz
  • 323

4 Answers4

55

Create these two folders:

32Bit: 

C:\Windows\System32\config\systemprofile\Desktop

64Bit:

C:\Windows\SysWOW64\config\systemprofile\Desktop

Excel needs these folders if it's not run interactively. Create both folders even if you are on a 64-bit OS.

lcarl
  • 3
eric
  • 551
12

It's likely a DCOM permissions issue. Automating Excel is sometimes fraught with peril...

The only way I've found around issues such as this is to set Excel to run as a specific user through DCOM permissions.

  1. Open Component Services (Start -> Run, type in dcomcnfg)
  2. Drill down to Component Services -> Computers -> My Computer and click on DCOM Config
  3. Right-click on Microsoft Excel Application and choose Properties
  4. In the Identity tab select This User and enter the ID and password of an interactive user account (domain or local) and click Ok

Keeping it as the interactive user or the launching doesn't work with the task scheduler unfortunately, even when setting the task up to run under an account that has admin access to the machine.

squillman
  • 8,551
5

When setting DCOM permissions, if Microsoft Excel doesn't appear in dcomcnfg try mmc comexp.msc /32

reference

cmo
  • 69
0

In my case the task would complete successfully when I did both of the following:

  1. Checked 'run with highest privileges' in the task settings
  2. Filled in the 'Start In (optional)' field in the task's action

Follow Up:

Although my script was opening the Excel file, running the macros, saving, closing the file, and completing successfully.... the external data was NOT refreshing.

To fix this, I had to change the identity of the user in Microsoft Excel DCOM (thank you squillman):

  1. Open Control Panel
  2. Administrative Tools
  3. Component Services
  4. Computer
  5. My Computer
  6. DCOM Config
  7. Microsoft Excel Application
  8. Properties
  9. Identity Tab
  10. Select this user
  11. Enter username and password
  12. Click Apply
  13. Ok

Task scheduler now opens, updates, saves, and closes my file. Also, the task completes successfully.

Note:

When I try to work in Excel, I get the error: 'Cannot use object linking and embedding.' and files won't open. This all goes away if I go back into DCOM Excel Properties, Identity and click 'The launching user'.

Amy
  • 1