How to isolate your Visual LANSA Application from Microsoft Office version incompatibilities

Date:14 November 2007
Product/Release:Visual LANSA V11.3
Abstract:A practical solution to check for the availability of an ActiveX control before instantiating it
Submitted By:LANSA Technical Support

Description:

Developing applications which make use of 3rd party ActiveX controls offers many additional benefits and functionality that can enhance your LANSA application. Unfortunately making use of ActiveX controls affects the portability of your application.

At a very minimum, the ActiveX control will need to be registered on the client PC (see this tip for information on how to do this). Some ActiveX controls require a complete application to be installed, for example Microsoft Office.

If you are developing with Microsoft Office integration you will soon discover that the ActiveX control is version dependant. For example, an application built with an ActiveX control for Office 2000 will not work on a PC with Office 2003 and vice versa. Unfortunately this is unavoidable since Microsoft changes the ActiveX interface with each version. And since Microsoft Office is often already installed on the client PC, you may be faced with a choice of upgrading all users to the same version of Office, or discarding Microsoft Office integration altogether.

Reason:

The registry entries which point to the Microsoft Office ActiveX DLL change with each version. In some cases even the methods/properties might change (as outlined in this tip). This is made even more noticeable by the fact that in many companies there may be 2 or 3 versions of Office on different user's PCs.

Note: This situation is not specific to LANSA. Other programming languages that use ActiveX controls can have the same problem.

Solution:

One way to avoid this problem within LANSA, is to use the following approach:

  1. Separate all ActiveX interaction into a single component
  2. Check the registry to determine if the ActiveX is available before instantiating the component

This can be applied to any ActiveX control, not just Microsoft Office components.

In the case of Microsoft Office, the solution is to create several 'versions' of the ActiveX interaction component for each version of Office you are likely to encounter (i.e. 97, 2000 and 2003). Each 'version' component is compiled on a PC with the relevant version of Office installed, and the component DLL is copied to the main development PC.

On the main development environment another 'version independent' component is created which contains dynamic references to each of those 'version' components. At runtime, this component should check the registry to determine which version of Office is installed, before instantiating that 'version' component.

For example, the following component has methods like OpenExcel(Filename), ReadCell(Row, Column, Value) and WriteCell(Row, Column, Value).

#Excel2003 Reusable part:

FUNCTION OPTIONS(*DIRECT)
BEGIN_COM ROLE(*EXTENDS #PRIM_OBJT)
Define_Com Class(#vx_excel.Application) Name(#ExcelApp) Reference(*DYNAMIC)
Define_Com Class(#vx_excel.Worksheet) Name(#Worksheet) Reference(*Dynamic)
Define_Com Class(#vx_excel.Range) Name(#Cells) Reference(*Dynamic)

Mthroutine Name(OpenExcel)
Define_Map For(*Input) Class(#Prim_Alph) Name(#File_Name)
Define_Map For(*result) Class(#Prim_Boln) Name(#Success)

#Success := False

If (#ExcelApp *Is *Null)
Set_Ref Com(#ExcelApp) To(*Create_As #vx_excel)
If (#ExcelApp *Is *Null)
Use OV_MESSAGE_BOX 'Could not create ActiveX.'
Return
Endif
Endif

If (#ExcelApp.Workbooks.Count > 0)
#ExcelApp.Workbooks.Close
Endif
#ExcelApp.Workbooks.Open(#File_Name)

If (#ExcelApp.Workbooks.Count = 0)
Use OV_MESSAGE_BOX 'Could not open file.'
Return
Endif

Set_Ref Com(#Worksheet) To(*Dynamic #ExcelApp.Worksheets<1>)
Set_Ref Com(#Cells) To(#Worksheet.Cells)

#Success := True
EndRoutine

MthRoutine Name(CloseExcel)
If (#ExcelApp *IsNot *Null)
If (#ExcelApp.Workbooks.Count > 0)
#ExcelApp.Workbooks.Close
Endif

#Cells #Worksheet #ExcelApp <= *Null
Endif
Endroutine

Mthroutine Name(ReadCell)
Define_Map For(*Input) Class(#Prim_Alph) Name(#CellName)
Define_Map For(*result) Class(#Prim_Alph) Name(#CellValue)

If ((#ExcelApp *Is *Null) *OrIf (#ExcelApp.Workbooks.Count = 0))
Return
Endif

#CellValue := #Cells.Range<#CellName.Value>.Value2
EndRoutine

Mthroutine Name(WriteCell)
Define_Map For(*Input) Class(#Prim_Alph) Name(#CellName)
Define_Map For(*Input) Class(#Prim_Alph) Name(#CellValue)

If ((#ExcelApp *Is *Null) *OrIf (#ExcelApp.Workbooks.Count = 0))
Return
Endif

#Cells.Range<#CellName.Value>.Value2 := #CellValue.Value
EndRoutine
END_COM

Then you would also create components #Excel2000, and #Excel97 using exactly the same source code.

Note:

#Excel2003 must be compiled on a PC which has Excel 2003 installed
#Excel2000 must be compiled on a PC which has Excel 2000 installed
#Excel97 must be compiled on a PC which has Excel 97 installed

Since the code above contains no application-specific fields or references (except for a reference to an Excel ActiveX component #VX_EXCEL), it can be easily copied and compiled on different development environments. #VX_EXCEL should be created to point to the Excel Application that is installed on the development PC.

All three components (#Excel97, #Excel2000, #Excel2003) should be created on the main development PC, but not compiled. The DLLs compiled on the other PCs should be copied to the partition execute directory of the main development PC instead.

The 'version independant' component will be called #ExcelX, and this has a dynamic reference to each of these components (#Excel2003, #Excel2000 and #Excel97). In the initialization routine for that component you must read the registry to find out what version of Excel is installed on the PC (using GET_REGISTRY_VALUE BIF). The keys to look for are:

  • Excel 2003: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\11.0\Excel\InstallRoot\Path
  • Excel 2000: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\9.0\Excel\InstallRoot\Path
  • Excel 97: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Excel\InstallRoot\Path

i.e. if the 2003 key exists, instantiate the #Excel2003 component, otherwise check for Excel 2000, and so on.

#ExcelX Reusable part:

Note: The code below may appear truncated. Either use the horizontal scroll bar at the bottom of the code block or select the code in its entirety by double click anywhere on the code and then copying and pasting it into your editor.

FUNCTION OPTIONS(*DIRECT)
BEGIN_COM ROLE(*EXTENDS #PRIM_OBJT)
Define_Com Class(#Excel2003) Reference(*Dynamic)
Define_Com Class(#Excel2000) Reference(*Dynamic)
Define_Com Class(#Excel97) Reference(*Dynamic)
Define_Com Class(#Prim_Objt) Name(#Excel_) Reference(*Dynamic)

EVTROUTINE HANDLING(#COM_OWNER.CreateInstance) OPTIONS(*NOCLEARMESSAGES *NOCLEARERRORS)
Define_Com Class(#Prim_Alph) Name(#ExcelPath)

* First check for Office 2003
Use Builtin(Get_Registry_Value) With_Args(HKEY_LOCAL_MACHINE 'SOFTWARE\Microsoft\Office\11.0\Excel\InstallRoot' 'Path') To_Get(#ExcelPath #Std_Retc)
If (#Std_Retc = OK)
Set_Ref Com(#Excel_) To(*Create_As #Excel2003)
Else

* 2003 not installed, now look for 2000
Use Builtin(Get_Registry_Value) With_Args(HKEY_LOCAL_MACHINE 'SOFTWARE\Microsoft\Office\9.0\Excel\InstallRoot' 'Path') To_Get(#ExcelPath #Std_Retc)

If (#Std_Retc = OK)
Set_Ref Com(#Excel_) To(*Create_As #Excel2000)
Else

* 2000 not installed, now look for 97
Use Builtin(Get_Registry_Value) With_Args(HKEY_LOCAL_MACHINE 'SOFTWARE\Microsoft\Office\8.0\Excel\InstallRoot' 'Path') To_Get(#ExcelPath #Std_Retc)

If (#Std_Retc = OK)
Set_Ref Com(#Excel_) To(*Create_As #Excel97)
Endif
Endif

Endif
ENDROUTINE

Mthroutine Name(OpenExcel)
Define_Map For(*Input) Class(#Prim_Alph) Name(#File_Name)
Define_Map For(*result) Class(#Prim_Boln) Name(#Success)

#Success := False

If (#Excel_ *IsOfType #Excel2003)
#Success := (#Excel_ *as #Excel2003).OpenExcel(#File_Name)
Endif

If (#Excel_ *IsOfType #Excel2000)
#Success := (#Excel_ *as #Excel2000).OpenExcel(#File_Name)
Endif

If (#Excel_ *IsOfType #Excel97)
#Success := (#Excel_ *as #Excel97).OpenExcel(#File_Name)
Endif
EndRoutine

Mthroutine Name(ReadCell)
Define_Map For(*Input) Class(#Prim_Alph) Name(#CellName)
Define_Map For(*result) Class(#Prim_Alph) Name(#CellValue)

If (#Excel_ *IsOfType #Excel2003)
#CellValue := (#Excel_ *as #Excel2003).ReadCell(#CellName)
Endif

If (#Excel_ *IsOfType #Excel2000)
#CellValue := (#Excel_ *as #Excel2000).ReadCell(#CellName)
Endif

If (#Excel_ *IsOfType #Excel97)
#CellValue := (#Excel_ *as #Excel97).ReadCell(#CellName)
Endif
EndRoutine

Mthroutine Name(WriteCell)
Define_Map For(*Input) Class(#Prim_Alph) Name(#CellName)
Define_Map For(*Input) Class(#Prim_Alph) Name(#CellValue)

If (#Excel_ *IsOfType #Excel2003)
(#Excel_ *as #Excel2003).WriteCell(#CellName #CellValue)
Endif

If (#Excel_ *IsOfType #Excel2000)
(#Excel_ *as #Excel2000).WriteCell(#CellName #CellValue)
Endif

If (#Excel_ *IsOfType #Excel97)
(#Excel_ *as #Excel97).WriteCell(#CellName #CellValue)
Endif
EndRoutine


EVTROUTINE HANDLING(#COM_OWNER.DestroyInstance) OPTIONS(*NOCLEARMESSAGES *NOCLEARERRORS)

If (#Excel_ *IsOfType #Excel2003)
(#Excel_ *as #Excel2003).CloseExcel
Endif

If (#Excel_ *IsOfType #Excel2000)
(#Excel_ *as #Excel2000).CloseExcel
Endif

If (#Excel_ *IsOfType #Excel97)
(#Excel_ *as #Excel97).CloseExcel
Endif

Set_Ref Com(#Excel_) To(*Null)
ENDROUTINE
END_COM

All other components in your Application can reference #ExcelX and its methods to access Excel ActiveX functionality, regardless of the user's Excel version:

i.e.

DEFINE_COM CLASS(#EXCELX) NAME(#EXCELX)

Then:

If (#ExcelX.OpenExcel(#FileName))
Use OV_MESSAGE_BOX 'Excel Opened Successfully'
Else
Use OV_MESSAGE_BOX 'Could not open file'
Endif

#ExcelX.WriteCell('A1' 'Hello World')