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?

Improve this question
3
  • 1
    What user account is used to run the scheduled task?
    – Massimo
    Apr 8, 2013 at 17:19
  • I used the domain administrator account, and it seemed to be the problem. Using the domain administrators group worked. But I now had to find why the DCOM Access authorization has an issue with the domain administrator account.
    – zrz
    Apr 9, 2013 at 13:19
  • tl;dr: The workaround in eric's answer is effective, but it is unsupported - see this Microsoft support article. Better to look for alternatives such as the DocumentFormat.OpenXml nuget package.
    – mklement0
    Feb 3, 2020 at 15:37

4 Answers 4

Reset to default
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.

Improve this answer
4
  • 1
    Thanks for pointing out dcomcnfg. I had to manually add the Excel Application into the Registry to be able to see it into dcomcnfg, then I tried to change identity settings but it didn't work either. From dcomcnfg, I looked into Access Authorization: the Domain Administrator was on the list and had local and remote access, but there was a little red sign (white cross within a red disk) over its icon. I have no idea why but somehow there is a problem with the Domain Administrator account. Adding the Domain Administrators group account and running the task from that group is a workaround.
    – zrz
    Apr 9, 2013 at 13:16
  • 1
    Microsoft doesn't support running Office COM components in non-interactive sessions - see support.microsoft.com/en-us/help/257757/…. eric's answer is a currently effective workaround, but it is unsupported.
    – mklement0
    Feb 3, 2020 at 15:30
  • 1
    This answer could be improved by covering how to add Microsoft Excel Application entries when they are missing from DCOM Config entirely. See docs.microsoft.com/en-us/archive/blogs/… Also, FWIW, this did not solve my problem. An Excel process still appears in Task Manager, but the specified file never opens/runs.
    – TylerH
    Apr 7, 2020 at 17:12
  • Likewise after making this change it prevented me from running or even opening Excel VBA files/ the VBA editor at all, causing CTDs whenever I attempt it.
    – TylerH
    Apr 7, 2020 at 17:19
52

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.

Improve this answer
5
  • 3
    I just want to add in that Microsoft does not endorse running any Office component non-interactively, but it is possible to get around it in many cases.
    – flindeberg
    Dec 2, 2014 at 8:52
  • 13
    This is some seriously black magic. I don't know what's scarier - that Excel needed this folder in this scenario or that someone knew that Excel needed this folder in this scenario.
    – Rich C
    Oct 27, 2015 at 19:27
  • 1
    To emphasize @flindeberg's point: This workaround is effective, but it is unsupported - see this Microsoft support article.
    – mklement0
    Feb 3, 2020 at 15:31
  • Creating these folders and assigning Full Control for my account (which is the account running the scheduled task as well) all the way down from system32/SysWOW64 to the Desktop folders still did not work for me.
    – TylerH
    Apr 7, 2020 at 17:21
  • 1
    This post has saved me some time at least twice over the years. Thank you!
    – planetregin
    Sep 15, 2022 at 18:55
4

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

reference

Improve this answer
1
  • This caused DCOM Config to open for me and asked me if I wanted to registry a couple entries, but Excel was not among them.
    – TylerH
    Apr 7, 2020 at 17:20
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'.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .