OLE – a – Introduction

I treat the terms  OLE and automation as equal! Except OLE is easier to spell!

I did not originally intend to support OLE, but I was driven by the wish to exchange data between SQLite and Excel. I decided not to try to read/write Excel files, so when I found out about OLE as a data exchange mechanism I only intended to support Excel.

However the code needed to access  Excel was trivially reusable for any OLE compatible application.

You will find a small selection of scripts (some OLE some not) here .

I make no promises, but if you send me an interesting script, I will consider adding it to the collection. Again, no promises, if I feel that the script is dangerous or does something I consider questionable then I shall not include it.


Embedded in the executable are a number of automation examples, they include the following applications (mainly Microsoft):

  • Excel
  • Word
  • PowerPoint
  • OutLook
  • InternetExplorer
  • FileScriptingObject
  • WMPlayer
  • WShell
  • WScript
  • ADODB
  • XMLHTTP
  • SAPI.SPVOICE

OLE also works with many non-Microsoft application for example Adobe Acrobat X.


The starting point for OLE is the command  CreateObject. There are quite a few demos in the executable that use CreateObject.

Here is the demo Text to Speech

' text to speech using Sapi.SpVoice
' It works for me on Win 7 and Win 10 without me needing to install anything!
newscript
dim SpVoice = CreateObject ("Sapi.SpVoice")
If ( ! SPVoice ) THEN
Message ="Could not create object"
exit
End IF
dim myI, i=0
for each myI in SPvoice.GetVoices
i = i +1
SpVoice.voice = myI
spVoice.speak ("Voice " & i & ". hello. are you surprised it works.")
next myI
set SpVoice = nothing

It is a fairly well behaved little script. It creates a link to the Sapi.SpVoice application, uses some object / methods and parameters, and then releases the application.

The key issues with OLE are:

  • Knowing the name of the application object to be accessed
  • Knowing the object model of the application i.e. what you can access.

Larger applications, such as Microsoft VBA, gain access to special type library files that inform the application about the target application, its methods, parameters and values.

I am not sorry, but my executable does not do this. I wanted a light weight executable, with the exception of the SQLite code and 2 or 3 other open source libraries, I have 78 files, > 96000 lines, and > 2.6 million characters. Maybe one day I will support type libraries, but not today!


The only reason I know that Sapi.SpVoice has a  voice object is because I surfed the Web!


On the Web I found that an OLE link to the Excel application must be:

Dim myExl = CreateObject ( "Excel.application")

or

Dim myExl = CreateObject ( "{00024500-0000-0000-C000-000000000046}")


This executable is written so that it will accept (for example)

newscript
Dim myExl = CreateObject ( "Excel.application")
myExl.visible = 1

But the executable does no real validation that the Excel object myExl has a property call visible.

Instead, the executable’s interpreter recognizes the syntax of the command line and asks the myExl object if it will accept setting a value to a property called visible.

If it does, fine.

If it does not then the command will fail, and if it is part of a script then the script will stop!


oleMAP

It is your problem to know what methods, parameters and values an OLE object has!

The only help that the executable offers is when you use the command oleMAP ( object ) , so in this case  oleMap ( myExl).

The executable will then ask the object for a list of the methods and properties that it has. The result is then written to the Report Panel.

For demonstration pruposes I will not use oleMAP on Excel, the resulting list is too long. Instead I will use  Sapi.SpVoice

newscript
dim SpVoice = CreateObject ("Sapi.SpVoice")
olemap ( SpVoice )

Then the Report Panel shows:

  • 0 QueryInterface F
  • 1 AddRef F
  • 2 Release F
  • 3 GetTypeInfoCount F
  • 4 GetTypeInfo F
  • 5 GetIDsOfNames F
  • 6 Invoke F
  • 7 Status G
  • 8 Voice G
  • 9 Voice R
  • 10 AudioOutput G
  • 11 AudioOutput R
  • 12 AudioOutputStream G
  • 13 AudioOutputStream R
  • 14 Rate G
  • 15 Rate P
  • 16 Volume G
  • 17 Volume P
  • 18 AllowAudioOutputFormatChangesOnNextSet P
  • 19 AllowAudioOutputFormatChangesOnNextSet G
  • 20 EventInterests G
  • 21 EventInterests P
  • 22 Priority P
  • 23 Priority G
  • 24 AlertBoundary P
  • 25 AlertBoundary G
  • 26 SynchronousSpeakTimeout P
  • 27 SynchronousSpeakTimeout G
  • 28 Speak F
  • 29 SpeakStream F
  • 30 Pause F
  • 31 Resume F
  • 32 Skip F
  • 33 GetVoices F
  • 34 GetAudioOutputs F
  • 35 WaitUntilDone F
  • 36 SpeakCompleteEvent F
  • 37 IsUISupported F
  • 38 DisplayUI F

The leading number has no significance. Then comes the name of a component, then a letter indicating if the component is a function/method (F), or a property that you can put/set/write (P), or  a property that you can get/read (G), or a property that is put by reference (R).

Not much help, but possibly better than nothing.

However acceptable values are much more difficult.


In the following script I had to surf the Web to find out that -4108 causes the vertical alignment of a cell to be xlVAlignCenter

newscript
dim myExcel= CreateObject ("Excel.application")
myExcel .workbooks.add
myExcel.cells.VerticalAlignment = -4108
myExcel.cell(1,1).value = "qwerty"

Recommendation: if you do a lot of Excel work, surf the Web for a document that lists all of the Excel constants (it is there!). Get it and keep it handy!


Good programming practice it to always release object that you no longer need.

In this executable all variables / objects created within a function or a subroutine are automatically/implicitly  released when the function/subroutein terminates.

You must take this as a matter of trust in the executable.

Trust is good, but releasing variables and objects explicitly is better.