CapeSoft Office Inside Excel

Data Import/Export HowTos and Examples

NOTE: Fields = elements in your file/Queue/View. Columns = columns in the Excel spreadsheet.
1. Save to a File, Queue, View
2. Load a Queue or File
3. How to change values during import or export (or override the default add/put methods)
4. How to append to an existing Excel spreadsheet (including overwriting existing data if necessary)
5. How to ignore certain fields
6. How to map columns to fields
7. How to use the abc filemanager to insert (and handle duplicate entries)
8. How to start at a particular row, and only export/import a certain number of rows
9. How to add additional information (like a total) at the end of the export.

Controlling Excel from your application.

Data Import/Export HowTos and Examples

This section will show you how to save a File, Queue, View to Excel. Or Load a Queue or File from an Excel spreadsheet. See below.

Also see:

How to change values during import or export (or override the default add/put methods)
How to append to an existing Excel spreadsheet (including overwriting existing data if necessary)
How to ignore certain fields
How to map columns to fields
The oiExcelImpEx Class
Implementing Translation in the Excel Import Wizard

See the ImportExport example for more information and the code in context.

Save a File to an Excel Workbook.

Save all fields in the File to the spreadsheet, in the order that they occur in the File. All records are saved.

if not MyExcel.Save(Customers, excelFileName)
Message('Cannot Save the data. ' & CLip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
end

Save a Browse Queue to an Excel Workbook

Save all fields in a Queue to the spreadsheet, in the order that they occur. All records are saved.

if not MyExcel.Save(BrowseCustomersQ, excelFileName)
Message('Cannot Save the data. ' & CLip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
end

Save a View to an Excel workbook

Save all fields in the view to the Excel workbook in the order that they occur.

if not MyExcel.Save(BRW1::View:Browse, excelFileName)
Message('Cannot Save the data. ' & CLip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
end

Load from an Excel SpreadSheet into a Queue

This example loads from an Excel spreadsheet directly into the queue. Each column is placed into the queue field in the order in which they occur.

MyExcel.freeBeforeLoad = True ! Clears the structure of all data/records before loading
if not MyExcel.Load(ExpensesQ, LongPath() & '\expenses.xls')
Message('Cannot import the data, loading failed. ' & CLip(MyExcel.errorMessage), 'Import Error', Icon:Exclamation)
end

Load from an Excel Spreadsheet into a File (Table)

This example loads the contents of an Excel spreadsheet into a File. See the Code in the section below on how to override the TakeRecord and InsertRecord methods to provide auto-numbering and to use the FileManager Insert() method to insert the records.

      if not MyExcel.Load(Customers, excelFileName)
          Message('Cannot import the data, loading failed. ' & CLip(MyExcel.errorMessage), 'Import Error', Icon:Exclamation)
      end
      BrowseCustomers.ResetFromFile()

How to change values during import or export (by overriding the TakeRecord and InsertRecord methods)

The oiExcelImpEx class provides a number of callback methods to allow the behavior of the class to be customized.

The TakeRecord method is provided to allow any additional code to be added that should be called each time a new record is processed (like changing a field's text before it gets written to Excel).

The InsertRecord method is provided to allow the default record insertion to be overridden (for example to call the FileManager Insert method rather than using Add - or to change a field's text before it is written to the file).

The example below customizes the behavior when loading files (handle auto numbering and call the FileManager method to insert the record).

  ! When loading from a File, populate the autonumber ID field
MyExcel.TakeRecord Procedure (*excelImportQType cellsQ)
  code
    if self.dataType = oi:file and self.action <> oix:Save
        Access:Customers.PrimeRecord(true)      ! Prime the record, do not clear fields
    end

    return parent.TakeRecord(cellsQ)            ! Call the parent to continue processing

! When loading from a File, call the FileManager Insert method and return.
! For all other cases, call the parent to before the default action.
MyExcel.InsertRecord Procedure()
  code
    if self.dataType = oi:file and self.action <> oix:Save
        return Access:Customers.Insert()
    else    ! Use the default behaviour when saving
        parent.InsertRecord()
    end

Export data to an existing Excel file (appending to or overwriting part of a worksheet)

In addition to exporting to a new file the Save method can be used to export to an Excel workbook which already exists. The new records can either be appended after the rows which already contain data, or they can be written from a specific row (overwriting any data which those rows might already contain).

Appending to an existing sheet:

     MyExcel.appendToFile = true
    if not MyExcel.Save(Customers, 'Customers') ! Note: If not file extension is specified, Excel will add one.
        Message('Cannot Save the data. ' & CLip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
    end

To start at a particular row, add the following code:

In MyExcel.TakeSetup():

MyExcel.TakeSetup Procedure() code self.startRow = 3 ! Set the desired start row.

How to ignore certain fields

Note: You can also map fields to different columns. You should not use both mapping of columns and ignoring of fields. You need to adopt one approach to limiting which data columns you would like to import/export.

The advantage of this approach is that it is very simple to implement. Essentially you call the IgnoreField method for each file/queue/group field that you want to ignore from the import/export.

Before calling the save (or load) command, you need to tell the class which columns to ignore:

MyExcel.numFields = 7         !If you're only importing from the first 7 fields, then indicate this using the NumFields property.
    MyExcel.IgnoreField(2)      !Ignore the second field of the file/queue/group.
    MyExcel.IgnoreField(4)
    MyExcel.IgnoreField(6)
    if not MyExcel.Save(BrowseCustomersQ, excelFileName)
        Message('Cannot Save the data. ' & CLip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
    end

How to map columns to fields

Note: You can also Ignore certain fields to limit column export (or import). You should not use both mapping of columns and ignoring of fields. You need to adopt one approach to limiting which data columns you would like to import/export.

The map fields method uses file/queue/group field to excel column mapping to determine which fields and columns are mapped to one another. This approach provides the following advantages:

Any column can be mapped to any field in any order
As few or as many columns and fields can be mapped as needed
Column and field mapping can be done at runtime

    MyExcel.MapFieldToColumn(5,1)       !Map table/queue/group field number 5 to excel spreadsheet column 1.
    MyExcel.MapFieldToColumn(2,2)
    MyExcel.MapFieldToColumn(3,3)
    MyExcel.MapFieldToColumn(4,3)       !This will override the previous line, so that field 4 will be mapped to col 3.
    MyExcel.MapFieldToColumn(5,5)       !This will clear the first mapping, in that Field 5 will now be mapped to Col 5.
	
    if not MyExcel.Save(BrowseCustomersQ, excelFileName)
        Message('Cannot Save the data. ' & Clip(MyExcel.errorMessage), 'Export Error', Icon:Exclamation)
    end

How to use the abc filemanager to insert (and handle duplicate entries)

The import template utility uses the load method to import data from excel into your database. The load method uses the generic add() command to perform the record addition. Often, though, it's preferable to use the FileManager to implement the add, which handles error correction, auto-inc, etc. You need to derive the InsertRecord method to put the call to the ABC manager there.

If you are using the Import Wizard (out the box) then you can simply use the filemanager handle declared in the local class:

If you are handcoding the import using the load method, then you need to know which FileManager you are using. Most likely the import is programmed for a single file, in which case you can use the file's Filemanager:

Access:MyFile.tryinsert()
return errorcode()

If your user has an option of files, then you need to determine which file is being inserted into, and use the corresponding filemanager to perform the insert:

if self.f &= MyFile
Access:MyFile.tryinsert()
return errorcode()
elsif self.f &= AnotherFile
Access:MyFile.tryinsert()
return errorcode()
end

To handle duplicates (and perform an update rather than an insert if a duplicate occurs):

if self.f &= MyFile
if Access:MyFile.tryinsert()
    if access:MyFile.tryfetch(UseAppropriateKey)
      self.PrimeRecord()    !Prime the fields that need to be changed.
      access:MyFile.tryupdate()
    end
end
return errorcode()
end

How to start at a particular row, and only export/import a certain number of rows

Before calling the load/save method, you can set at which row you want the Import/Export to occur from/to in the Excel spreadsheet. You do this using the StartAtRow property. You can also limit the number of rows to import/export from/to the Excel spreadsheet using the numRows property:

MyExcel.StartAtRow = 3 !Start Importing/Exporting from the excel spreadsheet in row 3.
MyExcel.numRows = 48 !Import/Export 48 rows from/to the excel spreadsheet.

How to add additional information (like a total) at the end of the export

The Save method is designed to be completely containerized, so it initializes, opens, does the export, closes and kills the Excel com object to be self-contained in one method. If you are wanting to perform additional functions, the full range of oiExcel functionality is available to you, which you can use in the derived TakeComplete method.

MyExcel.Takeomplete procedure
code
parent.TakeComplete()
!Write your code in hear using the various oiExcel methods and properties.

Implementing Translation in the Excel Import Wizard

All items that are translatable from within the class, are called with the translate method. The translate method simply receives a line of text and returns that same line of text. This means that you are able to derive the translate method and insert your code to implement translation on that text, and return the translated text string.

Examples of strings that are translatable:

'Select an Excel file to import', 'Excel Files|*.xls*;*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xlt|Web Pages|*.htm;*.mht;*.html;*.mhtml|XML Files|*.xml|Text Files|*.csv;*.txt;*.prn|dBase Files|*.dbf|Data Interchange Format|*.dif|All Files|*.*', 'Remove map to ', ' selected for the Table', column headings, and other error messages.

MyExcel.translate procedure (string pText)
code
    case pText
    of 'PRO:ProductName'   !The header name of the ProductName
      return 'Product'
    end

How Tos and Code Examples / Controlling Excel from your application

This section provides code for common task using Office inside.

Setting the Cell color using the Excel Palette
How to manage the Palette
How to write to and format cells
Conditionally Deleting Rows from a Workbook

You can also check out the Classes section section for details on other methods.

Setting the Cell color using the Excel Palette (required 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 the default 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 isI. 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 remember 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

How to manage the Palette

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 palette 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

How to write to and format 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)

!Set the picture:
ExcelDoc.SetCellTextFormat('@n03')             !You can also use the Excel format pictures directly if you know the excel picture to use 					  !using SetCellNumberFormat

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()

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.

The oiExcel Class - Methods Listed by Usage

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)
CountUsedColumns	Returns the number of columns 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	This method returns the currently selected active printer.
SetActivePrinter	This method sets the currently selected active printer.
Editing Methods
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
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.
AddImage	Add an image at the currently selected cell.
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	Returns the colour at the passed palette position.
SetPaletteColor	Sets a colour in the Excel palette
RunFile	Opens the Excel file in the parameter
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.
GetObjects	Called to initialize the various oiObjects in the Excel class.
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
WriteFormulaToCell	Writes a formula to a 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.
SetCellTextFormat	Sets the format (picture) used to display the contents of the selected cells using a clarion picture.
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.

AutoFilter

AutoFilter (<string pFromColumn>, <string pToColumn> ) ,byte,proc

Description

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.

If pFromColumn is omitted, then the first column is assumed.
if pToColumn is omitted, then the last column (with data) is assumed.

Example

MyExcel.AutoFilter ('A', 'E')

Calculate

Calculate ()

Description

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

Example
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)

Example

    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)

ClearPrintArea

ClearPrintArea ( ) ,byte,proc

Description

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

Example

MyExcel.ClearPrintArea ()

CloseWorkBook

CloseWorkbook ( byte pSaveChanges=1 ) ,byte,proc

Description

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.

Example

MyExcel.CloseWorkbook() ! user is prompted to save

Copy

Copy ( <string pFromCell>, <string pToCell> ) ,byte,proc

Description

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).

Example

Example
MyExcel.Copy () ! copies current selection MyExcel.Copy ('A1') ! copies cell A1 MyExcel.Copy ('A1', 'B3') ! copy range A1:B3

Find

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)

Description

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

Parameters

Parameter	Description
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

ColumnName (long columnNumber)

Description

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

Parameter	Description
long	columnNumber:

Return Value

Returns a string that contains the name of the column.

Example

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

Example
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)

Example

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)

CountOpenWorkBooks

CountOpenWorkbooks( ) ,long,proc

Description

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.

Example

TempLong = MyExcel.CountOpenWorkbooks()

CountUsedRows

CountUsedRows ( ) ,long,proc

Description

Returns the bottom-most row number of all the used cells in the currently open worksheet, or a negative number if an error occurred.

Example

TempLong = MyExcel.CountUsedRows()

CountUsedColumns

CountUsedColumns ( ) ,long,proc

Description

Returns the right-most column number of all the used cells in the currently open worksheet, or a negative number if an error occurred.

Example

TempLong = MyExcel.CountUsedColumns()

CountWorksheets

CountWorksheets ( ) ,long,proc

Description

Returns the number of currently open worksheets, or a negative number if an error occurred.

Example

TempLong = MyExcel.CountSheets()

DeleteSelection

DeleteSelection ( long pShift ) ,byte,proc

Description

Deletes the currently selected cells. pShift can be one of the following equates:
oix:Down
oix:ToLeft
oix:ToRight
oix:Up

Example

MyExcel.DeleteSelection (oix:Up)

DeleteWorksheet

DeleteWorksheet ( ) ,byte,proc

Description

Deletes the currently selected WorkSheet.

Example

MyExcel.DeleteWorksheet()

ErrorTrap

ErrorTrap (string pErrorString, string pFunctionName)

Description

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

Parameter	Description
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

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

Return Values

None

Notes

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.

ExcelImport

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

Description

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.

Parameter	Description
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.

Examples
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

Examples

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

GetActivePrinter

GetActivePrinter () ,string

Description

Gets the currently selected output printer.

Example

Example
CurrentPrinter = MyExcel.GetActivePrinter ()

ImportSetup

ImportSetup()

Description

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

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

GetObjects

GetObjects()

Description

This method is to set the internal oiObject handles to point to their respective COM counterparts. Currently, the handles that are set are: ActiveWorkbook, ActiveWorkSheet, WorkSheets, Workbooks, Names and Cells. These are all child classes (or sub classes) of the Application class.

Parameters

None

Return Values

None

Examples

Examples
ThisExcel.GetObjects()

ImportComplete

ImportComplete()

Description

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

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

FreezePanes ( byte pFreeze=true ) ,byte

Description

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.

Example

Example
MyExcel.FreezePanes () MyExcel.FreezePanes (false)

GetCellBackgroundColor

GetCellBackgroundColor (string fromCell, string toCell) , long

Description

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

Parameters

Parameter	Description
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.

Example

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

GetColumnAddressFromColumnNumber

GetColumnAddressFromColumnNumber (long pColumnNumber)

Description

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().

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.

Example

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

GetColumnWidth

GetColumnWidth ( string pColumn ) ,string

Description

Returns the width of the column that you pass as pColumn, or a negative number if an error occurred.

Example

loc:ColumnWidth = MyExcel.GetColumnWidth('B')

GetInfo

GetInfo ( byte pOption ) ,string,proc

Description

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.

Example

TempString = MyExcel.GetInfo (oix:WindowState)

GetFileName

GetFileName (), string

Description

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

Parameters

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'.

GetPaletteColor

GetPaletteColor (byte pSwatch), long

Description

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

Parameter	DDescription
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.InterpretClarionColorEquatesis 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.

Example

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 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 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')

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')

GetRowHeight

GetRowHeight ( string ) ,string

Description

Returns the height of a row (see example above)

Example

X = MyExcel.GetRowHeight (5) ! get height for row 5

HideFormulaBar

HideFormulaBar ( ) ,byte,proc

Description

Makes the Excel Formula Bar not visible.
Returns true (1) if no problems were experienced

Example

MyExcel.HideFormulaBar()

HideStatusBar

HideStatusBar ( ) ,byte,proc

Description

Makes the Excel Status Bar not visible.
Returns true (1) if no problems were experienced

Example

MyExcel.HideStatusBar()

HideToolbar

HideToolbar (long pToolbar) ,byte,proc

Description

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.

Example

MyExcel.HideToolbar(oix:ToolbarStandard)

ImportXLSFile

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

Description

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)

Example

Examples Typically you would code as follows:

Example
! 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)

Example

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

Init

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

Description

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.

Example

Example
! Initialise with the standard parameter values (Excel visible with no event handling) MyExcel.Init() ! Initialise with Excel hidden and event handling on:

InsertColumn

InsertColumn ( <string pCellAddress>, byte pShiftOption=0 ) ,byte,proc

Description

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.

Example

MyExcel.InsertColumn ()
MyExcel.InsertColumn ('C3')

InsertRow

InsertRow ( <string pCellAddress>, byte pShiftOption=0 ) ,byte,proc

Description

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.

Example

MyExcel.InsertRow ()
MyExcel.InsertRow ('C3')

InsertWorksheet

InsertWorksheet ( string pBefore, string pAfter, long pCount=0, long pType=0) ,byte,proc

Description

Inserts a new worksheet into the currently loaded workbook.
Returns true (1) if no problems were experienced
pBefore : the number of the sheet to add before
pAfter: Not implemented yet
pCount: Number of sheets to add
pType: Not Used

Example

MyExcel.InsertWorksheet()

Kill

Kill ( byte pUnloadCOM=1 ) ,byte,proc

Description

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.

Example

MyExcel.Kill()

MergeCells

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

Description

Merges the range of cells identified by the pFromCell andpToCell parameters.

Example

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

NewWorkbook

NewWorkbook ( ) ,byte,proc

Description

Creates a new Workbook. To create a workbook from a template, use the OpenWorkbook command.
Returns true (1) if no problems were experienced.

Example

MyExcel.NewWorkbook()

OpenWorkbook

OpenWorkbook ( <string pFileName> ) , long, proc

Description

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. You can use the pfileName parameter to open a new workbook based on a template (and then SaveAs to save the workbook).

Parameters

Parameter	Description
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.

Example

Examples
MyExcel.OpenWorkbook() ! Prompts the user for the file name
MyExcel.OpenWorkbook(LongPath() & '\data.xlsx') ! Opens the specified file

Paste

Paste ( ) ,byte,proc

Description

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

Example

MyExcel.Paste()

PrintWorksheetProcedure

PrintWorksheetProcedure (*oixPrintProperties printSettings)

Example

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

Example

! 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

PrintMe

PrintMe ( ) ,byte,proc

Description

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.

Example

MyExcel.PrintMe()

PrintPreview

PrintPreview ( ) ,byte, proc

Description

Returns true (1) if no problems were experienced.
More options to follow in future releases...

Example

MyExcel.PrintPreview()

ProtectWorkbook

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

Parameters: pStructure and pWindows are reserved for later use. Pass 0,0 in the interim.

Description

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").

ProtectWorksheet

ProtectWorksheet( string pPassword, byte pDrawingObjects, byte pContents, byte pScenarios, byte pUserInterfaceOnly ),byte ,proc

Description

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

ReadCell

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

Description

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

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

Redo

Redo( ) ,byte,proc

Description

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

Example

MyExcel.Redo()

RenameWorksheet

RenameWorksheet ( string pName ) ,byte,proc

Description

Renames the currently selected worksheet. You can use the SelectWorksheet method to select a worksheet, and can then call this method to rename it.

Example

MyExcel.RenameWorksheet ('Sales')

ReplaceText

ReplaceText ( string pFindText, string pReplaceText, byte pReplaceAll=false ) ,byte,proc

Description

Searches for the text passed as pFindText, and replaces it with the text passed in pReplaceText

Example

MyExcel.ReplaceText ('Cape', 'Soft', true)

AddImage

AddImage ( string pImageName) ,byte,proc

Description

Adds an image to the currently selected cell. You must first call select so that a cell is selected prior to calling this function

Example

MyExcel.AddImage ('C:\MyImages\MyImage.png') !You must include the path to the image

Save

Save ( ) ,byte,proc

Description

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

Example

MyExcel.Save()

SaveAs

SaveAs (<string pFileName>, <long fileType>,<String pOpenPassword>, <string pEditPassword>)

Description

Saves the current workbook using the name provided in theFileName 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

Parameter	Description
string pFileName (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.
string pOpenPassword (optional)	If this is set then this sets an Open password for the file. The user will need to enter this password in order to open the file.
string pEditPassword (optional)	If this is set then this sets an Edit password for the file. The user will need to enter this password in order to edit the file.

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:xlPDF	57	Save As PDF File
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	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 compatibility 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.

Example

Example
! 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)

Example

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

SelectCells

SelectCells ( string <pFromCell>, <string pToCell> ) ,byte,proc

Description

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

Example

Example
MyExcel.SelectCells ('B3') MyExcel.SelectCells ('B3', 'D12') MyExcel.SelectCells () ! Ctrl-A

SelectColumns

SelectColumns ( string pFromColumn, <string pToColumn> ) ,byte,proc

Description

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.

Example

Example
MyExcel.SelectColumns ('B') MyExcel.SelectColumns ('B', 'D')

SelectRows

SelectRows ( string pFromRow, <string pToRow> ) ,byte,proc

Description

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.

Example

Example
MyExcel.SelectRows (3) MyExcel.SelectRows (3, 8)

SelectWorksheet

SelectWorksheet ( <string pSheetName>, long pSheetNumber=0 ) ,byte,proc

Description

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.

Example

Example
MyExcel.SelectWorksheet ('Sheet2') MyExcel.SelectWorksheet ('My Sheet') MyExcel.SelectWorksheet ('', 2)

SetCalculations

SetCalculations (long calcType)

Description

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

Parameter	Description
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.

Parameter

Description

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.

Example

Example
ExcelDoc.SetCalculations(oix:CalculationManual) ! 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: EExcelDoc.SetCalculations(oix:CalculationAutomatic)

SetActivePrinter

SetActivePrinter ( string pPrinter ) ,byte,proc

Description

Sets the output printer to the one specified in the pPrinter string. NOTE: This string must be exactly correct (not just the description of the printer). The best way to ensure that this string is correct is to select the printer in Excel and then use the GetActivePrinter to return the correct printer. From there on, you can use that string to select the correct printer.
Returns true (1) if no problems were experienced

Example

Example
MyExcel.SetActivePrinter ('CutePDF Writer on CPW2:')

SetScreenUpdating

SetScreenUpdating (long updateScreen)

Description

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

Parameter	Description
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.

Example

Example
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)

Example

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)

SetCellAlignment

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

Description

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 pparameter 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

Parameter	Description
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.

Example

Example
! 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')
! Turn text wrapping off for a range of cells MyExcel.SetCellAlignment(oix:WrapText, false, 'B2', 'C4')

SetCellBackgroundColor

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

Description

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.

Example

Example
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

SetCellBorders

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

Description

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.

Example

Example
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)

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)

SetCellFontColor

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

Description

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

Parameters

Parameter	Description
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

Example

Example
MyExcel.SetCellFontColor (color:red) ! currently selected cell/range MyExcel.SetCellFontColor (color:red, 'A1') ! cell A1 MyExcel.SetCellFontColor (color:red, 'A1', 'C3') ! range A1:C3

SetCellFontName

SetCellFontName ( string pFontName, <string pFromCell>, <string pToCell> ) ,byte,proc

Description

Sets the Font name for a cell / range of cells
Returns true (1) if no problems were experienced.

Example

Example
MyExcel.SetCellFontName ('Verdana') ! currently selected cell MyExcel.SetCellFontName ('Arial', 'A1') ! cell A1 MyExcel.SetCellFontName ('Verdana', 'A1', 'C3') ! cell range A1:C3

SetCellFontSize

SetCellFontSize ( long pFontSize, <string pFromCell>, <string pToCell> ) ,byte,proc

Description

Sets the Font Size for a cell / a range of cells
Returns true (1) if no problems were experienced

Example

Example
MyExcel.SetCellFontSize (16) ! currently selected cell / range MyExcel.SetCellFontSize (20, 'A1') ! cell A1 MyExcel.SetCellFontSize (16, 'A1', 'C3') ! range A1:C3

SetCellFontStyle

SetCellFontStyle ( string pFontStyle, <string pFromCell>, <string pToCell> ) ,byte,proc

Description

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.

Example

Example
MyExcel.SetCellFontStyle ('Bold') ! currently selected cell/range MyExcel.SetCellFontStyle ('Bold Italic', 'A1') ! cell A1 MyExcel.SetCellFontStyle ('Bold', 'A1', 'C3') ! range A1:C3

SetCellFontUnderline

SetCellFontUnderline ( long pUnderlineStyle, <string pFromCell>, <string pToCell> ),byte,proc

Description

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.

Example

Example
MyExcel.SetCellFontUnderline (oix:UnderlineStyleDouble) MyExcel.SetCellFontUnderline (oix:UnderlineStyleNone, 'A1') MyExcel.SetCellFontUnderline (oix:UnderlineStyleDouble, 'A1', 'C3')

SetCellTextFormat

SetCellTextFormat (string pPicture, <string pFromCell>, <string pToCell>)

Description

Sets the format of the text for the currently selected cells, or optionally for the cell range specified by the pFromCell and pToCell parameters.

Parameters

Parameter	Description
string pPicture	Must be a clarion picture token (see the Clarion docs for picture tokens): Currently supported (at the time of writing) Time, Date, Numeric and Currency and String pictures. Special Overrides: Currency - uses the predefined Excel Currency picture.
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', '', ...). To format the currently selected cell, omit these parameters

SetCellNumberFormat

(Rather see SetCellTextFormat for formatting excel cells using Clarion picture tokens)

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

Description

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

Parameter	Description
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 yourFormatType 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: 9/7 9/7/03 09/07/03 7-Sep 7-Sep-03 07-Sep-03 Sep-03 September-03 September 7, 2003 S S-03 9/7/2003 7-Sep-2003 If FormatType is Time, then pSubType can be 1 through 6 - examples of these time formats are: 13:30 1:30 PM 13:30:55 1:30:55 PM 30:55.7 37:30:55

Example

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

SetCellProtection( byte pLocked, byte pFormulaHidden, <string pFromCell>, <string pToCell> ) , byte, proc

Description

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.

Example

Example
MyExcel.SetCellProtection (true, false) ! currently selected cell / range MyExcel.SetCellProtection (true, true, 'A1') ! cell A1 MyExcel.SetCellProtection (false, false, 'A1', 'C4') ! range A1:C4

SetColumnWidth

SetColumnWidth ( string FromColumn, <string ToColumn>, <string pWidth> ) ,byte, proc

Description

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.

Example

Example
MyExcel.SetColumnWidth ('A', 'C', '15.71') MyExcel.SetColumnWidth ('A', , '18.45')

SetPageView

SetPageView ( byte pPageView ) ,byte,proc

Description

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.

Example

MyExcel.SetPageView (oix:PageBreakPreview)

SetPaletteColor

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

Description

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).

Parameter

Parameter

Description

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.

Example

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

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')

SetPrintArea

SetPrintArea ( string fromCell, string ToCell ) ,byte,proc

Description

Sets the current print area (same as "File" --> "Print Area" --> "Set Print Area" in Excel).
Returns true (1) if no problems were experienced.

Example

MyExcel.SetPrintArea('A3', 'B5')

SetRowHeight

SetRowHeight ( string pFromRow, <string pToRow>, <string pHeight> ) , byte, proc

Description

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

Example

Example
MyExcel.SetRowHeight(5,8,'20.75') MyExcel.SetRowHeight(9,,'20.75')

ShowFormulaBar

ShowFormulaBar ( ) , byte, proc

Description

Makes the Excel Formula Bar visible.
Returns true (1) if no problems were experienced

Example

MyExcel.ShowFormulaBar()

ShowStatusBar

ShowStatusBar ( ) , byte, proc

Description

Makes the Excel Status Bar visible.
Returns true (1) if no problems were experienced

Example

MyExcel.ShowStatusBar()

ShowToolbar

ShowToolbar ( long pToolbar ) , byte, proc

Description

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

Example

MyExcel.ShowToolbar(oix:ToolbarStandard)

TakeEvent

TakeEvent ( string pEventString1, string pEventString2, long pEventNumber=0, byte pEventType=0, byte pEventStatus=0 )

Description

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

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.

Parameter

Parameter	Description
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.

Example

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

Undo

Undo ( ) , byte, proc

Description

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

Example

MyExcel.Undo()

UnmergeCells

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

Description

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

Example

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

UnprotectWorkbook

UnprotectWorkbook ( string pPassword ) , byte, proc

Description

See the ProtectWorkbook method.

Example

MyExcel.UnprotectWorkbook('MyPassword')

UnprotectWorksheet

UnprotectWorksheet ( string pPassword ) , byte, proc

Description

See the ProtectWorksheet method.

Example

MyExcel.UnprotectWorksheet('MyPassword')

Update

Update ( byte pOption, <string pValue> ) , byte, proc

Description

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.

Example

TempByte = MyExcel.Update (oix:WindowState, oix:MinimizeWindow)

WriteToCell

WriteToCell ( string pText, <string pCellAddress> ) , byte, proc

Description

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

MyExcel.WriteToCell('Test')
MyExcel.WriteToCell('Test', 'B3')

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, clip(MyExcel.ColumnName(1)) & currentRow)
        MyExcel.WriteToCell(MyFile.Field2, clip(MyExcel.ColumnName(2)) & currentRow)
        MyExcel.WriteToCell(day(LC:LC_Date)&'.'&month(LC:LC_Date)&'.'&year(LC:LC_Date), clip(MyExcel.ColumnName(3)) & currentRow) !This will enter the date in the format that excel understands.
        currentRow += 1
    end
    MyExcel.SaveAs(fileName)

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

To format the text in the cell, use the SetCellTextFormat method.

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).

oiExcel Class Properties
New Properties (also listed below)
dontCloseOnKill	Leaves Excel open when the Kill method is called.
oiExcel Propeties
dontCloseOnKill
interpretClarionColorEquates

Equate:
	GetInfo?	Update?	Description (To Do)
oix:Left
oix:Top
oix:Width
oix:WindowState
oix:ApplicationVisible

oiExcelImpEx Class Properties
startRow	The row number of the excel spreadsheet to start at for the import/export.
StartCol	The Column number from which to start importing/exporting (not applicable when column mapping is used)
ColumnHeadersFromFields	When exporting, if set to one of the equates below, exports the StartRow with the field names in the columns oix:LowerCase - creates the field names in lower case oix:UpperCase - creates the field names in upper case oix:CaseAsIs - creates the field names as is
ProgressControl	The progress control on the clarion window to update periodically
numRows	The number of rows to import/export from/to the excel file.
numCols	The number of columns to import/export from/to the excel file.
commitRecords	The number of records to insert before committing (file driver dependent)
openAfterSave	Open the Excel file after exporting
RowsDone	Set after the import/export. Indicates the number of rows imported/exported
ColsDone	Set after the import/export. Indicates the number of columns imported/exported
KillWhenDone	If you would like to only use the import/export once in this procedure, you can set the KillWhenDone flag. By default this is off so that you can re-do the load/save multiple times.

oiExcelImpEx Class Methods
Save	Exports the data to the excel file.
Load	Imports the data from an excel file into the data structure.
ValidateRecord	Useful if you want to filter out records immediately before importing/exporting that record (during the import process).
IgnoreField	Adds a field to the internal ignored queue, so that the particular data field is omitted from the exporting/import.
ClearIgnored	Clear the internal IgnoreFields queue.
IsIgnored	Check if a field is going to be ignored or not.
MapFieldToColumn	Creates an entry in the ColMap internal queue to map a field to a particular column.
PrimeRecord	This method primes the fields immediately prior to inserting the record. You can override field values here.

CapeSoft Office Inside Excel Documentation

JumpStart for adding Excel functionality into your application

Data Import into/Export from your application.

Implementing a wizard to allow the user to map the data fields to the excel columns (Only available in ABC applications at this stage)

Importing/Exporting data programmatically from your application

Exporting your reports to Excel

Use the oiExcel object in your application.

Converting an Excel Macro into your application

How To's and Code Examples

Data Import/Export HowTos and Examples

Save a File to an Excel Workbook.

Save a Browse Queue to an Excel Workbook

Save a View to an Excel workbook

Load from an Excel SpreadSheet into a Queue

Load from an Excel Spreadsheet into a File (Table)

How to change values during import or export (by overriding the TakeRecord and InsertRecord methods)

Export data to an existing Excel file (appending to or overwriting part of a worksheet)

How to ignore certain fields

How to map columns to fields

How to use the abc filemanager to insert (and handle duplicate entries)

How to start at a particular row, and only export/import a certain number of rows

How to add additional information (like a total) at the end of the export

Implementing Translation in the Excel Import Wizard

How Tos and Code Examples / Controlling Excel from your application

Setting the Cell color using the Excel Palette (required reading)

How to manage the Palette

How to write to and format cells

Conditionally Deleting Rows from a Workbook

Classes

The oiExcel Class - Introduction

The oiExcel Class - Methods Listed by Usage

AutoFilter

Calculate

ClearPrintArea

CloseWorkBook

Copy

Find

ColumnName

CountOpenWorkBooks

CountUsedRows

CountUsedColumns

CountWorksheets

DeleteSelection

DeleteWorksheet

ErrorTrap

ExcelImport

GetActivePrinter

ImportSetup

GetObjects

ImportComplete

FreezePanes

GetCellBackgroundColor

GetColumnAddressFromColumnNumber

GetColumnWidth

GetInfo

GetFileName

GetPaletteColor

GetRowHeight

HideFormulaBar

HideStatusBar

HideToolbar

ImportXLSFile

Init

InsertColumn

InsertRow

InsertWorksheet

Kill

MergeCells

NewWorkbook

OpenWorkbook

Paste

PrintWorksheetProcedure

PrintMe

PrintPreview

ProtectWorkbook

ProtectWorksheet

ReadCell

Redo

RenameWorksheet

ReplaceText

CapeSoft Office Inside
Excel Documentation