Vote for this Product at ClarionShop
 
Buy now at ClarionShop

CapeSoft Software copyright
www.capesoft.com
c3pa approved
     

 

 

Microsoft Excel Functionality



Contents
bullet small Templates
   Add an Excel Object
   Import From Excel templates.
 
bullet small Classes
     The oiExcel Class
        Methods
        Properties
        Equates
 
bullet small Dll Functions
 
bullet small

 Useful References
    Tips & FAQ
        Limitations in Excel (rows, columns etc.), workarounds and version differences.

"How To" and code examples (recommend reading) (New)
        Color Management in Excel and Cell Formatting (required reading)
        Exporting a table to a Excel file.
 

bullet small Return to the Main Office Inside Docs

     

bulletTemplates

Add_MSExcel_Object
Summary
Procedure Extension Template
Add an MS Excel object to a procedure
Optional
 
What does it do?
This template is used to add MS Excel functionality to a procedure in your app.  We are still developing it and adding functionality to it, more info will come later.
 
Prerequisites

You need to have added the Activate_Office_Inside global extension template before you can add this template to a procedure.

Editable Reports (Word and Excel report output)

Legacy Users: If you are using Legacy and would like to use the Editable Report Word and Excel templates you need to ticked on "Enable the use of ABC classes" checkbox under Global Settings on the Classes tab.

  1. Add the Local extension to a Report procedure (See "How do I implement it" below), and from the Template Type dropdown select "Reports"
  2. Click OK, and then select Generate from the Project menu
  3. Go to the Properties for the report procedure and click on the Report Properties option.
  4. Select the Report Target tab and either select "Ask at Runtime" or "other" from the Report Target dropdown. If you select Ask at Runtime, then user will be prompted. If you select Other you can choose the report output type from the Other Target dropdown.
     
How do I implement it?
  1. Select the procedure which you want to add this template to.
  2. Select "Properties..." from the "Edit" menu
  3. Click the "Extensions" button
  4. Click on the "Insert" button to add a new extension template
  5. Select "Add_MSExcel_Object" ( found under the "Class OfficeInside" group )
  6. Click "Select", then "OK", then "OK" again...

What are my options?

General Tab:

Object Name:

This is the name of the Office Inside object that this template will create and implement.  Typically this would default to 'MyExcel1', but you can change it if you would prefer to call the object something else.

 


     

 

Import an Excel File (ImportXLStoTPSAbc and ImportAnXLSFile)
  Summary  

The ImportXLStoTPSAbc and ImportAnXLSFile templates allow you to import files from Excel into you database.
 

1) ImportXLStoTPSAbc: Using the Wizard procedure:
From the Application menu in the Clarion select "Template Utility" and
select "ImportXlsToTpsABC" from the Class OfficeInside section. This will
add the import Wizard procedure. Simply call this procedure from a button on
your browse etc.

2) ImportAnXLSFile: import without a Wizard:
Add the local Office Inside extension to your procedure to populate an Excel
object.
Add a button to the window to start the import (you can also use a menu item
etc.) Go to the embeds for the button and double click on Event:Accepted.
Select "ImportXLS- Import an XLS File" from the list (under Class
OfficeInside).

Once you have added the code template, double click on it to change the
settings - enter the name of the Excel object and the file to import etc.

Both ways are demonstrated in the offdemo.app example application (see the
Excel_ImportFromXlsToTps_NoWizard, Excel_ImportFromXlsToTps_Wizard and
oiExcelImportWizard procedures).

     

 

Classes

This section describes the various classes which make up "Office Inside".  Each class contains methods and properties, which are documented below.  Each method is documented by giving the method name, the parameters, an example of how to code the method, a list describing what the method does, and additional comments below.

The oiExcel Class - Introduction

The oiExcel class is a "wrapper" class, which is used by the templates and by other classes as the "communication" layer between Office Inside and MS Excel.  Should you wish to write code which communicates "directly" with MS Excel, this is the class you're looking for.

Class Methods The oiExcel Class - Methods Listed by Usage

oiExcel Class Methods - Grouped by Use

oiExcel Methods

     

Workbook Methods

  NewWorkbook Creates a new workbook
  OpenWorkbook Opens an existing workbook
  CloseWorkbook Closes the currently open workbook
  CountOpenWorkbooks Returns the number of workbooks opened in this instance of Excel
  GetFileName Returns the current file name and path on disk.
  ProtectWorkbook Turns on protection for the current workbook
  UnprotectWorkbook Turns off protection for the current workbook
  Save Saves changes to the current workbook
  SaveAs Saves the workbook as a file on disk.
     
 

Worksheet Methods

 
  InsertWorksheet Inserts a new worksheet
  DeleteWorksheet Deletes a worksheet
  RenameWorksheet Renames a worksheet
  CountWorksheets Returns the number of worksheets in the workbook
  ProtectWorksheet Turns on protection for the worksheet
  UnprotectWorksheet Turns off protection for the worksheet
  SelectWorksheet Selects a particular worksheet in the current workbook
     
 

Rows and Column Methods

 
  InsertColumn Inserts a new column
  InsertRow Inserts a new row
  CountUsedRows Returns the number of rows that have been used (contain data)
  ColumnName Converts a column number into the Excel name for that column.
  GetColumnAddressFromColumnNumber Converts a number for the column into the Excel name for that column. Deprecated: replaced by ColumnName()
  GetColumnWidth Returns the width of a column
  GetRowHeight Returns the height of a row
  SelectRows Selects one or more rows
  SelectColumns Selects one or more columns
  SetRowHeight Sets the height of a row
  SetColumnWidth Sets the width of a column
 
 

Printing Methods

 
  SetPrintArea Sets the area to print (limits printing to the set area)
  ClearPrintArea Clears the are that was set using SetPrintArea() method
  PrintMe Prints the document (see the PrintWorkbook and PrintWorksheet for the newer, more extensive methods)
  PrintPreview Prints the document and displays a preview before printing to allow the user to choose whether to print or not and to check the print settings.
  PrintSheet Print out the current sheet in the current workbook
  PrintWorkbook Print the current workbook
  GetActivePrinter  
        
 

Editing Methods

 
  Cut Cuts the current selection (removes it from the document and places it on the clipboard)
  Copy Copies the current selection to the clipboard
  Find Searches for a string in the document
  Paste Pastes from the clipboard to the current position
  PasteSpecial Identical to Paste, but specifies what type of content should be pasted
  Undo Undoes the last action
  Redo Redoes an action that was undone
  DeleteSelection Deletes the current selection
  ReplaceText Replaces one or all occurrences of a string with the string specified.
     
     
 

View and Application Methods

 
  GetInfo Retrieves a variety of information related to Excel, the user, and the current document. This information can be updated using the Update() method.
  Update Sets a variety of information related to Excel, the user, and the current document. This information can be retrieved using the Get Info() method.
  GetPaletteColor (new) Returns the colour at the passed palette position.
  SetPaletteColor (new) Sets a colour in the Excel palette
     
 

View Management

 
  SetPageView Sets the way the page is displayed
  ShowFormulaBar Displays the Formula toolbar
  HideFormulaBar Hides the Formula toolbar
  ShowToolbar Shows the main Toolbar
  HideToolbar Hides the main Toolbar
  ShowStatusBar Shows the Status bar at the bottom of the window
  HideStatusBar Hides the Status bar
  FreezePanes  
     
 

Utility Methods

 
  ErrorTrap Called with error and warning messages. Provides centralised error handling and reporting.
  Init Initialises the object and the COM interface if needed
  Kill Disposes of the object and the COM interface if it is no longer being used by any objects.
  TakeEvent Event handling, called when an event occurred in Word
  ExcelImport Deprecated: Use the oiExcelImpEx class for importing and exporting data.

Imports a file from Excel into Clarion and calls the TakeImportRecord method for each row (record) imported.
 

TakeImportRecord

Called by ExcelImport to allow each row (record) to be processed.
 

ImportSetup

ExcelImport() Callback method - the spreadsheet has been opened, before records are imported.
 

ImportComplete

ExcelImport() Callback method - import is complete, before the spreadsheet is closed.
  ImportXLSFile (deprecated) Deprecated, replaced by ExcelImport
  AutoFilter Enables Excel's auto filtering on a range of cells.
     
 

Cell Methods

 
  ReadCell Gets the value of the specified cell
  WriteToCell Sets the value of the specified cell
  MergeCells Merges a range of cells into a single large cell
  UnmergeCells Unmerges a merged range of cells back into the original cells
  SelectCells Selects a range of cells
     
 

Cell Formatting

 
  GetCellBackgroundColor Gets the background color for a cell or a range of cells.
  SetCellAlignment Sets the alignment for the contents of the selected cells
  SetCellBackgroundColor Sets the background colour of the selected cells.
  SetCellBorders Sets the borders for the selected cells
  SetCellFontColor Sets the cell font colour
  SetCellFontName Sets the font for the contents of the selected cells.
  SetCellFontSize Sets the size of the font used for the selected cells
  SetCellFontStyle Sets the style of the font (bold, italic etc.)
  SetCellFontUnderline Underlines the contents of the selected cells
  SetCellNumberFormat Sets the format (picture) used to display the contents of the selected cells.
  SetCellProtection Turns on or off cell protection
     
 

Performance and Optimization

 
  SetCalculations Enables or disables automatic calculation each time a cell is modified. Turning this off has a massive performance impact when doing bulk insertion.
  Calculate Forces a manual recalculation when SetCalculations() has been used to turn it off.
  SetScreenUpdating Turns screen updating on or off. For use when Excel is hidden and a batch update is done. Has a small performance impact.
     
     

The oiExcel Class - Methods in Alphabetic Order

oiExcel Class Method Alphabetical Listing
 
  AutoFilter Enables Excel's auto filtering on a range of cells.
  ClearPrintArea Clears the are that was set using SetPrintArea() method
  CloseWorkbook Closes the currently open workbook
  ColumnName Returns the name of the Excel column when passed the column number
  CountOpenWorkbooks Returns the number of open workbooks
  CountUsedRows Returns the number of rows used
  CountWorksheets  
  InsertWorksheet  
  Cut Copy  
  DeleteSelection  
  DeleteWorksheet  
  ErrorTrap  
  ExcelImport Imports a file from Excel into Clarion and calls the TakeImportRecord method for each row (record) imported.
  FreezePanes  
 

GetColumnAddressFromColumnNumber

 
  GetColumnWidth  
  GetInfo  
  GetPaletteColor  
  GetRowHeight  
  HideFormulaBar  
  HideStatusBar  
  HideToolbar  
  ImportXLSFile (Deprecated) Deprecated, please use the ExcelImport method.
  Init  
  InsertColumn  
  InsertRow  
     
  Kill  
  MergeCells  
  NewWorkbook  
  OpenWorkbook  
  Paste  
  PasteSpecial  
  PrintMe  
  PrintPreview  
  ProtectWorkbook  
  ProtectWorksheet  
  ReadCell  
  Redo  
  RenameWorksheet  
  ReplaceText  
  Save  
  SaveAs  
  Search  
  SelectCells  
  SelectColumns  
  SelectRows  
  SelectWorksheet  
  SetCellAlignment  
  SetCellBackgroundColor  
  SetCellBorders  
  SetCellFontColor  
  SetCellFontName  
  SetCellFontSize  
  SetCellFontStyle  
  SetCellFontUnderline  
  SetCellNumberFormat  
  SetCellProtection  
  SetColumnWidth  
  SetPageView  
  SetPaletteColor  
  SetPrintArea  
  SetRowHeight  
  ShowFormulaBar  
  ShowStatusBar  
  ShowToolbar  
  TakeEvent  
  Undo  
  UnmergeCells  
  UnprotectWorkbook  
  UnprotectWorksheet  
  Update  
  WriteToCell  
 
     

 

 

 

AutoFilter     ( string pFromColumn, <string pToColumn> ) ,byte,proc
MyExcel.AutoFilter ('A', 'E')

  • Applies an AutoFilter to the range of columns identified by the pFromColumn and pToColumn parameters.  See the Microsoft Office documentation on 'AutoFilter' for more information on how AutoFiltering works.

up icon

 

Calculate ()

Forces the workbook to calculate the values of all cells and ensures they are up to date. When data is written to Excel by default it automatically checks whether the data is a formula and performs any needed calculation on the cell. This can slow down writing of data dramatically, although it ensures that all cells in the workbook are always up to date. In order to speed up writing data to Excel this method allow calculation to be set to manual by calling the SetCalculations() method, so data can be written, and then any calculations needed can be performed by calling the Calculate() method

Parameters

none

Return Value

Returns 1 if the function succeeds, and zero for failure. If the function fails the ErrorTrap() method is called with information relating to the error.

Examples

    ExcelDoc.SetCalculations(oix:CalculationManual)
ExcelDoc.SetScreenUpdating(false) ! Optionally turn off screen updating

! Write the required data to the worksheet here...
ExcelDoc.Calculate() ! Manually calculate (if necessary) ! Automatic calculation can be turned back on: ExcelDoc.SetCalculations(oix:CalculationAutomatic)

up icon

 

ClearPrintArea ( ) ,byte,proc

MyExcel.ClearPrintArea ()

  • Same as "File" --> "Print Area" --> "Clear Print Area" in Excel.
  • Returns true (1) if no problems were experienced.

up icon

 

CloseWorkbook     ( byte pSaveChanges=1 ) ,byte,proc
MyExcel.CloseWorkbook() ! user is prompted to save
  • Closes the currently open workbook.
  • The pSaveChanges parameter can be one of the following values:
    • 1  - User will be prompted (if necessary) as to whether he wants to save changes
    • 2  - Changes will be discarded without warning the user
    • 3  - The "Save As" dialog window will be called
  • Returns true (1) if no problems were experienced.

up icon

 

Copy     ( <string pFromCell>, <string pToCell> ) ,byte,proc
MyExcel.Copy () ! copies current selection
MyExcel.Copy ('A1') ! copies cell A1
MyExcel.Copy ('A1', 'B3') ! copy range A1:B3
  • Same effect as "Edit --> Copy" in MS Excel.
  • You can either copy the current selection, or a single specified cell, or a specified range (as shown above). 

up icon

 

Find     (string pFind, <string pAfter>, long lookIn=oix:Values, long lookAt=oix:Whole, long searchOrder=1, long searchDirection=1, long matchCase=0, long matchByte=0)

Search the current workbook for the passed string. All values other than the string to search for are optional.

Parameters

pFind

The string to search for.

pAfter [Optional]

The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you don’t specify this argument, the search starts after the cell in the upper-left corner of the range.


lookIn [Optional]

The search location - can be one of the following values: oix:Comments (-4144), oix:Formulas (-4123), oix:Values (-4163)

 lookAt

The type of matching to do. Can be oix:Part (2) or oix:Whole (1). oix:Whole means that the text search for and the text searched in are an exact match. oix:Part means that the text searched for exists in the text searched.

searchOrder

Can be either oix:ByRows (1) or oix:ByColumns (2).

searchDirection

The direction to search in, either oix:Next (1) or oix:Previous (2)

matchcase

Whether the search is case sensitive or not. oi:False for case insensitive, oi:True for case sensitive search

matchByte

Only applies for double byte languages (Unicode). Setting this to true force double byte chars to only match double byte chars (which is not desired behaviour with Clarion strings)

Return Value

Returns a string containing the location of the range found if successful. Returns an empty string for failure or if there are no matches. If the method fails, ErrorTrap is called.

 

 

ColumnName (long columnNumber)

Returns a string that contains the Excel name of a column when passed the number of the column. Excel names columns alphabetically. The first column is A, the second B and so on. After the 26th column (Z), two characters are used to present the column. Column 27 is AA, 28 is AB and so on. This method actually returns the characters used for any column up to 450,000. Version of Excel prior to Excel 2007 only supported 255 columns (so the final column was named IV). In Excel 2007 this limit has been increased to 16K.

This method is particularly useful when processing columns in a loop.

Parameters

long columnNumber:

Return Value

Returns a string that contains the name of the column.

Examples

This example writes the name of each cell to that particular cell for 100 rows and 320 columns

columnName      cstring(6)
col             long
row             long
  code 
    MyExcel.SetCalculations(oix:CalculationManual! Improve bulk insert performance 

    loop col = 1 to 320
        columnName = MyExcel.ColumnName(col)
        loop row = 1 to 100
            MyExcel.WriteToCell(columnName & row , columnName & row)
        end
    end

    MyExcel.SetCalculations(oix:CalculationAutomatic)

up icon

 

CountOpenWorkbooks( ) ,long,proc

TempLong = MyExcel.CountOpenWorkbooks()

  • Returns the number of currently open workbooks, or a negative value if an error occurred.
  • NOTE: At this time certain methods in Office Inside only support having one Workbook open at a time. 
  •  We don't recommend opening more than one at this stage, although this will change in future releases.

up icon

 

CountUsedRows     ( ) ,long,proc
TempLong = MyExcel.CountUsedRows()
  • Returns the bottom-most row number of all the used cells in the currently open worksheet, or a negative number if an error occurred.

up icon

 

CountWorksheets     ( ) ,long,proc
TempLong = MyExcel.CountSheets()
  • Returns the number of currently open worksheets, or a negative number if an error occurred.

up icon

 

Cut ( ) ,byte,proc
MyExcel.Cut()
  • ( Not yet implemented... )

up icon

 

DeleteSelection ( long pShift ) ,byte,proc
MyExcel.DeleteSelection (oix:Up)
  • Deletes the currently selected cells.  pShift can be one of the following equates:
    oix:Down
    oix:ToLeft
    oix:ToRight
    oix:Up

up icon

 

DeleteWorksheet ( ) ,byte,proc
MyExcel.DeleteWorksheet()
  • Deletes the currently selected WorkSheet.

up icon

 

ErrorTrap (string pErrorString, string pFunctionName)

This method is called when an error occurs. Office Inside provides embed points for this method (before parent call, and after parent call) where you can put code to deal with any errors Office Inside experiences (see the example code above - note the grey text indicates code generated by the Office Inside template, the black text is what you would add).

By default any errors that Office Inside encounters will be dealt with as follows:

  • First, this ErrorTrap method is called, where you can act on the error message as shown in the example code above.
  • Second, Outlook might display a message of its own.  You can suppress all messages by ticking the Suppress Error Messages check box in the procedure extension template.

Parameters

string pErrorString: A string containing the error description.

string pFunctionName: A string that contains the name of the function that the error occured in.

Examples

		
MyExcel.ErrorTrap PROCEDURE (string pErrorString, string pFunctionName)
  code
    PARENT.ErrorTrap (pErrorString, pFunctionName)
		
    if pErrorString = 'Init Failed'
HandleInitFailure()
end

Return Values

None

Notes

Note: This is a callback method. This means that unlike normal methods that you call in order to perform some function, the OfficeInside methods will call this method. This allows you to add your own code in this method to respond to to the particular event that triggered the method being called.

Note: Even if you suppress error messages the ErrorTrap method will still be called, however no message will be displayed to the user.

Note: The Suppress Error Messages checkbox simply generates a line of code that sets the SuppressErrorMessages property to true.  You can set this property manually if you prefer.

up icon

 

ExcelImport (string fileName, long progressControl=0, long columns, long startRow=1)

Note: For importing and exporting to and from Excel, we recommend using the OfficeInside oiExcelImpEx class, which handles importing to and from queues, files, groups and views.

Imports a file from Excel and allows each row to be processed. Loads a large number of file formats supported by Excel and processes each row in the file. For each row the TakeImportRecord() method is called to allow you to then process the row (record). The TakeImportRecord method is passed a queue that contains all the cells in the current row.

Parameters

string fileName: The file name to import. If an empty string ('') is passed the method will prompt the user for a file name.

long progressControl: An optional parameter that allows the a progress control handle to be passed to the method. The progress control will be updated as the import is done. Optional.

long columns: The number of columns to import. Required.

long startRow: The row to start at. This value is optional and default to importing from the first row (1).

Return Values

Returns 1 for success and zero for failure, or if the user cancels the import (only applies if no file name was passed and the user is prompted for a file name).

 

Examples

The below example demonstrates how to use the Who() and What() method to dynamically assign each field from the queue to a any file, based purely on field order. You could also use Who() to find fields with matching names. Alternatively you could fetch specific columns using the Get() function and assign them to specific fields rather than using a loop to do this dynamically.

MyExcel.ExcelImport('expenses.xls', ?Progress, 32)

! This will call the TakeImportRecord() method for each record,
! which allows the records to be processed (see below):

MyExcel.TakeImportRecord Procedure(long recordNumber, excelImportQType cellsQ)
ReturnValue   long                                                           

destField    
any
sourceField   any
i             long
  code                                                                       
    loop i = 1 to Records(cellsQ)
        Get(cellsQ, i)
        sourceField = Clip(cellsQ.cell)
        if Who(MyFile.Record, i) <> ''       ! If the field exists in the file assign to it.
            destField &= What(MyFile.Record, i)
            destField = sourceField
        end
    end
    if Access:MyFile.Insert()
        return level:fatal                   ! Error adding a record, cancel import
    end

    return level:benign                      ! continue importing
! Parent Call                                                                  
ReturnValue = parent.TakeImportRecord(recordNumber, cellsQ)                    
! [Priority 5000]                                                              


! End of "Class Method - Executable Code Section"                              
Return ReturnValue                                                             

 

up icon

 

ImportSetup ()

Note: For importing and exporting to and from Excel, we recommend using the OfficeInside oiExcelImpEx class, which handles importing and exporting to and from queues, files, groups and views.

 

This method is called by the ExcelImport method once the Excel spreadsheet has been opened, but before any data is imported. This allow the sheet to be changed, or the document otherwise manipulated or modified before the actual importing of individual records (rows) begins.

Parameters

None

Return Values

None

 

Examples

ThisExcel.ImportSetup()
  code

   
! Select worksheet to import using the worksheet name
   
self.SelectWorksheet('Sheet 2')  
    parent.ImportSetup()

 

ImportComplete ()

Note: For importing and exporting to and from Excel, we recommend using the OfficeInside oiExcelImpEx class, which handles importing and exporting to and from queues, files, groups and views.

 

This method is called by the ExcelImport method once the Excel spreadsheet has been completely imported, but but before the spreadsheet is closed. You can use this to do any cleanup, or take any actions once the import is completed that might need to be done on the Excel document.

Parameters

None

Return Values

None

 

Examples

ThisExcel.ImportComplete()
  code

   
! Save a copy of the imported document as a CSV file in an 'Imported' folder
    self.SaveAs(LongPath() & '\' & 'Imported\' |
              &
Format(Today(), @D6) & '.xls', oix:xlCSV)  
    parent.ImportComplete()

 

 

FreezePanes     ( byte pFreeze=true ) ,byte
MyExcel.FreezePanes ()
MyExcel.FreezePanes (false)
  • Freezes or unfreezes panes, depending on the value you pass in the pFreeze parameter (as shown in the example code above).  See the Excel help for more information.

up icon

GetCellBackgroundColor (string fromCell, string toCell) , long

Returns the background color of a cell or range of cells. The value returned is the palette position of the color, use the GetPaletteColor() and SetPaletteColor() to get and set the actual colors used.

Parameters

string fromCell

The starting cell address. Both cell addresses can be passed as empty strings to use the currently selection.

string toCell

The ending cell address. This can be passed as an empty string if a specific cell, or the current selection is being used.

Return Value

The palette position of the background color for the specific cells, or the current selection if no cell addresses are specified. If an error occurs the method returns -1 (color:none). The retrieve the color corresponding the the palette entry returned use the GetPaletteColor method.

Examples

curColor = oiExcel.GetCellbackgroundColor('', '')    ! Fetch the color for the currently selected cells
curColor = oiExcel.GetCellbackgroundColor('A1', '')  ! Fetch the color for a specific cell
curColor = oiExcel.GetCellbackgroundColor('D1', 'E7')! Fetch the color for the range of cells specified

 

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

 

GetColumnAddressFromColumnNumber (long pColumnNumber)

Important: This method is deprecated and has been replaced by ColumnName(). The new method removes the limitation of 26 columns and supports up to 450,000 columns (Excel 2007 removes the 255 column limit of previous version and supports 65535 columns). The GetColumnAddressFromColumnNumber method now just calls ColumnName().


MyExcel.WriteToCell ('Cell A1', (MyExcel.GetColumnAddressFromColumnNumber(1) & '1') )
MyExcel.WriteToCell ('Cell B1', (MyExcel.GetColumnAddressFromColumnNumber(2) & '1') )
MyExcel.WriteToCell ('Cell C1', (MyExcel.GetColumnAddressFromColumnNumber(3) & '1') )

  • Many methods in the Office Inside Excel object(s) take a cell address as one of the parameters.  An example of a cell address is "C5" for instance.  When coding (loops for instance), it is often inconvenient to have to enter a cell address using characters other than numbers, which is why we have this method.  The above example code shows how to write to cells A1, B1, and C1, using the numbers 1, 2, and 3 (in conjunction with this method) to make up valid cell addresses.  All this method does is takes a number (which is the column number, so column D would be column number 4 for instance), and returns the column "Address", as in "D".  You can use this method as shown above to convert numbers into valid cell addresses.

up icon

 

GetColumnWidth ( string pColumn ) ,string
loc:ColumnWidth = MyExcel.GetColumnWidth('B')
  • Returns the width of the column that you pass as pColumn, or a negative number if an error occurred.

up icon

 

GetInfo ( byte pOption ) ,string,proc
TempString = MyExcel.GetInfo (oix:WindowState)
  • This is a wrapper method which can be used to get various information, and read various properties, depending on the parameters which you pass.
  • The pOption parameter can be any one of the Excel GetInfo / Update Equates.

up icon

 

GetFileName (), string

Returns the file name and path on disk for the currently open document.

Paremeters

None

Return Value

Returns a string containing the file name and path of the current document. If the document was not opened (in other words, it is a new document rather than one loaded from disk), and has not been saved, then it returns the current name of the document, such as 'Document1'.


up icon

 

GetPaletteColor (byte pSwatch), long

Unlike MS Word, Excel stores colors in a palette - you can only use the colors contained in that palette.  If the default palette for a given worksheet does not contain the colors you want to use, you can change them using the SetPaletteColor method.

For two palette management functions for your applications see the Examples section below, as well as the section Color Management in Excel and Cell Formattting (recommended reading).

Parameters

byte pSwatch: The palette position (swatch) to return the color of.

Return Value

Returns a long value that contains the color of the swatch (palette entry) specified by the pSwatch parameter.

Notes (Important)

The color swatches are the "blocks" of color you see when you click the "Font Color" droplist on the Excel toolbar.  You will see there are 8 columns and 5 rows or swatches (totalling 40 swatches).  We have labelled these swatches as 1 being column 1 row 1, 2 being column 2 row 1, 8 being column 8 row 1, 9 being column 1 row 2, and so on.

By default, Office Inside supports using standard Clarion color equates, so long as you're using the default Excel palette, so you would simply pass COLOR:YELLOW to an Office Inside method, and it would then use the closest match to that color from the default palette.

If you would rather use the palette directly (which means that passing standard Clarion color equates would no longer work), so can do this by simply setting the InterpretClarionColorEquates property to false (after calling oiExcel.Init(), which will set it to true), as shown in the example code. If you do that, then any color parameter that you pass in needs to be the color swatch number.

Important: By default oiExcel.InterpretClarionColorEquates is set to true. This means that passing RGB color values, or colors stored in longs will not work. You must set this property to false (0) to pass colors as long value or as RBG colors (such as 0FF00FFh or a color value returned from the ColorDialog() function.

Examples

The first example below demonstrates how to create a local array of colors and a pair of procedures. This allows you to create your own color palette and synchronize it with that of Excel.

Example 1

AddPaletteColor(): This adds a palette color. Note that each time a color is added the variable numColors is incremented. In this case for versions of Excel prior to Excel 2007 you should limit this to 40 entries.

GetPaletteColor(): This locates a palette entry from the local palette by matching the passed color to each palette entry. You could expand on this to find the closest match. Unlike the oiExcel.GetPaletteColor method, this does not return a color at a position in a palette, it finds the color and returns the palette position.

! Local or module data
numColors         
long           ! number of colors used in the custom excel palette
colorPalette       long, dim(40)  ! colors in the excel palette
curColor           long           ! the current color in the excel palette
palettePos         long           ! position of the current color in the palette
 

! Adds a color to the local and Excel palette
AddPaletteColor
procedure(long pColor)
 
code
   
if NumColors > 39 and ExcelDoc.GetOfficeVersion < 12   ! Limit colors for old versions
       
! can do error handling here, or choose which color entry to replace etc.
       
return
   
end
    numColors += 1
    colorPalette[numColors] = pColor
    curColor = pColor
    palettePos = numColors

   
! add the color to the excel palette
    ExcelDoc.SetPaletteColor(numColors, pColor)     


! This locates a passed color in the palette and returns the position 
GetPaletteColor
procedure(long pColor)
i              long
 
code
   
loop i = 1 to 40
       
if colorPalette[i] = pColor
            curColor = colorPalette[i]
            palettePos = i
           
return i
       
end
   
end

   
return 0

Example 2

! Let the user pick a color (RGB)
if ColorDialog('Select a color', tempLong)
   
! Set InterpretClarionColorEquates to false and pass RGB colors,
    ! Not Clarion color equates.

    MyExcel.InterpretClarionColorEquates =
false 

 
  ! Set swatch 33 to this RGB color (column1, row5 in palette)
    MyExcel.SetPaletteColor (33, tempLong)       
 
 
  ! Set cell B2 to the color in swatch 33, which we just set
    MyExcel.SetCellBackgroundColor (33, 'B2')    
end

Example 3

curColor2 = MyExcel.GetPaletteColor(2) ! Fetch and store the current color
MyExcel.SetPaletteColor(2,
color:red! Replace it with red (Clarion color equate)
curColor3 = MyExcel.GetPaletteColor(3)

MyExcel.InterpretClarionColorEquates =
false
MyExcel.SetPaletteColor(3, 0FF00CCh)  
! Set to an RGB color value
! Set cell B2 to the color in swatch 33, which we just set
MyExcel.SetCellBackgroundColor(3, 'B2') 

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

 

up icon

 

GetRowHeight ( string ) ,string
X = MyExcel.GetRowHeight (5) ! get height for row 5
  • Returns the height of a row (see example above)

up icon

 

HideFormulaBar ( ) ,byte,proc
MyExcel.HideFormulaBar()
  • Makes the Excel Formula Bar not visible.
  • Returns true (1) if no problems were experienced

up icon

 

HideStatusBar ( ) ,byte,proc
MyExcel.HideStatusBar()
  • Makes the Excel Status Bar not visible.
  • Returns true (1) if no problems were experienced

up icon

 

HideToolbar (long pToolbar) ,byte,proc
MyExcel.HideToolbar(oix:ToolbarStandard)
  • Hides the toolbar specified in the pToolbar parameter.  For a list of valid Toolbar equates click here.
  • Returns true (1) if no problems were experienced.

up icon

 

ImportXLSFile (string pFileName, long pProgressControl=0, long pColums=10)

Important: This method is deprecated. Please use the new ExcelImport method.

This method provides an easy way to import an Excel spreadsheet.  You simply call this method, and then Office Inside will read the data out of the spreadsheet, one row at a time.  As it reads a row, the object's ImportXLSFile_TakeRecord method will be called, and the data for that row will be contained in the parameters for the ImportXLSFile_TakeRecord method.  There are actually two different ImportXLSFile_TakeRecord methods. One only passes in columns A through to J (default), the other passes in columns A through to Z.  Only one of these two ImportXLSFile_TakeRecord methods will be called. Which one called is determined by the value of the pColumns parameter:

  • If pColumns is omitted or is passed as 10, then only 10 columns are read in (The ImportXLSFile_TakeRecord method that passes in A through to J is fired)
  • If pColumns is omitted or is passed as 26, then 26 columns are read in (The ImportXLSFile_TakeRecord method that passes in A through to Z is fired)

Examples

Typically you would code as follows:

! Call ImportXLS file, to begin importing records
MyExcel.ImportXLSFile ('c:\temp.xls')

! Once you have called ImportXLSFile, the ImportXLSFile_TakeRecord method
! will fire once for every record that is read in, you have to put code
! into that method to actually write the record to your table, as follows:

MyExcel.ImportXLSFile_TakeRecord PROCEDURE (long pRowNumber, string pA, string pB, |
                                            string pC, string pD, string pE, string pF, |
                                            string pG, string pH, string pI, string pJ)

ReturnValue        byte
  CODE

    if pRowNumber > 10
        ! you don't have to do this, but you can return 
! record:OutOfRange to stop processing records
return Record:OutOfRange end if access:TempExcelTable.tryinsert() = level:benign TMP1:ColumnA = pA TMP1:ColumnB = pB TMP1:ColumnC = pC TMP1:ColumnD = pD TMP1:ColumnE = pE TMP1:ColumnF = pF TMP1:ColumnG = pG Access:TempExcelTable.update() end ReturnValue = PARENT.ImportXLSFile_TakeRecord(pRowNumber, pA, pB, pC, pD, | pE, pF, pG, pH, pI, pJ)

up icon

 

Init (byte startVisible=1, byte enableEvents=0)

Initialises the oiExcel object and the COM interface and performs all setup necessary to begin automating Excel, including starting Excel itself and controlling whether Excel starts as visible to the user and whether to enable event handling.

Parameters

byte startVisible: Determines whether Excel is visible at startup or not. Pass as 1 (the default) to make Excel visible, or pass zero to keep Excel hidden.

Note: You can also set whether Excel is visible after you have called the Init method by using the Update method, passing oix:ApplicationVisible as the property to set and 1 as the value to set it to.

byte enableEvents: Determines whether event handling is enabled. Defaults to zero (no event handling). Pass as 1 to enable event handling.

Return Values

Returns true (1) if successful and false (0) if the method fails.

Examples

! Initialise with the standard parameter values (Excel visible with no event handling)
MyExcel.Init()     

! Initialise with Excel hidden and event handling on:

up icon

 

InsertColumn ( <string pCellAddress>, byte pShiftOption=0 ) ,byte,proc
MyExcel.InsertColumn ()
MyExcel.InsertColumn ('C3')
  • Inserts a column to the left of the cell specified in the pCellAddress parameter.  If pCellAddress is not passed, then a column is inserted to the left of the currently selected column.
  • Returns true (1) if no problems were experienced.
  •  More options to follow.  The ShiftOption parameter is not yet supported. 

up icon

 

InsertRow ( <string pCellAddress>, byte pShiftOption=0 ) ,byte,proc
MyExcel.InsertRow ()
MyExcel.InsertRow ('C3')
  • Inserts a row above the cell specified in the pCellAddress parameter.  If pCellAddress is not passed, then a row is inserted above the currently selected row
  • Returns true (1) if no problems were experienced.
  •  More options to follow.  The ShiftOption parameter is not yet supported. 

up icon

 

InsertWorksheet ( ) ,byte,proc
MyExcel.InsertWorksheet()
  • Inserts a new worksheet into the currently loaded workbook.
  • Returns true (1) if no problems were experienced
  • More parameters to follow.

up icon

 

Kill ( byte pUnloadCOM=1 ) ,byte,proc

MyExcel.Kill()

  • Kills the oiExcel object and handles any cleaning up
  • Returns true (1) if no problems were experienced
  • Please do not use the pUnloadCOM parameter yet.  If you are going to call this method do so as per the example code above.

up icon

 

MergeCells ( string pFromCell, string pToCell ) , byte, proc

MyExcel.MergeCells ('B2', 'C4')

Merges the range of cells identified by the pFromCell and pToCell parameters.

up icon

 

NewWorkbook ( ) ,byte,proc
MyExcel.NewWorkbook()
  • Creates a new Workbook.
  • Returns true (1) if no problems were experienced.

up icon

 

OpenWorkbook ( <string pFileName> ) , long, proc

Opens a Workbook. The passed parameter is optional and if it omitted (or an empty string is passed), the user is prompted to select a Workbook to open. The file can be any type that is supported by the version of Excel installed.

Parameters

pFileName

The name of the file to open. Can be any format supported by the version of Excel installed. If pFileName is empty or omitted, then the user is prompted to select a file to open.

Return Value

Returns true (1) if no problems were experienced, zero if an error was encountered. If the method fails, then the ErrorTrap method is called with further information.

Examples

MyExcel.OpenWorkbook()       ! Prompts the user for the file name

MyExcel.OpenWorkbook(LongPath() & '\data.xlsx') ! Opens the specified file

 

up icon

 

Paste ( ) ,byte,proc

Same effect as "Edit --> Paste" in MS Excel.

Example

MyExcel.Paste()

up icon

 

PasteSpecial ( ) , byte, proc

This method has not been implemetned in this realease.

Example

MyExcel.PasteSpecial()

Notes

Not yet implemented...

up icon

 

PrintWorksheet Procedure(*oixPrintProperties printSettings)

! Excel print settings
oixPrintProperties            group, type
pFrom                           long                        ! The number of the page at which to start printing. If this argument is omitted, printing starts at the beginning. 
pTo                             long                        ! The number of the last page to print. If this argument is omitted, printing ends with the last page. 
copies                          long                        ! The number of copies to print. If this argument is omitted, one copy is printed. 
pPreview                        long                        ! True to have Microsoft Excel invoke print preview before printing the object. False (or omitted) to print the object immediately. 
activePrinter                   cstring(200)                ! Sets the name of the active printer.
printToFile                     long                        ! True to print to a file. If PrToFileName is not specified, Microsoft Excel prompts the user to enter the name of the output file. 
collate                         long                        ! True to collate multiple copies.
! These two properties are not supported by the _PrintOut() method, which is being used, as the PrintOut() method always fails.
prToFileName                    cstring(260)                ! If PrintToFile is set to True, this argument specifies the name of the file you want to print to. 
ignorePrintAreas                long                        ! True to ignore print areas and print the entire object.
                              end    


PrintSheet Procedure(*oixPrintProperties printSettings)

PrintWorkbook Procedure(*oixPrintProperties printSettings)

_PrintOut Procedure(*oixPrintProperties printSettings, long pType=oix:PrintWorkbook)

PrintMe ( ) ,byte,proc

Prints the current document to the default system printer.

This method has been deprecated, see the PrintWorkbook and PrintWorksheet for the newer, more extensive methods).

Return Value

Returns true (1) if no problems were experienced and False (0) if an error occurs.

Examples

MyExcel.PrintMe()

up icon

 

 

PrintPreview ( )  ,byte, proc
MyExcel.PrintPreview()
  • Returns true (1) if no problems were experienced.
  • More options to follow in future releases...

up icon

 

 

ProtectWorkbook ( string pPassword, byte pStructure, byte pWindows ) , byte, proc

This is the equivalent of "Tools" --> "Protection" --> "Protect Workbook" in MS Excel.  If you are unfamiliar with protecting sheets refer to the MS Excel documentation (search for "Protect workbooks from changes").

 

up icon

 

 

ProtectWorksheet     ( string pPassword, byte pDrawingObjects, byte pContents, byte pScenarios, byte pUserInterfaceOnly ) ,byte,proc
MyExcel.ProtectWorksheet('MyPassword', true, true, true, false)
  • This is the equivalent of "Tools" --> "Protection" --> "Protect Sheet" in MS Excel.  If you are unfamiliar with protecting sheets refer to the MS Excel documentation (search for "Protect worksheets from changes").

up icon

 

 

ReadCell (<string pCellAddress>, byte pFormula=false) , string

Reads a value from a cell, which is returned as a string. There is a limit of 255 characters for the returned data.

  • Parameters:
    • pCellAddress: The Excel address of the cell to be read using the standard Excel format such as 'B3', 'AC22', where the letter represents the column and the number represents the row.
    • pFormula: Optional parameter that allows you to read the formula for the cell rather than the actual data. For example if the cell contains
      'A3*B3', which gives a value of 140 then passing pFormula as FALSE will return the value '140', whereas setting pForumula to TRUE will return 'A3*B3'.
  • Return Value:
    • Returns a string containing the cell contents, with a maximum of 255 characters.
  • Example:
cellValue         string(255)
cellFormula       string(255)

code
  cellValue = MyExcel.ReadCell('B3')   cellFormula = MyExcel.ReadCell('B3', true)

up icon

 

 

Redo ( ) ,byte,proc
MyExcel.Redo()
  • Same as "Redo" in Excel.
  • Returns true (1) if no problems were experienced.

up icon

 

 

RenameWorksheet ( string pName ) ,byte,proc
MyExcel.RenameWorksheet ('Sales')
  • Renames the currently selected worksheet.  You can use the SelectWorksheet method to select a worksheet, and can then call this method to rename it.

up icon

 

 

ReplaceText ( string pFindText, string pReplaceText, byte pReplaceAll=false ) ,byte,proc
MyExcel.ReplaceText ('Cape', 'Soft', true)
  • Searches for the text passed as pFindText, and replaces it with the text passed in pReplaceText

up icon

 

 

Save     ( ) ,byte,proc
MyExcel.Save()
  • Saves the current workbook, using its existing name.
  • Returns true (1) if no problems were experienced.

up icon

 

 

SaveAs (<string FileName>, <long fileType>)

Saves the current workbook using the name provided in the FileName parameter. Also optionally allows the File Type to be specified, which is particularly useful with Office 2007, which default to a new XLSX format, and displays a "Compatibility Warning" dialog box when saving a file that has the .XLS extension.

Return Values

Returns true (1) if no problems were experienced and zero (0) if the function failed. When an error occurs the ErrorTrap() method is called with error information, if you have debug logging enabled this will be output to the debug log and can be viewed using DebugView.

Parameters

string fileName (optional): Specified the name of the file that the document should be saved as, and should include the full path if the file is not saved in the current directory. If no file name is specified the user is prompted for a name.

long fileType (optional): A value that determines the type of file saved. This can be used to override the default Excel file type. For example in Excel 2007 the default file type is .XLSX, which cannot be opened by previous versions of Excel. This can be used to save as a standard XLS file, which is compatible with Excel 97, 2000, XP and 2003. The below table shows a list of all possible values, and the equates provided for the values, as well as a description of the file type.

Name Value Description
oix:xlAddIn 18 Microsoft Excel 2007 Add-In
oix:xlAddIn8 18 Microsoft Excel 97-2003 Add-In
oix:xlCSV 6 CSV
oix:xlCSVMac 22 Macintosh CSV
oix:xlCSVMSDOS 24 MSDOS CSV
oix:xlCSVWindows 23 Windows CSV
oix:xlCurrentPlatformText -4158 Current Platform Text
oix:xlDBF2 7 DBF2
oix:xlDBF3 8 DBF3
oix:xlDBF4 11 DBF4
oix:xlDIF 9 DIF
oix:xlExcel12 50 Excel12
oix:xlExcel2 16 Excel2
oix:xlExcel2FarEast 27 Excel2 FarEast
oix:xlExcel3 29 Excel3
oix:xlExcel4 33 Excel4
oix:xlExcel4Workbook 35 Excel4 Workbook
oix:xlExcel5 39 Excel5
oix:xlExcel7 39 Excel7
oix:xlExcel8 56 Excel8
oix:xlExcel9795 43 Excel9795
oix:xlHtml 44 HTML format
oix:xlIntlAddIn 26 International Add-In
oix:xlIntlMacro 25 International Macro
oix:xlOpenXMLAddIn 55 Open XML Add-In
oix:xlOpenXMLTemplate 54 Open XML Template
oix:xlOpenXMLTemplateMacroEnabled 53 Open XML Template Macro Enabled
oix:xlOpenXMLWorkbook 51 Open XML Workbook
oix:xlOpenXMLWorkbookMacroEnabled 52 Open XML Workbook Macro Enabled
oix:xlSYLK 2 SYLK
oix:xlTemplate 17 Template
oix:xlTemplate8 17 Template 8
oix:xlTextMac 19 Macintosh Text
oix:xlTextMSDOS 21 MSDOS Text
oix:xlTextPrinter 36 Printer Text
oix:xlTextWindows 20 Windows Text
oix:xlUnicodeText 42 Unicode Text
oix:xlWebArchive 45 Web Archive
oix:xlWJ2WD1 14 WJ2WD1
oix:xlWJ3 40 WJ3
oix:xlWJ3FJ3 41 WJ3FJ3
oix:xlWK1 5 WK1
oix:xlWK1ALL 31 WK1ALL
oix:xlWK1FMT 30 WK1FMT
oix:xlWK3 15 WK3
oix:xlWK3FM3 32 WK3FM3
oix:xlWK4 38 WK4
oix:xlWKS 4 Worksheet
oix:xlWorkbookDefault 51 Workbook default
oix:xlWorkbookNormal -4143 Workbook normal
oix:xlWorks2FarEast 28 Works2 FarEast
oix:xlWQ1 34 WQ1
oix:xlXMLSpreadsheet 46 XML Spreadsheet

 Remarks

Excel 2007 has a new default file type which is .XLSX. This format cannot be opened by previous versions of Excel. The fileFormat parameter can be used to save as a standard XLS file, which is compatible with Excel 97, 2000, XP and 2003 by passing oix:xlWorkbookNormal. Use oix:xlWorkbookDefault to allow Excel to save in the default format specified, which in Excel 2007 is always the new XLSX. The changes made to Excel 2007 result in unexpected behaviour if you assume a certain format when saving. If the incorrect file extension is specified the file will be saved in the default, or the specified format, but when opened it will display an error warning the user that the file extension is incorrect and that the file may be dangerous. If you choose to save the document as an Excel 97-2003 compatible XLS document by setting the fileFormat parameter to oix:xlWorkbookNormal, then depending on the content of the document Excel may display a compatiblity warning dialog box (which may or may not be displayed in front of your application).

Important: Excel 2007 allows the user to set the default format for saving documents (such as the new XLSX or old XLS format). This setting has no effect on the default format saved when calling OfficeInside via the COM interface, oix:xlWorkbookDefault will always save as a Excel 2007 .xlsx document!

Important: If the document already exists then Excel will prompt the user to overwrite the existing document.

Examples

! Dangerous in Excel 2007 this may save as an xlsx file with an .xls extension
! which will display a warning when the document is opened, and may display
! a compatibility dialog when the document is saved in Excel 2007
MyExcel1.SaveAs(' test.xls ')

! Save in the default format and allow Excel to set the correct extension
MyExcel1.SaveAs('
test', oix:xlWorkbookDefault)   

! Prompt the user
MyExcel1.SaveAs()    

! Save as a "normal" workbook (.xls), may display a compatibility dialog in Excel 2007
MyExcel1.SaveAs(LongPath() & '\' & Clip(excelFileName), oix:xlWorkbookNormal) 

up icon

 

 

Search     ( ) ,byte,proc
MyExcel.Search()
  • ( Not yet implemented... )

up icon

 

 

SelectCells     ( string <pFromCell>, <string pToCell> ) ,byte,proc
MyExcel.SelectCells ('B3')
MyExcel.SelectCells ('B3', 'D12')
MyExcel.SelectCells () ! Ctrl-A
  • Selects a cell, or a range of cells, depending on whether you pass one parameter or two (as per code examples above)
  • If you pass an empty string in the pFromCell parameter (see 3rd example above), then all cells will be selected (same as Ctrl-A)
  • Returns true (1) if no problems were experienced

up icon

 

 

SelectColumns     ( string pFromColumn, <string pToColumn> ) ,byte,proc
MyExcel.SelectColumns ('B')
MyExcel.SelectColumns ('B', 'D')
  • Selects a column, or a number of columns, depending on whether you pass one parameter or two (as per code examples above).
  • Returns true (1) if no problems were experienced.

up icon

 

 

SelectRows     ( string pFromRow, <string pToRow> ) ,byte,proc
MyExcel.SelectRows (3)
MyExcel.SelectRows (3, 8)
  • Selects a row, or a number of rows, depending on whether you pass one parameter or two (as per code examples above).
  • Returns true (1) if no problems were experienced.

up icon

 

 

SelectWorksheet     ( <string pSheetName>, long pSheetNumber=0 ) ,byte,proc
MyExcel.SelectWorksheet ('Sheet2')
MyExcel.SelectWorksheet ('My Sheet')
MyExcel.SelectWorksheet ('', 2)
  • Selects a worksheet in the currently loaded Workbook.
  • Either pass the name of the worksheet as the pSheetName parameter, or pass the number of the sheet using the pSheetNumber parameter.
  • Returns true (1) if no problems were experienced.

up icon

 

 

SetCalculations (long calcType)

When data is written to Excel by default it automatically checks whether the data is a formula and performs any needed calculation on the cell. This can slow down writing of data dramatically, although it ensures that all cells in the workbook are always up to date. In order to speed up writing data to Excel this method allow calculation to be set to manual, so data can be written, and then any calculations needed can be performed by calling the Calculate method

Parameters

long calcType: determines how Excel will handle inserted data. Can be one of three values:

  • oix:CalculationAutomatic: The default, performs calculations to ensure all cells are up to date every time data is inserted. Only recommended if the user is interacting with the data and calculations need to be done in real time.
  • oix:CalculationManual: The recommended option when doing bulk inserts or building Excel worksheets without user interaction. Provides a dramatic performance increase.
  • oix:CalculationSemiautomatic: Turns off most calculation.

Return Value

Returns 1 if the function succeeds, and zero for failure. If the function fails the ErrorTrap() method is called with information relating to the error.

Examples

ExcelDoc.SetCalculations(oix:CalculationManual)
! Screen updating can be turned off to provide a small performance boost
! in cases where the window is hidden or the user does not need to see the
! document being updated

ExcelDoc.SetScreenUpdating(
false)
! Write the required data to the worksheet here...

! Manually calculate (if necessary)
ExcelDoc.Calculate()

! Automatic calculation can be optionally turned back on:
ExcelDoc.SetCalculations(oix:CalculationAutomatic)

up icon

 

 

SetScreenUpdating (long updateScreen)

This method allows screen updating (redrawing) to be turned off and on. Turn screen updating off can provide a small performance improvement for batch inserts etc. It is only recommended in cases where the user does not have to see or interact with the document. The same method can be used with the oiWord object to slightly improve performance. For a large performance increase when doing bulk updates or insert see the SetCalculations method.

Parameters

long updateScreen: Set this to 1(true) to turn screen updating on, and to zero (false) to turn screen updating off.

Return Value

Returns 1 if the function succeeds, and zero for failure. If the function fails the ErrorTrap() method is called with information relating to the error.

Examples

ExcelDoc.SetCalculations(oix:CalculationManual)
! Screen updating can be turned off to provide a small performance boost

! in cases where the window is hidden or the user does not need to see the
! document being updated

ExcelDoc.SetScreenUpdating(false)

! Write the required data to the worksheet here...


! Manually calculate (if necessary)
ExcelDoc.Calculate()

! Automatic calculation can be optionally turned back on

ExcelDoc.SetCalculations(oix:CalculationAutomatic)

up icon

 

 

SetCellAlignment (long option, string value, <string fromCell>, <string toCell>), long, proc

Sets the alignment in the cells identified by the pFromCell and pToCell parameters. If these parameters are omitted then this applies to the current selection (so you can select a range of cells and then set the alignment without passing the fromCell and toCell parameters. This method allows the horizontal and vertical alignment to be set, as well as text wrapping and text orientation (the angle that the text in the cell is drawn at). The option parameter specifies which property to set and the value parameter specifies what to see it to. See below for a list of equates and values that may be passed.

Parameters

long option: Specifies which option is being set. This parameter determines what the method actually does. Can have one of the following values:

  • oix:WrapText - Sets whether text wrapping is enabled for the cells. If text wrapping is on then text that would not fit in the cell is wrapped onto the next line. The value parameter determines whether text wrapping is turned on (1) or off (0) for the cells.
  • oix:HorizontalAlignment - set the horizontal alignment for the currently selected cells, or the range of select of cells if fromCell and toCell are passed.
    Supported values for the value parameters are:
    • oix:xlCenter - aligns the contents of the cell in the centre of the cell
    • oix:xlLeft - aligns the cell contents left
    • oix:xlRight - aligns the cell contents right
    • oix:xlDistributed - distributes (spreads) the contents across the cell
    • oix:xlJustify - justifies the contents of the cell (each line is spread to the width of the cell)
  • oix:VerticalAlignment - sets the vertical alignment for the cell. By default, Excel aligns text vertically on the bottom of a cell.
    The value parameter may be one of the following:
    • oix:xlTop - aligns the cell contents with the top of the cell
    • oix:xlBottom - aligns the cell contents with the top of the cell
    •  oix:xlCenter  - aligns the cell contents in the centre of the cell
    • oix:xlDistributed  - distributes the cell contents within the cell
    • oix:xlJustify  - similar to Distributed, spreads the contents out from the top to the bottom of the cell
  • oix:Indent - Indents cell contents from any edge of the cell, the value passed is the amount to indent by. The unit used is the width of a single character. So an indent to 3 would indent by the width of 3 characters.
  • oix:ShrinkToFit -Reduces the apparent size of font characters so that all data in a selected cell fits within the column. The character size is adjusted automatically if you change the column width. The applied font size is not changed.
  • oix:Orientation - Change the orientation of text in selected cells. Rotation options may not be available if other alignment options are selected. This rotates the text in the selected cells.
    The value passed can be an integer from -90 to 90 degrees, or one of the following values:
    • oix:Downward
    • oix:Horizontal
    • oix:Upward
    • oix:Vertical
  • oix:TextDirection - sets the reading order of the cell. Can be used for languages such as Arabic and Hebrew that are read from right to left.
    The value parameter may be one of the following:
    • oix:Context - allow Excel to use the default based on the context
    • oix:LTR - use Left to Right reading order, the standard for most languages
    • oix:RTL - use Right to Left reading order for Hebrew, Arabic etc.

string value: The value to set the specified option to. The values for each option are listed under that option above.

string fromCell: An optional parameter specifying the cell or start of a range of cells to apply the option to. The fromCell and toCell parameters are optional, and if they are not passed then the current selection in the document is used. If a single cell is passed in the fromCell parameter and toCell is not passed then only that single cell is affected. If both fromCell and toCell are specified they are used to determine the rectangular region of cells that are affected. Both fromCell and toCell must specify an Excel cell name. For example A1, B22, BA2 etc. You can use the ColumnName() method to convert a column number into the Excel name for that column.

string toCell: An optional parameter specifying the cell or start of a range of cells to apply the option to. See the description for the fromCell parameter above for more information.

Return Values

The method returns 1 if successful and zero if it failed.

If an error occurs the ErrorTrap() method is called with the description of the error. If debug output is turned on (for example using a command line parameter) then ErrorTrap() will log the error to the system debug log (viewable using DebugView from www.sysinternals.com). See the debugging section for more information.

Examples

! Align the current selection right
MyExcel.SetCellAlignment(oix:HorizontalAlignment, oix:xlRight)

! Turn text wrapping on for a range of cells
MyExcel.SetCellAlignment(oix:WrapText,
true, 'B2', 'C4 ')

! Turn text wrapping off for a range of cells
MyExcel.SetCellAlignment(oix:WrapText,
false, 'B2', 'C4')

up icon

 

 

SetCellBackgroundColor     ( long olor, <string pFromCell>, <string pToCell> ) ,byte,proc

Changes the background color for a cell, or a range of cells.

Parameters

  • The pColor parameter can be one of the following equates (click here).
  • If you want to change a range of cells, use both the pFromCell and pToCell parameters, as in the code above.  You can also pass a single cell address in pFromCell to set the color for one cell only, or you can omit pFromCell and pToCell to change the background in the currently selected cell / range.
  • If you want to change a single cell's background, pass that cell address as the pFromCell parameter, as above, leaving the pToCell parameter empty.

Return Value

Returns True (1) if no problems were experienced and False (0) if the method fails.

Examples

MyExcel.SetCellBackgroundColor (oix:ColorYellow)
MyExcel.SetCellBackgroundColor (oix:ColorYellow, '
D5')
MyExcel.SetCellBackgroundColor (oix:ColorRed, '
B2', 'C5')
MyExcel.SetCellBackgroundColor (oix:Color55, '
B2', 'C5')

Remarks

Important: By default, Office Inside supports using standard Clarion color equates, so long as you're using the default Excel palette, so you would simply pass COLOR:YELLOW to an Office Inside method, and it would then use the closest match to that color from the default palette.  If you would rather use the palette directly (which means that passing standard Clarion color equates would no longer work), so can do this by simply setting the InterpretClarionColorEquates property to false (after calling oiExcel.Init, which will set it to true). If you do that, then any color parameter that you pass in needs to be the color swatch number.

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

 

up icon

 

 

SetCellBorders     ( string pFromCell, string pToCell, long pBorder, long pLineStyle, <long pWeight>, <long pColorIndex> ) ,byte,proc

MyExcel.SetCellBorders ('D5', '', oix:BorderEdgeBottom, oix:LineStyleDouble)
MyExcel.SetCellBorders ('B2', 'C6', oix:BorderEdgeBottom, oix:LineStyleContinuous)
MyExcel.SetCellBorders ('B2', 'C6', oix:BorderEdgeTop, oix:LineStyleContinuous)
MyExcel.SetCellBorders ('B2', 'C6', oix:BorderEdgeLeft, oix:LineStyleContinuous)
MyExcel.SetCellBorders ('B2', 'C6', oix:BorderEdgeRight, oix:LineStyleContinuous)
MyExcel.SetCellBorders ('B2', 'C6', oix:BorderInsideHorizontal, oix:LineStyleContinuous)
MyExcel.SetCellBorders ('B2', 'C6', oix:BorderInsideVertical, oix:LineStyleContinuous)
MyExcel.SetCellBorders ('D5', '', oix:BorderEdgeBottom, oix:LineStyleNone, oix:BorderWeightThick, color:red)

  • Changes the borders for a cell, or a range of cells
  • The pBorder parameter can be one of the equates listed here.
  • The pLineStyle parameter can be one of the equates listed here.
  • The pWeight parameter can be one of the equates listed here, or can be omitted (in which case the default weight is used).
  • The pColorIndex parameter can be any of the standard Clarion color equates, or can be omitted (in which case color:black is used).
  • Please note that using the pLineStyle and the pWeight parameters can cause conflicts, as in using a Double Line Style with a Thick Weight doesn't actually work in Excel, so it won't work in Office Inside.  Office Inside resolves these "conflicts" for you by simply ignoring the pWeight parameter if you pass a pLineStyle which does not also support using a pWeight.
  • If you want to change a range of cells, use both the pFromCell and pToCell parameters, as in the code above.  You can also pass a single cell address in pFromCell, or pass nothing in the pFromCell and pToCell parameters to work with the currently selected cell / range.
  • Returns true (1) if no problems were experienced.

 

SetCellFontColor     ( long pFontColor, <string pFromCell>, <string pToCell> ) ,byte,proc

Sets the Font Color inside a cell / range of cells.

Parameters

long pFontColor

The font color for the cells. The type of the value passed is dependant on the .interpretClarionColorEquates property. If this is set to True, then the passed value is a Clarion color equate, such as Color:Yellow or Color:Red. If this is set to False (0), then the value passed is a position in the Excel palette. See the Remarks and See Also sections for palette and color management information.

string pFromCell (optional)

If specified this is the starting cell of the selection to set the font color for. If this is not passed the current selection is used. If pFromCell is passed and pToCell is omitted, then the font color is set for the single cell.

string pToCell (optional)

If specified this determines the end of the selection to set the font color for (all cells from pFromCell to pToCell will be modified). If both pFromCell and pToCell are omitted, then the currently selected cells are modified. If only pFromCell is passed, then just that cell is modified.

Return Value

Returns true (1) if no problems were experienced.

Remarks

Important: By default, Office Inside supports using standard Clarion color equates, so long as you're using the default Excel palette, so you would simply pass COLOR:YELLOW to an Office Inside method, and it would then use the closest match to that color from the default palette.  If you would rather use the palette directly (which means that passing standard Clarion color equates would no longer work), so can do this by simply setting the InterpretClarionColorEquates property to false (after calling oiExcel.Init, which will set it to true). If you do that, then any color parameter that you pass in needs to be the color swatch number.

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

Examples

MyExcel.SetCellFontColor (color:red) ! currently selected cell/range

MyExcel.SetCellFontColor (color:red, 'A1') ! cell A1

MyExcel.SetCellFontColor (color:red, 'A1', 'C3') ! range A1:C3

up icon

 

 

SetCellFontName     ( string pFontName, <string pFromCell>, <string pToCell> ) ,byte,proc
MyExcel.SetCellFontName ('Verdana') ! currently selected cell
MyExcel.SetCellFontName (
'Arial', 'A1') ! cell A1
MyExcel.SetCellFontName (
'Verdana', 'A1', 'C3') ! cell range A1:C3
  • Sets the Font name for a cell / range of cells
  • Returns true (1) if no problems were experienced.

up icon

 

 

SetCellFontSize     ( long pFontSize, <string pFromCell>, <string pToCell> ) ,byte,proc
MyExcel.SetCellFontSize (16) ! currently selected cell / range
MyExcel.SetCellFontSize (20, 'A1') ! cell A1
MyExcel.SetCellFontSize (16, 'A1', 'C3') ! range A1:C3
  • Sets the Font Size for a cell / a range of cells
  • Returns true (1) if no problems were experienced

up icon

 

 

SetCellFontStyle     ( string pFontStyle, <string pFromCell>, <string pToCell> ) ,byte,proc
MyExcel.SetCellFontStyle ('Bold') ! currently selected cell/range
MyExcel.SetCellFontStyle ('Bold Italic', 'A1') ! cell A1
MyExcel.SetCellFontStyle ('Bold', 'A1', 'C3') ! range A1:C3
  • Sets the Font Style for a cell / a range of cells.
  • The FontStyle parameter can be one of the following:
    • 'Bold'
    • 'Regular'
    • 'Italic'
    • 'Bold Italic'
  • Returns true (1) if no problems were experienced.

up icon

 

 

SetCellFontUnderline     ( long pUnderlineStyle, <string pFromCell>, <string pToCell> ) ,byte,proc
MyExcel.SetCellFontUnderline (oix:UnderlineStyleDouble)
MyExcel.SetCellFontUnderline (oix:UnderlineStyleNone, 'A1')
MyExcel.SetCellFontUnderline (oix:UnderlineStyleDouble, 'A1', 'C3')
  • Underlines the text in a cell / range of cells.
  • See the Underline Style Equates for a list of valid values / equates to pass as the Underline parameter.
  • Returns true (1) if no problems were experienced.

up icon

 

 

SetCellNumberFormat (long pFormatType, long pSymbol=-1, long pDecimalPlaces=-1, long pSubType=-1, <string pFromCell>, <string pToCell>)

Sets the format for the currently selected cells, or optionally for the cell range specified by the pFromCell and pToCell parameters. This method is equivalent to right clicking on the cells and choosing the Format option in Excel.

Parameters

string pFromCell and string pToCell : used to specify the range which is going to be formatted.  If you only want to format a single cell either pass the same address in both parameters, or pass the second parameter as nothing ( ' ' ).  e.g. ('A1', 'A1'...) or ('A1', '', ...)

long pFormatType: Must be one of the following equates:

  • oix:NumberFormatGeneral
  • oix:NumberFormatNumber
  • oix:NumberFormatCurrency
  • oix:NumberFormatDate
  • oix:NumberFormatTime
  • oix:NumberFormatPercentage
  • oix:NumberFormatScientific
  • oix:NumberFormatText

long pSymbol: Only applies if your FormatType is Currency or Accounting (see list above).  This is used to set the currency symbol displayed with your numbers.  pSymbol can be one of the following:  ( if your currency is not listed here please see the Note section below for using any format supported by Excel)

  • oix:CurrencySymbolNone
  • oix:CurrencySymbolDefault
  • oix:CurrencySymbolUKPound
  • oix:CurrencySymbolUSDollar
  • oix:CurrencySymbolSARand

long pDecimalPlaces: Only applies if your formatType is Number, Currency, Accounting, Percentage or Scientific.  As the name implies, it is used to set the number of decimal places.

long  pSubType: Only applies if your FormatType is Date, Time, Fraction, Special or Custom.  Implementation is as follows:

If FormatType is Date, then pSubType can be 1 through to 13 - examples of these date formats are:

  1. 9/7
  2. 9/7/03
  3. 09/07/03
  4. 7-Sep
  5. 7-Sep-03
  6. 07-Sep-03
  7.  Sep-03
  8. September-03
  9. September 7, 2003
  10. S
  11. S-03
  12.  9/7/2003
  13. 7-Sep-2003

If FormatType is Time, then pSubType can be 1 through 6 - examples of these time formats are:

  1. 13:30
  2. 1:30 PM
  3. 13:30:55
  4. 1:30:55 PM
  5. 30:55.7
  6. 37:30:55

Examples

MyExcel.WriteToCell ('5.98', 'A3')
MyExcel.SetCellNumberFormat(oix:NumberFormatCurrency, oix:CurrencySymbolUnitedKingdom, 2, , '
A3')

Notes: Additional Formats, Fractions, Custom Pictures and Currencies.

If formatType is Fraction, Special or Custom, then this method does not support it, however you can call an internal method to set the value to any value supported by Excel. You need to call the following method:

    oiExcel._WrapperRange (16, '', '', formatString, '', '', '')

The formatString parameter contains a picture for the string and the number of decimal places. This is a standard Excel format string, and exactly the
same string that works in Excel will work in this string:

    formatString = '#,##0.00'            ! no currency, 2 decimal places
    formatString = '
[$£-809]#,##0.000'   ! UK Pound, 3 decimal places
    formatString = '
[$$-409]#,##0.00'    ! US Dollar, 3 decimal places
    formatString = '
[$$-2409]# ##0.00'   ! Caribbean dollar, 2 decimal spaces

You can even use formats such as:

  formatString = '$#,##0;[Red]$#,##0'

Which uses the dollar sign and comma separator for thousands, and no decimal places. The second part after the semi colon will cause negative numbers to
be displayed in red (without a minus sign, sign it isn't explicitly stated in this case).

In order to get the format strings for all the currencies (or anything else) that you need, open Excel, right click on a cell, and choose Format from the popup menu. In the dialog box displayed select the currency format that you want to use. Once you have the currency format select, the number of decimal places etc. change from Currency to Custom in the Category list on the left. It will display the picture used for this format. For example the following option is selected from the dropdown:

$ English (Caribbean)

Then and enter 2 decimal spaces, and switch from Currency to Custom in the Category list. The following picture is display:

[$$-2409]# ##0.00

The value can be passed as the formatString parameter to the _WrapperRange method.

 

 

SetCellProtection ( byte pLocked, byte pFormulaHidden, <string pFromCell>, <string pToCell> ) , byte, proc
MyExcel.SetCellProtection (true, false) ! currently selected cell / range
MyExcel.SetCellProtection (true, true, 'A1') ! cell A1 MyExcel.SetCellProtection (false, false, 'A1', 'C4') ! range A1:C4
  • Cell Protection in Excel allows you to prevent users from changing your data, and seeing your formulae
  • To lTo learn more about this, in MS Excel choose "Format --> Cells", then choose the "Protection" tab.  As documented (in MS Excel), cell protection does not work unless you have protected the worksheet also.  This can be done using the Office Inside ProtectWorksheet method.

 

 

SetColumnWidth ( string FromColumn, <string ToColumn>, <string pWidth> )  ,byte, proc
MyExcel.SetColumnWidth ('A', 'C', '15.71')
MyExcel.SetColumnWidth (
'A', , '18.45')
  • Sets the column width for a column, or a range of columns.
  • If you omit the pWidth parameter, then the columns will AutoFit.
  • Returns true (1) if no problems were experienced.

up icon

 

 

SetPageView ( byte pPageView ) ,byte,proc
MyExcel.SetPageView (oix:PageBreakPreview)
  • Sets the current page view.
  • The pPageView parameter can be one of the following equates:
    • oix:NormalView
    • oix:PageBreakPreview
  • Returns true (1) if no problems were experienced.

up icon

 

 

SetPaletteColor (byte pSwatch, long pColor ), byte, proc

The colors in Excel are stored in a palette. Each block (or swatch) in the palette stores a particular color, and colors are referenced by their position in the palette, from 1 to 40. The color swatches are the "blocks" of color you see when you click the "Font Color" droplist on the Excel toolbar. This method sets the color for a particular position in the palette (swatch). Only colors in the palette can be used in the Excel worksheet, and changing a palette entry will change the color of anything that swatch was used for.

Please see the section Color Management in Excel and Cell Formattting (recommended reading).

Parameters

byte pSwatch

The position in the palette to set

long pColor

The color to set the specified color entry to. There are two ways of passing colors that are supported of OfficeInside. You can pass clarion color equates such as color:red, or you can pass the actual color values using a variable, or a numeric value such as 0FF0C0Ah. By default only Clarion color equates may be used. If you wish to pass the actual color values you must set the oiExcel.InterpretClarionColorEquates property to zero. It is set to 1 (true) by default.

Return Values

Returns 1 if the palette was successfully set and zero if the method failed. If an error occurs the ErrorTrap() method will be called with a string containing the error description.

Notes

By default, Office Inside supports using standard Clarion color equates, so long as you're using the default Excel palette, so you would simply pass COLOR:YELLOW to an Office Inside method, and it would then use the closest match to that color from the default palette.

Important: By default oiExcel.InterpretClarionColorEquates is set to true. This means that passing RGB color values, or colors stored in longs will not work. You must set this property to false (0) to pass colors as long value or as RBG colors (such as 0FF00FFh or a color value returned from the ColorDialog() function.

Examples

The first example below demonstrates how to create a local array of colors and a pair of procedures. This allows you to create your own color palette and synchronize it with that of Excel.

Example 1

AddPaletteColor(): This adds a palette color. Note that each time a color is added the variable numColors is incremented. In this case for versions of Excel prior to Excel 2007 you should limit this to 40 entries.

GetPaletteColor(): This locates a palette entry from the local palette by matching the passed color to each palette entry. You could expand on this to find the closest match. Unlike the oiExcel.GetPaletteColor method, this does not return a color at a position in a palette, it find the color and returns the palette position.

! Local or module data
numColors         
long           ! number of colors used in the custom excel palette
colorPalette       long, dim(40)  ! colors in the excel palette
curColor           long           ! the current color in the excel palette
palettePos         long           ! position of the current color in the palette 
    code

! Adds a color to the local and Excel palette
AddPaletteColor
procedure(long pColor)
 
code
   
if NumColors > 39 and ExcelDoc.GetOfficeVersion < 12   ! Limit colors for old versions
       
! can do error handling here, or choose which color entry to replace etc.
       
return
   
end
    numColors += 1
    colorPalette[numColors] = pColor
    curColor = pColor
    palettePos = numColors

   
! add the color to the excel palette
    ExcelDoc.SetPaletteColor(numColors, pColor)     


! This locates a passed color in the palette and returns the position 
GetPaletteColor
procedure(long pColor)
i              long
 
code
   
loop i = 1 to 40
       
if colorPalette[i] = pColor
            curColor = colorPalette[i]
            palettePos = i
           
return i
       
end
   
end

   
return 0

Example 2

! Let the user pick a color (RGB)
if ColorDialog('Select a color', tempLong)
   
! Set InterpretClarionColorEquates to false and pass RGB colors,
    ! Not Clarion color equates.

    MyExcel.InterpretClarionColorEquates =
false 

 
  ! Set swatch 33 to this RGB color (column1, row5 in palette)
    MyExcel.SetPaletteColor (33, tempLong)       
 
 
  ! Set cell B2 to the color in swatch 33, which we just set
    MyExcel.SetCellBackgroundColor (33, 'B2')    
end

Example 3

curColor2 = MyExcel.GetPaletteColor(2) ! Fetch and store the current color
MyExcel.SetPaletteColor(2,
color:red! Replace it with red (Clarion color equate)
curColor3 = MyExcel.GetPaletteColor(3)

MyExcel.InterpretClarionColorEquates =
false
MyExcel.SetPaletteColor(3, 0FF00CCh)  
! Set to an RGB color value

! Set cell B2 to the color in swatch 3, which we just set
MyExcel.SetCellBackgroundColor(3, 'B2') 

 

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

 

up icon

 

 

SetPrintArea ( string fromCell, string ToCell ) ,byte,proc
MyExcel.SetPrintArea('A3', 'B5')
  • Sets the current print area (same as "File" --> "Print Area" --> "Set Print Area" in Excel).
  • Returns true (1) if no problems were experienced.

up icon

 

 

SetRowHeight ( string pFromRow, <string pToRow>, <string pHeight> ) , byte, proc
MyExcel.SetRowHeight(5,8,'20.75')
MyExcel.SetRowHeight(9,,'20.75')
  • Sets the height of a row or a range of rows (see the code example above).
  • If you omit the pHeight parameter, then the rows will AutoFit.
  • Returns true (1) if no problems were experienced

up icon

 

 

ShowFormulaBar ( ) , byte, proc
MyExcel.ShowFormulaBar()
  • Makes the Excel Formula Bar visible.
  • Returns true (1) if no problems were experienced

up icon

 

 

ShowStatusBar ( ) , byte, proc
MyExcel.ShowStatusBar()
  • Makes the Excel Status Bar visible.
  • Returns true (1) if no problems were experienced

 

 

ShowToolbar ( long pToolbar ) , byte, proc
MyExcel.ShowToolbar(oix:ToolbarStandard)
  • Hides the toolbar specified in the pToolbar parameter.  For a list of valid Toolbar equates click here.
  • Returns true (1) if no problems were experienced

 

 

TakeEvent ( string pEventString1, string pEventString2, long pEventNumber=0, byte pEventType=0, byte pEventStatus=0 )
  • The TakeEvent method is not a method that you (typically) would call yourself.  It is a virtual method that fires, which you can write code into in order to deal with certain events.  See the offdemo.app (Excel Automation Example) example to see how we make use of this method.

 

 

TakeImportRecord (long recordNumber, excelImportQType cellsQ)

Note: We recommend using the oiExcelImpEx class for import and export to and from Excel. This class provides fully automated import and export to and from queues, groups, files (tables) and views. It allows columns and fields to be mapped in any order.

 

This callback method is called for each row (record) when importing a file from Excel using the ExcelImport method. This allows each row to be processed. The queue contains the value of each cell in the row. The maximum length of the data for any cell is 1024 characters. Each cell is stored in the cellsQ.cell queue property passed. You need to add code to this method in order to process imported record. See the Return Values section below for the values that you should return.

Parameters

long recordNumber: The record number being processed.

excelImportQType cellsQ: A queue that contains the values of all cells (fields) in the current row (record). The queue has the following structure:

excelImportQType queue
cell                 string(_oit:LargeStringSize)
                 end

The _oit:LargeStringSize equate defaults to 1024, which means that each cell may only contain up to 1024 characters.

Return Values

Return level:benign (zero) to indicate that processing should continue, and level:fatal to indicate that processing should halt (such as if the record is out of range).

Example

The below example demonstrates how to use the Who() and What() method to dynamically assign each field from the queue to a any file, based purely on field order. You could also use Who() to find fields with matching names. Alternatively you could fetch specific columns and assign them to specific fields rather than using a loop to do this dynamically.

This method is normally generated for you when you add an Excel object to the procedure. You then add code to process each record as demonstrated in the example below.

MyExcel.ExcelImport('expenses.xls', ?Progress, 32)

! This will call the TakeImportRecord() method for each record,
! which allows the records to be processed (see below):

MyExcel.TakeImportRecord Procedure(long recordNumber, excelImportQType cellsQ)
ReturnValue   long                                                           

destField    
any
sourceField   any
i             long
  code                                                                       
    loop = i = 1 to Records(cellsQ)
        sourceField &= What(cellsQ, i)
        if Who(MyFile.Record, i) <> ''           ! If the field exists in the file assign to it.
            destField &= What(MyFile.Record, i)
            destField = sourceField
            if Access.MyFile.Insert()
                return level:fatal               ! Error adding a record, cancel import
            end
        end
    end
    return level:benign                          ! continue importing
! Parent Call                                                                  
ReturnValue = parent.TakeImportRecord(recordNumber, cellsQ)                    
! [Priority 5000]                                                              


! End of "Class Method - Executable Code Section"                              
Return ReturnValue                                                             

 

up icon

 

 

Undo ( ) , byte, proc

MyExcel.Undo()

  • Same as "undo" in Excel.
  • Returns true (1) if no problems were experienced.

up icon

 

 

UnmergeCells ( string pFromCell, string pToCell ) , byte, proc

MyExcel.UnmergeCells ('B2', 'C4')

  • Use the Use the MergeCells method to merge cells, and use this method to unmerge them.

up icon

 

 

UnprotectWorkbook ( string pPassword ) , byte, proc
MyExcel.UnprotectWorkbook('MyPassword')

up icon

 

 

UnprotectWorksheet ( string pPassword ) , byte, proc
MyExcel.UnprotectWorksheet('MyPassword')

 

 

Update ( byte pOption, <string pValue> ) , byte, proc
TempByte = MyExcel.Update (oix:WindowState, oix:MinimizeWindow)
  • This is a wrapper method which can update one of several "properties" or "settings", depending on the parameters which you pass.
  • See the ExSee the Excel GetInfo / Update Equates for a list of valid values to use in the "pOption" parameter.
  • The pValue parameter (string) contains the new value which you want to update / change.
  • If you pass oix:WindowState as the first parameter, you can pass one of the following equates as the second parameter:
  • oix:RestoreWindow
  • oix:MaximizeWindow
  • oix:MinimizeWindow
  • Returns true (1) if no problems were experienced.

up icon

 

 

WriteToCell ( string pText, <string pCellAddress> ) , byte, proc
MyExcel.WriteToCell('Test')
MyExcel.WriteToCell('Test', 'B3')
  • Writes the text passed in to the cell identified in pCellAddress, or in the currently selected cell if pCellAddress is not passed
  • Returns true (1) if no problems were experienced

Example

This Example loops through a table and writes each row in the table to a row in Excel. The example only writes the first two fields, but each additional field is done in the same manner.

currentRow         long
  code
    MyExcel.NewWorkbook()
    currentRow = 1

    Set(MyFile)
    loop until Access:MyFile.Next()
        MyExcel.WriteToCell(MyFile.Field1, MyExcel.ColumnName(1) & currentRow)
        MyExcel.WriteToCell(MyFile.Field2, MyExcel.ColumnName(2) & currentRow)
        currentRow += 1
    end
    MyExcel.SaveAs(fileName)

The GetColumnAddressFromColumnNumber() method called above converts a number to the column name in Excel, which are named alphabetically.

You could get fancier and use Who(), What() and Where() to make it this process generic, regardless of the table being exported. This approach will be added to a new Export class and template in the near future.

Hint: You can also use SendTo to export browses and lists to Excel, Word, PDF, Email etc. with all their formatting etc. SendTo uses Office Inside to export to Excel and Word, so it is a good example, and actually contains some very useful Office Inside code (SendTo ships are pure Clarion source code).

up icon

 

 


Properties

This section lists the properties of the Excel class and their use. This section is a work in Progress and will be fleshed out in the next few releases.

oiExcel Class Properties
 

New Properties

(also listed below)
  dontCloseOnKill Leaves Excel open when the Kill method is called.
     
     

oiExcel Propeties

  dontCloseOnKill  
  interpretClarionColorEquates  
     
     
     
     
 
     

 

dontCloseOnKill long

Setting dontCloseOnKill to a non zero value will cause the instance of Excel to remain open when the Kill method is called. Any documents that have been created or opened will be left open etc. The state of the Excel window will be set to the default state for a newly opened Excel window (this behaviour is determined by Office). This property is common to all the Office objects, and be used with Word etc.

The property should be set after the call to the Init() method, and before the call to Kill().

Example:

    ! Clean up the object and COM interface, but leave Excel open:
    myExcel.dontCloseOnKill = 1
    myExcel.Kill()

 

interpretClarionColorEquates long

This property is set to 1 by default and will automatically convert Clarion color equates (such as color:red, color:black and so on) to Excel palette colors. In order to use RGB colors and Excel palette colors (swatches) you should set this property to zero. See the GetPaletteColor method for more information on colors and Excel. The Color Equates section shows the default Excel colors and a set of equates that can be used for them.

Unlike MS Word, Excel stores colors in a palette - you can only use the colors contained in that palette.  If the default palette for a given worksheet does not contain the colors you want to use, you can change them using the SetPaletteColor method.

Important: By default, Office Inside supports using standard Clarion color equates, so long as you're using the default Excel palette, so you would simply pass COLOR:YELLOW to an Office Inside method, and it would then use the closest match to that color from the default palette.  If you would rather use the palette directly (which means that passing standard Clarion color equates would no longer work), so can do this by simply setting the InterpretClarionColorEquates property to false (after calling oiExcel.Init, which will set it to true). If you do that, then any color parameter that you pass in needs to be the color swatch number, as explained in the next point.

Note: The color swatches are the "blocks" of color you see when you click the "Font Color" droplist on the Excel toolbar.  You will see there are 8 columns and 5 rows or swatches (totalling 40 swatches).  We have labelled these swatches as 1 being column 1 row 1, 2 being column 2 row 1, 8 being column 8 row 1, 9 being column 1 row 2, and so on.

Example 1

curColor         long
  code
    ! Let the user pick a color (RGB)
    if ColorDialog('Select a color', curColor)
    
  MyExcel.SetPaletteColor(33, curColor) ! Set swatch 33 to this RGB color
  MyExcel.InterpretClarionColorEquates = false   MyExcel.SetCellBackgroundColor(33, 'B2') end

Example 2

      TempLong = 0FFFF00h                       ! Use an RGB color (yellow)
MyExcel.SetPaletteColor(33, curColor) MyExcel.InterpretClarionColorEquates = false ! Set cell B2 to the color in swatch 33, which we just set MyExcel.SetCellBackgroundColor (33, 'B2')

See also

SetPaletteColor, GetPaletteColor, SetCellBackgroundColor, Excel Color Equates.

 

 

bullet smallThe oiExcel Class - Equates

Toolbar Equates     ( used in the ShowToolbar and HideToolbar methods )

oix:ToolbarWorksheetMenuBar
oix:ToolbarChartMenuBar
oix:ToolbarStandard
oix:ToolbarFormatting
oix:ToolbarPivotTable
oix:ToolbarChart
oix:ToolbarReviewing
oix:ToolbarForms
oix:ToolbarStopRecording
oix:ToolbarExternalData
oix:ToolbarAuditing
oix:ToolbarFullScreen
oix:ToolbarCircularReference
oix:ToolbarVisualBasic
oix:ToolbarWeb
oix:ToolbarControlToolbox
oix:ToolbarExitDesignMode
oix:ToolbarRefresh
oix:ToolbarDrawing
oix:ToolbarQueryAndPivot
oix:ToolbarPivotChartMenu
oix:ToolbarWorkbookTabs
oix:ToolbarCell
oix:ToolbarColumn
oix:ToolbarRow
oix:ToolbarCell2
oix:ToolbarColumn2
oix:ToolbarRow2
oix:ToolbarPly
oix:ToolbarXLMCell
oix:ToolbarDocument
oix:ToolbarDesktop
oix:ToolbarNondefaultDragAndDrop
oix:ToolbarAutoFill
oix:ToolbarButton
oix:ToolbarDialog
oix:ToolbarSeries
oix:ToolbarPlotArea
oix:ToolbarFloorAndWalls
oix:ToolbarTrendline
oix:ToolbarChart2
oix:ToolbarFormatDataSeries
oix:ToolbarFormatAxis
oix:ToolbarFormatLegendEntry
oix:ToolbarFormulaBar
oix:ToolbarPivotTableContextMenu
oix:ToolbarQuery
oix:ToolbarQueryLayout
oix:ToolbarAutoCalculate
oix:ToolbarObjectPlot
oix:ToolbarTitleBarCharting
oix:ToolbarLayout
oix:ToolbarPivotChartPopup
oix:ToolbarPhoneticInformation
oix:ToolbarWordArt
oix:ToolbarPicture
oix:ToolbarShadowSettings
oix:Toolbar3DSettings
oix:ToolbarBorders
oix:ToolbarChartType
oix:ToolbarPattern
oix:ToolbarFontColor
oix:ToolbarFillColor
oix:ToolbarLineColor
oix:ToolbarOrder
oix:ToolbarNudge
oix:ToolbarAlignOrDistribute
oix:ToolbarRotateOrFlip
oix:ToolbarLines
oix:ToolbarConnectors
oix:ToolbarAutoShapes
oix:ToolbarCallouts
oix:ToolbarFlowchart
oix:ToolbarBlockArrows
oix:ToolbarStarsAndBanners
oix:ToolbarBasicShapes
oix:ToolbarShapes
oix:ToolbarInactiveChart
oix:ToolbarExcelControl
oix:ToolbarCurve
oix:ToolbarCurveNode
oix:ToolbarCurveSegment
oix:ToolbarPicturesContextMenu
oix:ToolbarOLEObject
oix:ToolbarActiveXControl
oix:ToolbarWordArtContextMenu
oix:ToolbarRotateMode
oix:ToolbarConnector
oix:ToolbarScriptAnchorPopup
oix:ToolbarAddCommand
oix:ToolbarBuiltinMenus
oix:ToolbarSystem
Color Equates     ( used in the SetCellBackgroundColor methods )

Below is a picture of the 56 default colors.  Office Inside has 56 equates representing these colors, which are named as follows:

oix:Color1, oix:Color2, oix:Color3, ..., oix:Color55, oix:Color56

We also have synonyms for the first 8 colors (equates), as follows:

oix:ColorBlack   ( same as oix:Color1 )
oix:ColorWhite   ( same as oix:Color2 )
oix:ColorRed   ( same as oix:Color3 )
oix:ColorGreen   ( same as oix:Color4 )
oix:ColorBlue   ( same as oix:Color5 )
oix:ColorYellow   ( same as oix:Color6 )
oix:ColorMagenta   ( same as oix:Color7 )
oix:ColorCyan   ( same as oix:Color8 )

 

Cell Border Equates     ( used in the SetCellBorders method )

oix:BorderInsideHorizontal
oix:BorderInsideVertical
oix:BorderDiagonalDown
oix:BorderDiagonalUp
oix:BorderEdgeBottom
oix:BorderEdgeLeft
oix:BorderEdgeRight
oix:BorderEdgeTop

 

Line Styles     ( used in the SetCellBorders method )

oix:LineStyleContinuous
oix:LineStyleDash
oix:LineStyleDashDot
oix:LineStyleDashDotDot
oix:LineStyleDot
oix:LineStyleDouble
oix:LineStyleSlantDashDot
oix:LineStyleNone

 

Border Weights     ( used in the SetCellBorders method )

oix:BorderWeightHairline
oix:BorderWeightMedium
oix:BorderWeightThick
oix:BorderWeightThin

 

Underline Styles     ( used in the SetCellFontUnderline method )

oix:UnderlineStyleDouble
oix:UnderlineStyleDoubleAccounting
oix:UnderlineStyleNone
oix:UnderlineStyleSingle
oix:UnderlineStyleSingleAccounting

 

GetInfo / Update Equates     ( used in the GetInfo and Update methods )

 

Equate:

GetInfo?

Update?

Description (To Do)

oix:Left

oix:Top
oix:Width

oix:WindowState

oix:ApplicationVisible

 

horizontal rule

Dll Functions

At this time we have documented all functions for all the Office components in the "main" OfficeInside.htm document.  Click here to go to that section.

horizontal rule

bulletUseful References

bullet smallTips & FAQs

This section contains Tips and Frequently Asked Questions pertaining only to the MS Excel parts of Office Inside.  For Tips and FAQ's pertaining to the product as a whole, or to other components, please click here

  1. When using editable reports, I get the compile errors "Syntax error: Field Not Found: ADDITEM" and "Syntax error: Unknown procedure label".
  bullet small If you are using Legacy and would like to use the Editable Report Word and Excel templates you need to ticked on "Enable the use of ABC classes" checkbox under Global Settings on the Classes tab.
 
  2. What do I need to use the Word and Excel editable report templates?
  bullet small
  1. Clarion 6 (both PE and EE are supported)
  2. If you are using Legacy and would like to use the Editable Report Word and Excel templates you need to ticked on "Enable the use of ABC classes" checkbox under Global Settings on the Classes tab.

Clarion 5.5 is not supported for the editable report template, as it does not ship with the Report Generator interface.

 


horizontal rule

How Tos and Code Examples

This section provides code for common task using Office inside.

 

horizontal ruleColor Management in Excel and Cell Formatting (required reading reading)

One of the most frequent problems that people have when formatting cells in Excel is setting the cell colour, only to have it end up black, rather than the color selected. This is because te deafult behaviour is to translate Clarion color equates to Excel colors.The first property that you have to make sure is set correctly when using color in Excel is oiExcel.interpretClarionColorEquates. This property defaults to 1 (enabled) and allows you to pass Clarion color equates to the various methods. To use the Excel equates or RGB colors you must set this property to 0. Also note that you do not have to use the Excel default palette, you can create your own palette, which avoids the rather hideous Excel default colors.

Not setting interpretClarionColorEquates is the most frequent mistake when using colors to format excel spreadsheets. Always remembet to set oiExcel.interpretClarionColorEquates! It is recommended that you store your own palette and use the functions provided below to manage the colors in the palette. This creates a "palette", which is an array of longs and is used to store the colors used, and the functions to get/set the colors ensure that the Excel palette remains sychronised. In versions of Excel prior to Excel 2007 this was limited to 40 colors (which is handled in the docs below).

The oiExcel object in this example is named ExcelDoc.

numColors         long           ! number of colors used in the custom excel palette
colorPalette      long, dim(40)  ! colors in the excel palette
curColor          long           ! the current color in the excel palette
palettePos        long           ! position of the current color in the palette

cellFontColor     long           ! The color to set the cell to
curCell           string(5)      ! The current cell
columnNumber      long           ! The current column number
rowNumber         long           ! The current row number
  code

    ExcelDoc.intepretClarionColorEquates = 0  ! Important: Treat passed colors as RGB
    numColors = 1                             ! First color in the Excel palette is always black
    palettePos = 1                            ! The current position in our palette
    colorPalette[1] = color:black             ! An array of colors (256)
    curColor = color:black                    ! the current color
    ! Each time I need to use a specific color I check whether
    ! it needs to be added to the palette:    
    if not ExcelDoc.GetPaletteColor(cellFontColor)
         ExcelDoc.SetPaletteColor(cellFontColor)
    end
    ExcelDoc.SetCellFontColor(palettePos, currentCell)   

Methods and Properties for color managment:

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

 

The Palette Management functions

The oiExcel Class provides two palette management methods. These allow a color to be retrieve from the palette by calling oiExcel.SetPaletteColor(). In addition GetPaletteColor() can be used to check if the color exists or not. The oiExcel.SetPaletteColor() method adds a color to the palette and keeps the objects's palettte synchronised with Excel's. Note that the exception is color:black, which is always the first entry in the palette and can always be used.

! Adds a color to the local and Excel paletteAddPaletteColor procedure(long pColor)
  code
    if NumColors > 39 and ExcelDoc.GetOfficeVersion < 12   ! Limit colors for old versions
        ! can do error handling here, or choose which color entry to replace etc.
        return
    end
    numColors += 1
    colorPalette[numColors] = pColor
    curColor = pColor
    palettePos = numColors

    ! add the color to the excel palette
    ExcelDoc.SetPaletteColor(numColors, pColor)


! This locates a passed color in the palette and returns the position 
GetPaletteColor procedure(long pColor)
i              long
  code
    loop i = 1 to 40
        if colorPalette[i] = pColor
            curColor = colorPalette[i]
            palettePos = i
            return i
        end
    end

    return 0

 

Writing to and formatting cells

! Store the cell name (Note that the new method is called ColumnName()
! instead of GetColumnAddressFromColumnNumber())
currentCell = ExcelDoc.ColumnName(columnNumber) & rowNumber

! Add the data:  
ExcelDoc.WriteToCell(cellData), currentCell) 

! Format the cell:
ExcelDoc.SetCellFontName(cellFontName, currentCell)
ExcelDoc.SetCellFontSize(cellFontSize, currentCell)

if Band(cellFontStyle, font:weight) >= font:bold
    ExcelDoc.SetCellFontStyle('Bold', currentCell)
end
if Band(cellFontStyle, font:underline)
    ExcelDoc.SetCellFontUnderline(oix:UnderlineStyleSingle, currentCell)
end
if cellFontColor > 0
    if not ExcelDoc.GetPaletteColor(cellFontColor)
        ExcelDoc.SetPaletteColor(cellFontColor)
    end
    ExcelDoc.SetCellFontColor(palettePos, currentCell)
end

if backgroundColor <> color:none
    if not ExcelDoc.GetPaletteColor(backgroundColor)
        ExcelDoc.SetPaletteColor(backgroundColor)
    end
    ExcelDoc.SetCellBackgroundColor(palettePos , currentCell)
end

 

Conditionally Deleting Rows from a Workbook

This code example loads a workbook, processes each row, and conditionally deletes the row. The workbook is saved on completion.

excel           oiExcel
row             long
tot             long
  code
    excel.OpenWorkbook(myWorkbookFile)          ! Open the workbook

    tot = excel.CountUseRows()                  ! Get the number of used rows

    loop row = 1 to tot
        idNumber = excel.ReadCell('A' & row)    ! Get the ID from a particular cell
        if idNumber = someValue
           excel.SelectRows (row)
           excel.DeleteSelection(oix:up)
        end
    end

    excel.Save()



horizontal rule

bullet small Exporting a Table to an Excel spreadsheet.

By far the simplest way to export a table to an Excel workbook is to use the oiExcelImpEx class and simply call the Save method:

ieExcel     oiExcelImpEx
  code 
    ieExcel.Save(MyFile, 'MyFile')   ! Export to MyFile.xls or .xlsx
	
	

The code below demonstrates a very simple manual export looping through a table to write two fields in a file to a workbook.

See the WriteToCell documentation, which also includes the below example.

This Example loops through a table and writes each row in the table to a row in Excel. The example only writes the first two fields, but each additional field is done in the same manner.

currentRow         long
  code
    MyExcel.NewWorkbook()
    currentRow = 1

    Set(MyFile)
    loop until Access:MyFile.Next()
        MyExcel.WriteToCell(MyFile.Field1, MyExcel.ColumnName(1) & currentRow)
        MyExcel.WriteToCell(MyFile.Field2, MyExcel.ColumnName(2) & currentRow)
        currentRow += 1
    end
    MyExcel.SaveAs(fileName)

 

The ColumnName() method called above converts a number to the column name in Excel, which are named alphabetically.

Tip: You can also use SendTo to export browses and lists to Excel, Word, PDF, Email etc. with all their formatting etc. SendTo uses Office Inside to export to Excel and Word, so it is a good example, and actually contains some very useful Office Inside code (SendTo ships are pure Clarion source code).

horizontal rule

bullet smallLimitations in Excel, Workarounds and Version Differences.

For versions of Office prior to 2007 there is a limit of 256 columns and 64K rows. For Office 2007 the limit is 1,048,576 rows by 16,384 columns. A full list of limit changes is below.
 
The rows and columns limit is not per worksheet, but rather a per workbook limit. In versions prior to Excel 2007, in order to support more than 64K rows split the data into multiple workbooks rather than create new sheets, which is clunky, but workable.
 
Of course in Excel 2007 this limit isn't much of an issue, as it supports 1 million rows by 16K columns. Excel 2007 also contains optimisations to speed up calculations when using new dual and multi core CPUs.

The total number of available columns in Excel
Old Limit: 256  (2^8)
New Limit: 16k  (2^14)

The total number of available rows in Excel
Old Limit: 64k  (2^16)
New Limit: 1M  (2^20)

Total amount of PC memory that Excel can use
Old Limit: 1GB
New Limit: Maximum allowed by Windows

Number of unique colours allowed a single workbook
Old Limit: 56 (indexed colour)
New Limit: 4.3 billion (32-bit colour)

Number of conditional format conditions on a cell
Old Limit: 3 conditions
New Limit: Limited by available memory

Number of levels of sorting on a range or table
Old Limit: 3
New Limit: 64

Number of items shown in the Auto-Filter dropdown
Old Limit: 1,000
New Limit: 10,000

The total number of characters that can display in a cell
Old Limit: 1k (when the text is formatted)
New Limit: 32k or as many as will fit in the cell (regardless of formatting)

The number of characters per cell that Excel can print
Old Limit: 1k
New Limit: 32k

The total number of unique cell styles in a workbook (combinations of all cell formatting)
Old Limit: 4000
New Limit: 64k

The maximum length of formulas (in characters)
Old Limit: 1k characters
New Limit: 8k characters

The number of levels of nesting that Excel allows in formulas
Old Limit: 7
New Limit: 64

Maximum number of arguments to a function
Old Limit: 30
New Limit: 255

Maximum number of items found by “Find All”
Old Limit: ~64k (65472)
New Limit: ~2 Billion

Number of rows allowed in a Pivot Table
Old Limit: 64k
New Limit: 1M

Number of columns allowed in a Pivot Table
Old Limit: 255
New Limit: 16k

Maximum number of unique items within a single Pivot Field
Old Limit: 32k
New Limit: 1M

Length of the MDX name for a Pivot Table item; also the string length for a relational Pivot Table
Old Limit: 255 characters
New Limit: 32k

The length at which fields’ labels are truncated when added to PivotTable; this also includes caption length limitations
Old Limit: 255
New Limit: 32k

The number of fields (as seen in the field list) that a single PivotTable can have
Old Limit: 255
New Limit: 16k

The number of cells that may depend on a single area before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
Old Limit: 8k
New Limit: Limited by available memory

The number of different areas in a sheet that may have dependencies before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
Old Limit: 64k
New Limit: Limited by available memory

The number of array formulas in a worksheet that can refer to another (given) worksheet
Old Limit: 65k
New Limit: Limited by available memory

The number of categories that custom functions can be bucketed into
Old Limit: 32
New Limit: 255

The number of characters that may be updated in a non-resident external workbook reference
Old Limit: 255
New Limit: 32k

Number of rows of a column or columns that can be referred to in an array formula
Old Limit: 65,335
New Limit: Limitation removed (full-column references allowed)

The number of characters that can be stored and displayed in a cell formatted as Text
Old Limit: 255
New Limit: 32k








CapeSoft Software copyright