从C＃应用程序调用Excel加载项方法，反之亦然 [英] Calling an Excel Add-In method from C# application or vice versa

问题描述

我在bas文件中有一个带有公共方法的Excel VBA加载项。此方法当前创建一个VB6 COM对象，该对象存在于正在运行的VB6 exe / vbp中。 VB6应用程序加载数据，然后Excel加载项方法可以调用VB6 COM对象上的方法以将数据加载到现有的Excel xls中。

我们已经将VB6应用程序转换为C＃。

我的问题是：用C＃/模仿这种行为的最佳/最简便方法是什么？ NET应用程序？
我想我可能无法通过加载项方法将.NET应用程序中的数据提取到Excel中，因为.Net应用程序需要在加载数据的情况下运行（因此不能单独使用C＃类库）。也许我们可以通过从C＃代码访问VBA加载项方法将数据从.NET推送到Excel？

以下是访问VB6应用程序的现有VBA方法：

I have an Excel VBA add-in with a public method in a bas file. This method currently creates a VB6 COM object, which exists in a running VB6 exe/vbp. The VB6 app loads in data and then the Excel add-in method can call methods on the VB6 COM object to load the data into an existing Excel xls. This is all currently working.
We have since converted our VB6 app to C#.
My question is: What is the best/easiest way to mimic this behavior with the C#/.NET app? I'm thinking I may not be able to pull the data from the .NET app into Excel from the add-in method since the .Net app needs to be running with data loaded (so no using a stand-alone C# class library). Maybe we can, instead, push the data from .NET to Excel by accessing the VBA add-in method from the C# code?
The following is the existing VBA method accessing the VB6 app:

Public Sub UpdateInDataFromApp()
   Dim wkbInData As Workbook
   Dim oFPW As Object
   Dim nMaxCols As Integer
   Dim nMaxRows As Integer
   Dim j As Integer
   Dim sName As String
   Dim nCol As Integer
   Dim nRow As Integer
   Dim sheetCnt As Integer
   Dim nDepth As Integer
   Dim sPath As String
   Dim vData As Variant
   Dim SheetRange As Range

   Set wkbInData = wkbOpen("InData.xls")

   sPath = g_sPathXLSfiles & "\"

   'Note:  the following will bring up fpw app if not already running
   Set oFPW = CreateObject("FPW.CProfilesData")

   If oFPW Is Nothing Then
      MsgBox "Unable to reference " & sApp
   Else
          .
          .
          .      
      sheetCnt = wkbInData.Sheets.Count 'get number of sheets in indata workbook
      For j = 2 To sheetCnt 'set counter to loop over all sheets except the first one which is not input data fields
          With wkbInData.Worksheets(j)
             Set SheetRange = .UsedRange
          End With
          With SheetRange
             nMaxRows = .Rows.Count    'get range of sheet(j)
             nMaxCols = .Columns.Count 'get range of sheet(j)
             Range(.Cells(2, 2), .Cells(nMaxRows, nMaxCols)).ClearContents 'Clears data from data range (51 Columns)
             Range(.Cells(2, 2), .Cells(nMaxRows, nMaxCols)).ClearComments
          End With
          With oFPW 'vb6 object
             For nRow = 2 To nMaxRows ' loop through rows
                sName = SheetRange.Cells(nRow, 1) 'Field name
                vData = .vntGetSymbol(sName, 0)   'Check if vb6 app identifies the name

                nDepth = .GetInputTableDepth(sName) 'Get number of data items for this field name from vb6 app
                nMaxCols = nDepth + 2 'nDepth=0, is single data item
                For nCol = 2 To nMaxCols 'loop over deep screen fields
                   nDepth = nCol - 2     'current depth
                   vData = .vntGetSymbol(sName, nDepth) 'Get Data from vb6 app
                   If LenB(vData) > 0 And IsNumeric(vData) Then  'Check if data returned
                      SheetRange.Cells(nRow, nCol) = vData   'Poke the data in
                   Else
                      SheetRange.Cells(nRow, nCol) = vData   'Poke a zero in
                   End If

                Next 'nCol
             Next 'nRow
          End With
          Set SheetRange = Nothing
      Next 'j

  End If

  Set wkbInData = Nothing
  Set oFPW = Nothing
Exit Sub
    .
    .
    .
End Sub  

任何帮助将不胜感激。

推荐答案

http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx

这篇关于从C＃应用程序调用Excel加载项方法，反之亦然的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！