GetObject() from VBA

Creating a macro - Writing a Script - Using the API

GetObject() from VBA

Postby pitonyak » Mon Feb 18, 2008 6:39 pm

I want to manipulate Outlook.Application inside of OOo Basic. I can easily do this using

Code: Select all   Expand viewCollapse view
obj = CreateObject(Outlook.Application")


or

Code: Select all   Expand viewCollapse view
obj = New Outlook.Application


or

Code: Select all   Expand viewCollapse view
Dim obj = As New Outlook.Application


I would like to use something similar to the VBA method

Code: Select all   Expand viewCollapse view
obj = GetObject(, "Outlook.Application")


This method will return an existing reference to the object if it is already running (rather than creating a new window I believe).

Any thoughts?
Andrew Pitonyak
http://www.pitonyak.org/oo.php
LO and AOO on Fedora
pitonyak
Volunteer
 
Posts: 186
Joined: Sun Oct 07, 2007 9:13 pm
Location: Columbus, Ohio, USA

Re: GetObject() from VBA

Postby TerryE » Tue Feb 19, 2008 7:24 pm

The VBA documentation states that you should use the GetObject function when there is a current instance of the object or if you want to create the object with a file already loaded. If there is no current instance, and you don't want the object started with a file loaded, use the CreateObject function. If an object has registered itself as a single-instance object, only one instance of the object is created, no matter how many times CreateObject is executed. With a single-instance object, GetObject always returns the same instance when called with the zero-length string ("") syntax, and it causes an error if the pathname argument is omitted. You can't use GetObject to obtain a reference to a class created with Visual Basic.

I have scripted Outlook before but typically within the VbaProject.OTM attached to Outlook Client itself. I have done Outlook vbscripts. From what I recall the Outlook.Application binds your current Outlook client version which is Outlook.Application.11 on my PC, which invokes up C:\PROGRA~1\MICROS~2\OFFICE11\OUTLOOK.EXE. This is a single instance object within a user session. They will only run within a logged on context if you have the appropriate verion of Outlook client installed on your machine. For Server-side stuff you need to use CDO.

So the following code works fine on my PC:
Code: Select all   Expand viewCollapse view
Sub Main
  Const olFolderInbox = 6
  Dim oFactory, oApp, oSess, oFolder
  oFactory = createUnoService("com.sun.star.bridge.oleautomation.Factory")
  oApp = oFactory.createInstance("Outlook.Application")
  oSess = oApp.GetNamespace("MAPI")
  oFolder = oSess.GetDefaultFolder(olFolderInbox)
  Print oFolder.Name
End Sub


My general recommendation for developing any COM scripts (including OOo Basic through the UNO Automation bridge) is first to develop them within the "home" MS application, anf then to migrate them once working.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: GetObject() from VBA

Postby pitonyak » Tue Feb 19, 2008 8:34 pm

I translated an existing VBA macro. The macro translated easily. Simple differences like changing to use IsNull and/or IsEmpty.

Code: Select all   Expand viewCollapse view
If (x is nothing) Then


The existing macro uses GetObject to avoid creating a new window unless it is needed. I was not aware of the oleautomation factory. I will take a look at that. Knowing that this is simply a new window into the existing/running instance is useful.

Thanks for the reply!
Andrew Pitonyak
http://www.pitonyak.org/oo.php
LO and AOO on Fedora
pitonyak
Volunteer
 
Posts: 186
Joined: Sun Oct 07, 2007 9:13 pm
Location: Columbus, Ohio, USA

Re: GetObject() from VBA

Postby TerryE » Tue Feb 19, 2008 9:58 pm

A, To use this you need to have Outlook Client installed on your PC. If you have a full install of OC then you will also have the developer tools. Hit Alt-F11 to bring up the VBA IDE and F1 to bring up help; F2 to bring up the Object Browser (a compile time Xray but more featured). This will take you around the object model.

One thing I would recommend is to dump all typing, and leave all declarations as implicit variant. You will avoid all sorts of pits and traps that way — an the OOo RTL runs varaints faster anway!

Hope that the family is giving you a lot of enjoyment. :-)
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: GetObject() from VBA

Postby pitonyak » Tue Feb 19, 2008 11:33 pm

TerryE wrote:Hope that the family is giving you a lot of enjoyment. :-)

My wife really wants the baby out, but it is not due until March11.
Andrew Pitonyak
http://www.pitonyak.org/oo.php
LO and AOO on Fedora
pitonyak
Volunteer
 
Posts: 186
Joined: Sun Oct 07, 2007 9:13 pm
Location: Columbus, Ohio, USA

Re: GetObject() from VBA

Postby ms777 » Wed Feb 20, 2008 12:50 am

Hi,

just one addition for those readers who want to use GetObject for non single instance objects: It is possible to use the MSScriptControl as a wrapper. Example here dumps the IP configuration into a messagebox. You could also directly return oWMIService from ExecuteQuery, the lack of a for ... each statement in OOBasic prohibits it here.

ms777

Code: Select all   Expand viewCollapse view
Sub Main
Dim aasDesc() as String
Dim aasIP() as Any

call ExecuteQuery(asDesc, aasIP)

s = "IPConfig: " & Chr(10)
for k=LBound(asDesc) to UBound(asDesc)
  sDesc = asDesc(k)
  s = s & sDesc & Chr(10)
  asIP = aasIP(k)
  for j=LBound(asIP) to UBound(asIP)
    s = s & "  " & j & ": " & asIP(j) & Chr(10)
    next j
  next k


msgbox s
End Sub

sub ExecuteQuery(DescrArray as Any, IPAddArray as Any)
oleService = createUnoService("com.sun.star.bridge.OleObjectFactory")
VBScript= oleService.createInstance("MSScriptControl.ScriptControl")
VBScript.Language = "VBScript"

s = ""   
s = s + "Public DescrArray()" + Chr(10)
s = s + "Public IPAddArray()" + Chr(10)
s = s + "Set oWMIService = GetObject(""winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2"")" + Chr(10)
s = s + "Set IPConfigSet = oWMIService.ExecQuery(""Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE"")" + Chr(10)

s = s + "ReDim DescrArray(IPConfigSet.Count-1)" + Chr(10)
s = s + "ReDim IPAddArray(IPConfigSet.Count-1)" + Chr(10)
s = s + "k = 0" + Chr(10)
s = s + "For Each IPConfig in IPConfigSet" + Chr(10)
s = s + "  DescrArray(k) = IPConfig.Description" + Chr(10)
s = s + "  IPAddArray(k) = IPConfig.IPAddress" + Chr(10)
s = s + "  k = k + 1" + Chr(10)
s = s + "  Next" + Chr(10)

VBScript.ExecuteStatement(s)
DescrArray = VBScript.CodeObject.DescrArray
IPAddArray = VBScript.CodeObject.IPAddArray
End sub
ms777
Volunteer
 
Posts: 131
Joined: Mon Oct 08, 2007 1:33 am

Re: GetObject() from VBA

Postby pitonyak » Wed Feb 20, 2008 5:18 am

Very interesting... I would not have expected this usage...
Andrew Pitonyak
http://www.pitonyak.org/oo.php
LO and AOO on Fedora
pitonyak
Volunteer
 
Posts: 186
Joined: Sun Oct 07, 2007 9:13 pm
Location: Columbus, Ohio, USA

Re: GetObject() from VBA

Postby TerryE » Wed Feb 20, 2008 2:58 pm

I rembered that Andreas introduced the New syntax with OOo 2.0 as syntactic sugar for a call to createUnoService( "com.sun.star.bridge.OleObjectFactory" ).createInstance. I also looked an old OOoForum post of mine where I referred to this, and refreshed my memory on its details. The feature / functionality of this control is rich. (Incidentally it runs about 20-30 times as fast as the OOoBasic RTL.)

If you want to run non-trivial code you need to register functions and subroutines. Functions can return a value is called via a run statement. You can also access public variables (which may be variant array structures), per the following code example. Of course this only works on MS OSs.
Code: Select all   Expand viewCollapse view
Option Explicit
Option Compatible
Sub X()
Dim a As ScriptControl, oMod, i, myZ

Set a = New ScriptControl
a.Language = "VBscript"
oMod = a.Modules.Add("Mod1")
oMod.AddCode ( _
    "Public x, y, z" & vbCr & _
    "Sub Fred (a): x = 2*a : End Sub")
oMod.AddCode ("Function sqrIt(a): sqrIt = a*a : End Function")
oMod.AddCode ("Sub save(a,b): z = array (a,b) : End Sub")

For i = 1 To oMod.Procedures.Count
   Print oMod.Procedures.Item(i).Name
Next i

Print oMod.Run("sqrIt", 12.4)
oMod.Run("Fred", 10.4)
Print oMod.CodeObject.x
oMod.ExecuteStatement "Call Fred(23): y = 123 + x + sqrIt(3)"
Print oMod.CodeObject.y
oMod.Run("save","Hello","there")
myZ = oMod.CodeObject.z
Print myZ(1)
End Sub


To get more details do a google on "script control site:microsoft.com"
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 11 guests