I am familiar with OLE Automation and controlling other applications from a given VBA IDE / VBE (particularly Excel's VBE). While I know it's possible to use the SendKeys method as a way to essentially accomplish what I am trying to do, I feel it can be unreliable in certain situations (not to mention a little sloppy).
In a nutshell, I am attempting to write a VBA module to a text file from within Excel (already figured this one out) and then have a target application's VBA IDE import the module and execute the code.
My primary reasoning for this is that the reference libraries available to Excel in regards to said target application have limitations (while, as one would expect, the target application's VBA IDE has much more properties and methods to work with as its reference libraries are specific to the application itself, for obvious reasons).
Any thoughts on how to best accomplish this?
Would it be better to just create a custom COM reference or tweak and existing one (I would assume this is fairly difficult as I am not familiar with C# or Visual Studio)?
(Note: In case you were wondering, I am working with Reflection Sessions (IBM, for Windows), and have pretty extensive exposure to the primary COM I am working with (EXTRACOM). Within the Reflection Sessions VBE there are methods such as .GetFieldText (which will return the entire field name regardless of where the application cursor is placed on a given field. This can be much more useful than EXTRACOM's .GetString which requires the programmer to specify field length first and cursor position). Another property method: .GetFieldColor (which will return a numerical code for a field's color, a property/method that EXTRACOM (Excel's reference file for Reflection) lacks.
It's not clear to me what the OP is actually trying to do here.
It seems that the Reflection Type Libraries aren't reliable when called from outside of Reflection, so the solution would seem to involve using Application.Run or automating the VBE.
There are 2 ways to achieve that:
Have reflection call VBA functions in an instance of Excel (the example I've shown here), but if you can get a reference to the Reflection VBE, you could invert this and invoke Reflection VBA from Excel.
Have Reflection export the modules from Excel's vbProject(s), and then import them into the Refelction vbProject(s).
Fortunately, VBA makes both approaches possible. For both approaches, you'll need to add references to Excel and Visual Basic for Applications Extensibility
Using Application.Run to call functions in another VBA host
Using Application.Run, we can call functions in Excel projects (and we can even step from one VBE to the other while debugging. We can pass arguments, and we can receive the return values.
Sub CallExcelUDFFromNonExcelHost()
'Get an existing instance of Excel
Dim appXL As Excel.Application
Set appXL = GetObject(, "Excel.Application")
'Get the already opened Excel workbook
Dim wbk As Excel.Workbook
Set wbk = appXL.Workbooks("MyExcelFunctions.xlsm")
'Call the function in the Excel workbook
Dim result
result = appXL.Run(wbk.VBProject.Name & ".MyFunction", "Some Argument")
End Sub
Automating the VBE of an external VBA host I don't have Reflection, so here my code is running in Access, and importing a module from Excel.
Sub ImportVBAModuleFromOtherIDE()
'Get an existing instance of Excel
Dim appXL As Excel.Application
Set appXL = GetObject(, "Excel.Application")
'Get the already opened Excel workbook
Dim wbk As Excel.Workbook
Set wbk = appXL.Workbooks("MyExcelFunctions.xlsm")
'Export a module from Excel
wbk.VBProject.VBComponents("Module1").Export "C:\Temp\Module1.bas"
'Import the module into the Access project
Application.VBE.VBProjects("Database11").VBComponents.Import "C:\Temp\Module1.bas"
End Sub