Error opening excel in powershell

Question:

I need to open excel file with CorruptLoad paramterer from powershell-script. But when I try to make it, I get an error Exception calling "Open" with "15" argument(s): "open method workbooks class failed". This error occurs only when I call Open with all 15 arguments. And when I try to open the same excel file with VB.net program with 15 arguments or with specifying value of named argument CorruptLoad, there is no problem!

I’m using powershell v 4.0, Office 2010 with SP2 and .NET Framework 4.5.2.

Here is my powershell code:

I have not idea why error occurs. I’ll be glad to any advices and assumptions!

Answer:

After much playing around with your PowerShell script… it’s all very odd.

Observed Behaviour

Firstly, the Open method on the Workbooks object only reports 14 parameters when $excel.Workbooks.Open.Invoke.ToString() is run. The ouptut reads:

However, there is some kind of overload method for 15 parameters because, when using the more verbose error read-out $Error[0]|format-list -force i did two test calls, the first with 15 parameters and the second with 16.

15 Parameters

16 Parameters

As you can read above, the method does support 15 parameters but not 16. However, no matter what values are supplied to the 15th parameter, it will fail to open the file.

For the record, it works with 14 or less arguments, it is only the 15th argument that throws the exception.

Conclusion

From everything that I’ve seen, I can only conclude that there is a problem with the Excel COM interop support in powershell. Supplying the 15th parameter as $missing simply should not change behaviour according to the Workbooks.Open() reference. There being a problem with Powershell’s Excel COM support is further reinforced because, when the VBA script is run as a macro, everything works as per the documentation.

Suggested Next Steps

The first work-around that comes to mind is to write the VBA script as a macro and store it in an excel file that is used specifically for running VBA scripts from the command line. It would take minimal effort to implement and it is known by testing from both the OP and myself that it will work.

If you have any difficulty triggering the macro from powershell, see Calling Excel macros from PowerShell with arguments

Source:

Error opening excel in powershell by licensed under CC BY-SA | With most appropriate answer!

Leave a Reply