Working with collections and object hierarchies

Many ActiveX objects return or receive collections as arguments to methods and properties, or expose themselves as hierarchies of objects. Manipulating collections and hierarchies of objects with f90VB is no different than manipulating any other object (in fact, collections are just a particular kind of object). Example 6.3 contains a Fortran program that automates Microsoft Excel, creating a spreadsheet, adding some data, and then creating a chart that displays the data as a XY scatter-plot. Excel exposes its objects as a logical hierarchy of sub-objects, which makes it a perfect server for this section. Example 6.3 also illustrates other topics that will be discussed in the following sections.

Example 6.3

program Example63

!Example shows how to use f90VB to automate Excel

!Illustrates:

! - Creating an new object

! - Getting an interface to an active object

! - Setting/Reviewing property values

! - Executing object methods

! - Requesting properties/Executing methods in

! an object hierarchy

! - Handling collections

use f90VBDefs

use f90VBVariants

use f90VBAutomation

implicit none

!Variants containing main objects

type(VARIANT)::Excel, WorkBook, Chart, Range

!Variants used to stored temporal objects and collections

type(VARIANT)::VarTmp

integer(HRESULT_KIND)::iRet

integer::i

real::k

type(VARIANT)::IsVisible

!Initialize Ole

iRet = OleInitialize()

!If Excel is already running, then get an interface

!to the running instance

Excel = GetActiveOleObject('Excel.Application',iRet)

if (iRet.ne.S_OK) then

!no instances of Excel are running, create one

Excel = CreateOleObject('Excel.Application', iRet)

if (iRet.ne.S_OK) then

print *,'Cannot instantiate the Excel object. Excel might not be'

print *,'installed or properly registered in your system'

goto 1000

endif

!Add a new workbook by calling Workbooks.Add method

WorkBook=ExecMethod(Excel,'Workbooks.Add')

!Get a range object from the current workbook

VarTmp=VariantCreate(VT_BSTR,'A1:B20')

Range=PropertyGet(Excel,'Range',VarTmp)

!Clear BString stored in VarTmp

call VariantClear(VarTmp)

!Set the range formulas to some random values

VarTmp=VariantCreate(VT_BSTR,'=10*RAND()')

call PropertyPut(Range,'Formula',VarTmp)

!Clear BString stored in VarTmp

call VariantClear(VarTmp)

!Add a new Chart by calling Charts.Add method

Chart=ExecMethod(WorkBook,'Charts.Add')

!Set the type of the chart to scatterplot (-4169)

call PropertyPut(Chart,'ChartType', VariantCreate(VT_I4,-4169))

!Set the range object to be the data for the chart

VarTmp=ExecMethod(Chart,'SetSourceData',Range, VariantCreate(VT_I4,2))

!Set the title for the chart

call PropertyPut(Chart,'HasTitle',VariantCreate(VT_BOOL,.true.))

VarTmp = VariantCreate(VT_BSTR,'f90VB is Easy!')

call PropertyPut(Chart,'ChartTitle.Text',VarTmp,iRet=iRet)

!We don't want to see a legend in this case, so remove it

call PropertyPut(Chart,'HasLegend',VariantCreate(VT_BOOL,.false.))

!Makes the excel object visible

call PropertyPut(Excel,'Visible',VariantCreate(VT_BOOL,.true.))

!release all the currently held interfaces

call Release(Chart)

call Release(Workbook)

!close Excel (uncomment next line for closing)

!VarTmp= ExecMethod(Excel,'Quit',iRet=iRet)

call Release(Excel)

1000 continue

call OLEUninitialize()

stop

end

This example doesn’t introduce much that you have not already seen in previous examples, however there are several interesting techniques that are worth your attention.

First, notice how at the beginning of the program we check if there is a running instance of Excel and use it, or create a new instance if Excel is not running:

Excel = GetActiveOleObject('Excel.Application',iRet)

if (iRet.ne.S_OK) then

!no instances of Excel are running, create one

Excel = CreateOleObject('Excel.Application', iRet)

if (iRet.ne.S_OK) then

print *,'Cannot instantiate the Excel object. Excel might not be'

print *,'installed or properly registered in your system'

goto 1000

endif

This is a rather standard procedure when automating ActiveX server applications. Usually you don’t want to have more than one instance of the main application (in this case Excel.Application) running, when all you need is a new instance of one of its contained objects (a Workbook object in this example). This technique is very useful when working with memory-hungry application servers, like Excel and Word.

Second, let’s take a more detailed look at one the statements in Example 6.3:

WorkBook=ExecMethod(Excel,'Workbooks.Add')

This statement invokes the method Add from the object Workbooks. Note that we did not have to instantiate object Workbooks to call the method. The way you would normally call the Add method for object Workbooks is as follows:

WorkBooks=PropertyGet(Excel,’Workbooks’)

WorkBook=ExecMethod(WorkBooks,'Add')

i.e. you first get an interface to object Workbooks and then call its methods. This works fine, but has a tendency to result in programs with many objects. To avoid this problem, f90VB’s procedures PropertyGet, PropertyPut, PropertySet and ExecMethod include a parser and the necessary logic to create an intermediate Workbooks object, call its Add method and return the WorkBook object resulting from the Add method. The intermediate object Workbooks is destroyed by f90VB after it is no longer needed. This is a very nice feature, because it can potentially reduce by several fold the number of variables and objects you need to use in your program. However, it should be used carefully. When you invoke ExecMethod(Excel,'Workbooks.Add') f90VB internally has to instantiate and release intermediate objects which can be a time consuming operation.

The f90VB parser has an important limitation; it cannot parse arguments to functions or methods. For example, in Visual Basic you can use this type of construct:

Workbook.Range(“A1:B20”).Formula = “=10*RAND()”

Method Workbook.Range(“A1:B20”) returns a Range object, which has a Formula property you can change. Note that Range(“A1:B20”) is in fact the invocation of a method passing a single argument; a character string describing the range. f90VB’s parser would choke on a statement like the following:

VarTmp=VariantCreate(VT_BSTR,'=10*RAND()')
call PropertyPut(Range,'Workbook.Range(“A1:B20”).Formula',VarTmp)

Because it wouldn’t know how to invoke the Range method with its arguments. In these cases, you need to do as we did in Example 6.3, create an intermediate Range object:

VarTmp=VariantCreate(VT_BSTR,'A1:B20')

Range=PropertyGet(Excel,'Range',VarTmp)

…

VarTmp=VariantCreate(VT_BSTR,'=10*RAND()')

call PropertyPut(Range,'Formula',VarTmp)

If you no longer need the Range object after you have set its Formula property, you can Release it. In Example 6.3, the range object is used later to indicate the range of values to be included in the scatter plot the example creates, so we’ll just leave it around a while longer.

Using f90VB to access Visual Basic procedures from Fortran