.net 如何将参数数组传递给COM对象？

ix0qys7i 于 2022-12-20 发布在 .NET

关注(0)|答案(1)|浏览(152)

similar questions是关于传递参数数组的，它们在托管代码中工作。使用Microsoft.Office.Interop.Excel时，您可以使用Application.Run方法运行Excel文件中的宏。该方法的签名显示它采用宏名称和传递给Excel VBA过程的任意参数列表。这看起来和行为都像参数数组，但实际上不是。
当你把一个参数数组传递给.Run的参数部分时，你会得到：
System.Runtime.InteropServices.COMException:'参数不是可选的。（HRESULT异常：0x8002000F（显示_E_参数选项））'
这表明参数数组未正确传递，可能是由于COM互操作没有ParamArray类型。
如何将任意长度的ParamArray扩展为可以传递给COM对象的任意参数列表？
示例代码：

''' <summary>
    ''' Run an Excel macro silently in the background. Quits at the end.
    ''' </summary>
    ''' <param name="excelFileName">Full path to Excel file.</param>
    ''' <param name="macroName">The macro you want to run.</param>
    ''' <param name="args">Arguments to pass into that macro procedure.</param>
    Public Shared Sub RunMacro(excelFileName As String, macroName As String, ParamArray args As String())
        ' Create new Excel instance
        Dim excelApp = New Application With {
            .Visible = True,
            .EnableEvents = False   ' Suppress the Workbook_Open event
        }

        Dim excelBooks As Workbooks = excelApp.Workbooks

        ' Open the Excel workbook
        Dim thisWorkbook As Workbook = excelBooks.Open(excelFileName)

        ' Hide the window while the macro runs
        excelApp.Visible = False

        ' Run the VBA procedure.
        excelApp.Run(macroName, args)  ' <-- Throws because it can't accept ParamArray

        ' Cleanup
        thisWorkbook.Close(SaveChanges:=False)
        excelApp.Quit()
    End Sub

.net

来源：https://stackoverflow.com/questions/74720704/how-can-i-pass-a-parameter-array-to-a-com-object

1条答案

按热度按时间

e0uiprwp1#

我发现，当面对不可能的情况时，答案往往是“不要那样做”。
.Run的互操作方法不会接受ParameterArray，也没有办法动态传递参数。因此，我们需要一个不同的方法。在COM互操作的情况下，您可以手动.InvokeMember，它接受参数作为对象数组。这让我们可以绕过.Run接受一个强制字符串和最多30个参数的事实，因为它允许我们一次性将其作为数组传递。
最终方法如下所示：

''' <summary>
''' Run an Excel macro silently in the background. Quits at the end.
''' </summary>
''' <param name="excelFileName">Full path to Excel file.</param>
''' <param name="macroName">The macro you want to run.</param>
''' <param name="args">Arguments to pass into that macro procedure.</param>
Public Shared Sub RunMacro(excelFileName As String, macroName As String, ParamArray args As String())

    ' Make an array of object of string for the invoker
    Dim argumentArray = args.ToArray()
    Dim objectArray = New Object(argumentArray.Length) {}
    objectArray(0) = macroName
    If argumentArray IsNot Nothing Then argumentArray.CopyTo(objectArray, 1)

    Dim excelApp As Application = Nothing
    Dim excelBooks As Workbooks = Nothing
    Dim thisWorkbook As Workbook = Nothing
    Try
        ' Create new Excel instance
        excelApp = New Application With {
            .Visible = False,       ' Hide the window while the macro runs
            .EnableEvents = False   ' Suppress the Workbook_Open event
        }

        ' Open the Excel workbook
        excelBooks = excelApp.Workbooks
        thisWorkbook = excelBooks.Open(excelFileName)
        excelApp.GetType().InvokeMember(
        "Run", Reflection.BindingFlags.Default Or Reflection.BindingFlags.InvokeMethod, Nothing, excelApp, objectArray
        )
    Finally ' Cleanup
        If thisWorkbook IsNot Nothing Then
            thisWorkbook.Close(SaveChanges:=False)
            Marshal.ReleaseComObject(thisWorkbook)
        End If

        If excelBooks IsNot Nothing Then Marshal.ReleaseComObject(excelBooks)

        If excelApp IsNot Nothing Then
            excelApp.Quit()
            Marshal.ReleaseComObject(excelApp)
        End If
    End Try
End Sub

赞(0）回复(0）举报 2022-12-20

我来回答

.net 如何将参数数组传递给COM对象？

1条答案

相关问题

热门标签

最新问答