How to avoid runtime errors with Excel automation

[Anna Hristova]

When automating Excel, runtime errors of the type "Internal datastructures corrupted (IDSC)" can be observed if a large number of properties are set. The stability of the application can be increased if the implicitly created AutomationObject instances are destroyed synchronously. An example of a problematic coding pattern can look like this:

   oSheet:Cells(nRow,1):Value := FIELD->PARTNAME
   oSheet:Cells(nRow,2):Value := FIELD->PURCHASE
   oSheet:Cells(nRow,3):Value := FIELD->SELLPRICE

In the above code 3 AutomationObject instances are created which are then asynchronously discarded by the garbage collector. The identical functionality can also be implemented with the following code:

   ...
   SetCellsValue( oSheet, nRow, 1, FIELD->PARTNAME )
   SetCellsValue( oSheet, nRow, 2, FIELD->PURCHASE )
   SetCellsValue( oSheet, nRow, 3, FIELD->SELLPRICE )
   ...
   STATIC PROCEDURE SetCellsValue( oSheet, nRow, nCol, xValue )
      LOCAL oCells
      oCells := oSheet:Cells( nRow, nCol )
      oCells:Value := xValue
      oCells:destroy()
   RETURN

In the above helper function SetCellsValue() the AutomationObject representing :Cells is stored in a local variable and then synchronously destroyed.

This approach is also suitable to increase performance of the program, which is described in this forum post: Daling with slow downs or hang ups when automating ms excel