3

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.

ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
Singularity20XX
  • 321
  • 5
  • 20
  • 1
    Still confused - you want to write the contents of a module to a text file (or just export the .bas file?) and then import it into another Office app? or a non-Office app? – SierraOscar Oct 19 '15 at 19:11
  • Which version of Reflection are you using? Is it 2011 or after or something prior to that? I'm not sure I fully understand what you are trying to do, but I've had a lot of luck with C# that interacts with both Excel and Reflection (2011). – Hambone Oct 19 '15 at 21:35
  • Hambone: It is 2011. Basically; you have many more methods and properties to work with if you are using Reflections VBE than you do if you are controlling Reflections from Excel's VBE. Per Attachmate's website, many of the methods and properties available in various reference file's for OLE automation between Excel/Reflections don't actually work (EXTRACOM for instance is the library reference file I use because it is the best choice). When you say C# are referring to one of said file types used with to control Reflection from Excel? – Singularity20XX Oct 21 '15 at 17:34
  • @ Macro Man; No. This is actually what I am already doing and what I am trying to avoid. Currently I write a .bas file with VBA compatible to the Reflection VBE. Then I use sendkeys method from Excel to have the Reflections VBE import said file as a module. It isn't a reliable method, hence why I have come to the conclusion that the only way to accomplish what I am trying to do is via VBE to VBE OLE automation. Example: Using Excel's VBE to get Access's VBE to import a module. Only in my case I am not using an Office application per se, I am using Reflection (3270 emulator) – Singularity20XX Oct 21 '15 at 17:44

1 Answers1

0

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:

  1. 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.

  2. 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
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60