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

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

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

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

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

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

Calculate ()

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

Parameters

none

Return Value

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

Examples

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

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

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

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

ClearPrintArea ( ) ,byte,proc

MyExcel.ClearPrintArea ()

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

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

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

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

Parameters

pFind

The string to search for.

pAfter [Optional]

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

lookIn [Optional]

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

 lookAt

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

searchOrder

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

searchDirection

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

matchcase

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

matchByte

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

Return Value

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

ColumnName (long columnNumber)

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

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

Parameters

long columnNumber:

Return Value

Returns a string that contains the name of the column.

Examples

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

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

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

    MyExcel.SetCalculations(oix:CalculationAutomatic)

TempLong = MyExcel.CountOpenWorkbooks()

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

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

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

• ( Not yet implemented... )

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

• Deletes the currently selected WorkSheet.

ErrorTrap (string pErrorString, string pFunctionName)

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

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

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

Parameters

string pErrorString: A string containing the error description.

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

Examples

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

        HandleInitFailure()

    end

Return Values

None

Notes

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

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

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

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

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

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

Parameters

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

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

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

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

Return Values

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

Examples

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

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

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

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

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

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

ImportSetup ()

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

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

Parameters

None

Return Values

None

Examples

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

ImportComplete ()

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

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

Parameters

None

Return Values

None

Examples

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

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

GetCellBackgroundColor (string fromCell, string toCell) , long

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

Parameters

string fromCell

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

string toCell

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

Return Value

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

Examples

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

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

GetColumnAddressFromColumnNumber (long pColumnNumber)

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

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

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

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

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

GetFileName (), string

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

Paremeters

None

Return Value

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

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

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

Parameters

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

Return Value

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

Notes (Important)

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

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

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

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

Examples

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

Example 1

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

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

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

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

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


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

    return 0

Example 2

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

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

Example 3

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

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

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

• Returns the height of a row (see example above)

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

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

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

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

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

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

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

Examples

Typically you would code as follows:

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

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

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

ReturnValue        byte
  CODE

    if pRowNumber > 10
        ! you don't have to do this, but you can return 
                ! record:OutOfRange to stop processing records
        return Record:OutOfRange
    end

    if access:TempExcelTable.tryinsert() = level:benign
        TMP1:ColumnA = pA
        TMP1:ColumnB = pB
        TMP1:ColumnC = pC
        TMP1:ColumnD = pD
        TMP1:ColumnE = pE
        TMP1:ColumnF = pF
        TMP1:ColumnG = pG
        Access:TempExcelTable.update()
    end

    ReturnValue = PARENT.ImportXLSFile_TakeRecord(pRowNumber, pA, pB, pC, pD, |
                                                  pE, pF, pG, pH, pI, pJ)

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

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

Parameters

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

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

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

Return Values

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

Examples

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

! Initialise with Excel hidden and event handling on:

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

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

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

MyExcel.Kill()

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

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

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

• Creates a new Workbook.
• Returns true (1) if no problems were experienced.

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

Parameters

pFileName

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

Return Value

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

Examples

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

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

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

Example

MyExcel.Paste()

This method has not been implemetned in this realease.

Example

MyExcel.PasteSpecial()

Notes

Not yet implemented...

! 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    

Prints the current document to the default system printer.

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

Return Value

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

Examples

MyExcel.PrintMe()

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

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

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

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

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

cellValue         string(255)
cellFormula       string(255)

code

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

        

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

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

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

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

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

Return Values

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

Parameters

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

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

 Remarks

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

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

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

Examples

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

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

! Prompt the user
MyExcel1.SaveAs()    

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

• ( Not yet implemented... )

• 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

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

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

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

SetCalculations (long calcType)

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

Parameters

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

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

Return Value

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

Examples

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

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

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

SetScreenUpdating (long updateScreen)

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

Parameters

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

Return Value

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

Examples

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

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

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

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

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

Parameters

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

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

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

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

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

Return Values

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

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

Examples

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

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

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

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

Parameters

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

Return Value

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

Examples

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

Remarks

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

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

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

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

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

Parameters

long pFontColor

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

string pFromCell (optional)

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

string pToCell (optional)

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

Return Value

Returns true (1) if no problems were experienced.

Remarks

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

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

Examples

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

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

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

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

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

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

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

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

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

Parameters

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

long pFormatType: Must be one of the following equates:

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

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

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

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

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

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

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

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

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

Examples

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

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

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

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

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

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

You can even use formats such as:

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

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

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

$ English (Caribbean)

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

[$$-2409]# ##0.00

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

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

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

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

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

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

Parameters

byte pSwatch

The position in the palette to set

long pColor

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

Return Values

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

Notes

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

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

Examples

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

Example 1

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

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

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

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

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


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

    return 0

Example 2

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

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

Example 3

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

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

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

See Also

Color Management in Excel and Cell Formatting

Methods: GetPaletteColor, SetPaletteColor, SetCellFontColor, GetCellBackgroundColor, SetCellBackGroundColor

Properties: interpretClarionColorEquates

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

• 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

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

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

• 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

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

TakeImportRecord (long recordNumber, excelImportQType cellsQ)

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

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

Parameters

long recordNumber: The record number being processed.

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

excelImportQType queue
cell                 string(_oit:LargeStringSize)
                 end

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

Return Values

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

Example

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

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

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

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

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

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

Undo ( ) , byte, proc

MyExcel.Undo()

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

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

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

• See the ProtectWorkbook method.

• See the ProtectWorksheet method.

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

• Writes the text passed in to the cell identified in pCellAddress, or in the currently selected cell if pCellAddress is not passed
• Returns true (1) if no problems were experienced

Example

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

currentRow         long
  code
    MyExcel.NewWorkbook()
    currentRow = 1

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

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

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

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

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

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

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

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