Create neat excel spreadsheets and charts from your Fortran programs (Example 6.3)

This is an example in which we create a Fortran Automation controller for Excel. The main Fortran application opens a new Excel spreadsheet and set the values of a few cells to random values. Then the Fortran applications creates a new chart that plots the random values as XY coordinates. After this is done, the spreadsheet is made visible, so the user can see the results.

Important points that you should notice in this example include:

• First, note how easy it is to control automation objects using f90VB. The same program, written in C/C++ would be much more complex.

• Second, 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
 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.

• Third, 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

WorkBook=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 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.

Here is a screen shot of the output of the program:

And here is the complete example:

program Example62

!Example shows how to use f90VBA to automate Excel
!Copyright (C) 1999-2000, Canaima Software, Inc.
!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 store 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
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.))

!Make 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