在PowerShell中打开excel的错误

问题描述

我需要使用来自powershell-script的 CorruptLoad paramterer打开excel文件。但是当我尝试使用它时，我收到错误异常调用打开与15参数：打开方法工作簿类失败。当我使用所有15个参数调用打开时，仅发生此错误。当我尝试打开相同的excel文件与 VB.net 程序与15个参数或指定的值的命名参数 CorruptLoad ，没有问题！

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!

我正在使用 powershell v 4.0 ，Office 2010与SP2和 .NET Framework 4.5.2 。

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

这是我的 powershell 代码：

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
try
{
    $missing = [System.Type]::Missing
#   $wb = $excel.Workbooks.Open("d:\temp\start_instrument.xls", $missing, $missing, $missing, $missing,
#                               $missing, $missing, $missing, $missing, $missing,
#                               $missing, $missing, $missing, $missing, $missing)

#   $wb = $excel.Workbooks.Open("d:\temp\start_instrument.xls", $missing, $missing, $missing, $missing,
#                               $missing, $missing, $missing, $missing, $missing,
#                               $missing, $missing, $missing, $missing, 1)

    $XlCorruptLoad = "Microsoft.Office.Interop.Excel.XlCorruptLoad" -as [type]

    $wb = $excel.Workbooks.Open("d:\temp\start_instrument.xls", $missing, $missing, $missing, $missing,
                                $missing, $missing, $missing, $missing, $missing,
                                $missing, $missing, $missing, $missing, $XlCorruptLoad::xlRepairFile)
}
catch
{
    Write $Error[0].ToString()
}

# some stuff

if ($excel -ne $null)
{
    $excel.Quit()

    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
    $excel = $null
}

[System.GC]::Collect() | Out-Null
[System.GC]::WaitForPendingFinalizers() | Out-Null

我不知道为什么会发生错误。我很高兴有任何建议和假设！

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

推荐答案

在你的PowerShell脚本中玩了很多，这一切都非常

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

首先，Workbooks对象上的Open方法仅在<$运行c $ c> $ excel.Workbooks.Open.Invoke.ToString（） ouptut显示为：

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

Workbook Open (string, Variant, Variant, Variant, Variant, Variant, Variant, Variant, Variant, Variant, Variant, Variant, Variant, Variant, Variant)

然而，有一些重载方法15个参数，因为当使用更详细的错误读取 $ Error [0] | format-list -force 我做了两个测试调用，第一个有15个参数，第二个为16.

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参数

Exception             : System.Runtime.InteropServices.COMException (0x800A03EC): Unable to get the Open property of the Workbooks class
                           at System.Management.Automation.Interpreter.MethodInfoCallInstruction.InvokeInstance(Object instance, Object[] args)
                           at System.Management.Automation.Interpreter.DynamicInstructionN.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

16参数

Exception             : System.Management.Automation.MethodException: Cannot find an overload for "Open" and the argument count: "16" --->
                        System.Reflection.TargetParameterCountException: Cannot find an overload for "Open" and the argument count: "16"
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception
                        exception)
                           at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

如上所述，该方法支持15个参数，但不支持16。但是，无论提供给第十五个参数的值如何，都将无法打开e文件

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.

对于记录，它可以使用14个或更少的参数，它只是引发异常的第15个参数。

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

从我看过的一切，我只能得出结论，在PowerShell中的Excel COM互操作支持有问题。根据。 Powershell的Excel支持问题进一步加强，因为当VBA脚本作为宏运行时，一切都按照文档运行。

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.

首先解决的问题是将VBA脚本编写为一个宏，并将其存储在专门用于从命令运行VBA脚本的excel文件中线。实现这一点并不需要很少的努力，而且OP和我自己都会通过测试来知道它将会工作。

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.

如果您有任何困难从PowerShell触发宏，请参阅

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

这篇关于在PowerShell中打开excel的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！