PowerShell Direct - Unable to open Excel workbook with Excel.Application ComObject

[Tom Phillips]

Hello All,

Hoping someone may be able to enlighten me or provide an alternative.

Running in a local PS session on a Win10 client with Office 2019 installed, I am able to run these commands, open, and use content from an xlxs file:

$file = "C:\TestFiles\Test.xlsx"
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($file)

<...more code here but not relevant to the conversation....>

When I try to use the same command using PowerShell Direct on the host where the VM is running, I get this:

<Enter-PSSession -VMName WIN10.....command already run and connected. User account connecting is a local administrator in the Workgroup joined VM...>

[WIN10]: PS C:\Users\TestUser\Documents> $file = "C:\TestFiles\Test.xlsx"
[WIN10]: PS C:\Users\TestUser\Documents> $excel = New-Object -ComObject Excel.Application
[WIN10]: PS C:\Users\TestUser\Documents> $workbook = $excel.Workbooks.Open($file)
Microsoft Excel cannot access the file 'C:\TestFiles\Test.xlsx'. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
At line:1 char:1
+ $workbook = $excel.Workbooks.Open($file)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Yes, the file is there. No it isn't opened by another program. Not trying to save. Again, the commands work over and over when run in a local PS session.

I'm leaning toward using the ImportExcel PS module but I'm not sure it will allow me to find formulas in a spreadsheet (I've not seen where that can be done).

Regardless, I've repro'd this on three different Hyper-V servers and it seems like there is a problem with ComObject. I'm not versed well enough to know much more than that. I've read about DCOM Config and allowing permissions to Excel but there are no entries for either Excel or a CLSID for Excel.

This is simple for anyone to repro. Just have a Win10 VM with Office and create an Excel file. You don't need to do anything with the file. This all falls apart in PS Direct when the workbook is being opened.

Thank you in advance for any help.

Tom

[#2 · Accepted solution]

@Tom Phillips 

I hate to be the one to answer my own question, but that is what's happening.

After what I thought was a thorough three day search, I happened across a similar solution used for another like situation.

I found that going into DCOM Config and, in the properties of the Microsoft Excel > Identity tab, change the setting to "The interactive user".

Since this is not a production machine and is being used for testing, I have not looked any deeper than solving my immediate problem. To those who see a security issue, again, this was not a consideration for me in this case. If it was production, I would spend more time considering the collateral of this change.

[#3]

Tom, I'm having the same issue. Let me try this solution and get back to you

[#4]

Thanks Tom for this. I created an account in the Tech Microsoft Community just to send my gratitude for sharing your findings. I have the same issue and updating the DCOM config worked for me.