0

I am considering to switch from Excel to Open Office (actually to Lotus Symphony).
I have many Excel files with VBA macros that connect to CAD, CAM and other applications, and to my own COM objects.
The function GetObject doesn't exist in Open Office Basic, but I was able to make a function that uses the GetObject function of a scripting object.

The problem is that I can access some properties/methods of that object, but not others.

Below there is an example that connects to Excel, gets the ExcelApp.Workbooks.Count property, and crashes when it tries to get the ExcelApp.Workbooks(I).Name property. In this example I use Excel as the external application just because it's common and easy to understand.

I noticed that the Open Office VBA IDE is poorer than the Excel VBA IDE: it's impossible to change the value of a variable, evaluate an expression, change the current statement, no intellisense, etc.
Is it so simple because it's Lotus Symphony, and installing another version of Open Office things would get better?
Is it so simple because it's VBA, and using another language like Python things would get better?

Here is the code:

Code:

Public ExcelApp As Object 

Sub Test 
  GetRunningExcel 
  If ExcelApp Is Nothing Then 
    MsgBox "Excel is not running" 
  Else 
    Dim S As String, I As Integer, N As Integer 
    N = ExcelApp.Workbooks.Count 
    For I = 1 To N 
      S = S & Chr(10) & ExcelApp.Workbooks(I).Name 
    Next I 
    S = "Excel has " & N & " open documents" & S 
    MsgBox S 
  End If 
End Sub 

Function GetRunningExcel() 
  Dim oleService 
  Dim VBScript 
  Dim S as string 
  oleService = createUnoService("com.sun.star.bridge.OleObjectFactory") 
  VBScript= oleService.createInstance("MSScriptControl.ScriptControl") 
  VBScript.Language = "VBScript" 
  S = S + "Public O" + Chr(10) 
  S = S + "Set O = GetObject(, ""Excel.Application"")" + Chr(10) 
  On Error Resume Next 
  VBScript.ExecuteStatement(S) 
  Set ExcelApp = VBScript.CodeObject.O 
  On Error Goto 0 
End Function
stenci
  • 8,290
  • 14
  • 64
  • 104
  • 1
    OpenOffice's Basic implementation understands UNO objects natively, but not COM objects. That's why you have to use that bridging object. I'm not sure whether that UNO<->COM bridge is full-fidelity, or if you'll always lose something in translation. – Martin Feb 14 '13 at 16:37
  • I installed the latest OO 3.4.1, and it's much better than the old version distributed with IBM Lotus Symphony. Still, it is doesn't look good enough for me. The bad integration with COM and the poor IDE make it impossible to port my Excel addins (thousands of lines of code, dozens of forms and classes) to OO. I will see if it is worth creating a few macros that write to file and call an external program, wait for it to be done, and import the result from a file. Then it would be matter of porting the old macros to an external .net application. – stenci Feb 14 '13 at 18:10

0 Answers0