Calling an Excel Add-In method from C# application or vice versa
- by Jude
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
Any help would be appreciated.
