ComponentSource
| |ExcelController Plus |
| | |
|[pic] |Version independent Microsoft Excel integration in an ActiveX |
| |component |
| | |
| |Rainier Software Solutions |
[pic]
© Rainier Software Solutions 1996-2003. All Rights Reserved.
For more information contact info@rainier.co.uk
Contents
1. What is ExcelController Plus? 5
2. Installation and Use 7
3. Object, Property and Function Reference 8
3.1 ExcelControl Object Properties 8
3.1.1 DisplayErrors 8
3.1.2 ErrorText 8
3.1.3 IsV2000OrLater 9
3.1.4 IsV2002OrLater 9
3.1.5 IsV2003OrLater 9
3.1.6 IsV97OrLater 9
3.1.7 ResultValue 9
3.1.8 ReturnCode 10
3.1.9 ExcelApp 10
3.1.10 ExcelVersion 10
3.2 ExcelControl Object Methods 10
3.2.1 xlcClearRange 13
3.2.2 xlcClearRangeContents 14
3.2.3 xlcClose 15
3.2.4 xlcCloseWorkbook 15
3.2.5 xlcCopyOrMoveSheet 16
3.2.6 xlcCopyRange 17
3.2.7 xlcCreateNewWorkbook 18
3.2.8 xlcDeleteRowsOrColumns 19
3.2.9 xlcDeleteSheet 19
3.2.10 xlcDeriveRangeName 20
3.2.11 xlcFormatRange 21
3.2.12 xlcFormatTable 22
3.2.13 xlcFreeze 23
3.2.14 xlcGetExcelWindowHandle 24
3.2.15 xlcGetRangeBoundary 25
3.2.16 xlcGetValueFromCell 25
3.2.17 xlcGetValueFromCellByRC 26
3.2.18 xlcGetWorkbookName 27
3.2.19 xlcGetWorkbookPath 27
3.2.20 xlcGoto 28
3.2.21 xlcGotoRCRange 29
3.2.22 xlcImportTextFile 29
3.2.23 xlcInsertChartAsNewSheet 31
3.2.24 xlcInsertChartOnSheet 32
3.2.25 xlcInsertPageBreak 34
3.2.26 xlcInsertPictureAtRange 35
3.2.27 xlcInsertPivotTable 36
3.2.28 xlcInsertRowsOrColumns 37
3.2.29 xlcInsertSheet 38
3.2.30 xlcInsertValue 39
3.2.31 xlcLaunch 40
3.2.32 xlcLaunchNewInstance 40
3.2.33 xlcMergeCells 41
3.2.34 xlcOpenWorkbook 42
3.2.35 xlcPasteRange 43
3.2.36 xlcPasteSpecialRange 43
3.2.37 xlcPrintRange 45
3.2.38 xlcPrintSheet 45
3.2.39 xlcPrintWorkbook 46
3.2.40 xlcProtectWorkbook 47
3.2.41 xlcProtectWorksheet 47
3.2.42 xlcRenameSheet 48
3.2.43 xlcReplaceAll 49
3.2.44 xlcRunMacro 50
3.2.45 xlcSaveWorkbook 51
3.2.46 xlcSaveWorkbookAsType 52
3.2.47 xlcSaveWorksheetAsType 53
3.2.48 xlcSetAlignment 54
3.2.49 xlcSetAutocalculateOff 55
3.2.50 xlcSetAutocalculateOn 55
3.2.51 xlcSetBackgroundColor 56
3.2.52 xlcSetBackgroundColorRGB 57
3.2.53 xlcSetCellProtection 58
3.2.54 xlcSetFont 59
3.2.55 xlcSetNumberFormat 60
3.2.56 xlcSetRangeName 61
3.2.57 xlcSetRowOrColumnSize 62
3.2.58 xlcSetStatusBarMessage 63
3.2.59 xlcSetWindowSplit 63
3.2.60 xlcSortRange 64
3.2.61 xlcUnFreeze 65
3.2.62 xlcUnprotectWorkbook 65
3.2.63 xlcUnprotectWorksheet 66
4. Specifying Excel Ranges 67
5. Excel Templates and Data Sources 68
5.1 Text Files and xlcImportTextFile 68
5.2 Using Templates 69
6. Error Handling 70
7. ExcelController in Action - Creating a Formatted Spreadsheet 71
8. Sample Code 74
8.1 Declaring the main ExcelController object variable 74
8.2 Setting Error Display On 74
8.3 Launching Excel 75
8.4 Opening a workbook 75
8.5 Checking for errors after an ExcelController function call 75
8.6 Complete Workbook Generation Procedure 75
8.7 Combining ExcelController functions with native calls to Excel for additional functionality 76
1 What is ExcelController Plus?
ExcelController Plus is an is an ActiveX component which allows Developers to quickly and easily integrate their applications with multiple versions of Microsoft Excel™.
Developers who wish to integrate their applications with Excel on the user machine face a problem with regard to the different features and behaviour of different Excel versions. Applications which are written for one version of Excel do not necessarily work when run on a workstation which has a different version installed.
Developers do not want to have to specify that their Users have a particular version, nor do they want to revisit their applications when the next version of Excel is released. ExcelController addresses these issues by adding a layer on top of the functionality exposed by Excel, detecting what version is present at runtime and executing code that will work with that version. It is now possible to write one set of code which can be deployed to users without worrying what version of Excel they have. And when the next version of Excel is released let us worry about testing its behaviour and making any changes necessary to ensure that the ExcelController functions behave in a manner as consistent as possible with the earlier versions.
Further, ExcelController aggregates some complex Excel functionality into high level new functions which allow the Developer to do the most commonly required tasks: create new workbooks; insert data; build, format and print tables and reports etc. with just a few lines of code and without any need to understand the complex Excel object model. If your goal is to integrate your applications with Excel with the minimum of effort and the lowest learning requirement then ExcelController is for you.
With the help of ExcelController Plus a client application can
• determine if Excel is running and return the Excel window handle
• launch Excel in any state (hidden, normal, minimized, maximized)
• open and close Excel workbooks (optionally with passwords and/or read only)
• navigate workbooks, activating workbooks, worksheets and ranges
• create new workbooks, optionally based on pre-defined templates
• import data into Excel from text files. If desired the data can be embedded in Excel template workbooks.
• format Excel tables and defined ranges to create a sophisticated, custom look
• insert values, pictures and formulae into cells. Retrieve data from cells.
• copy data, formulae and/or formatting from one part of a workbook to another.
• insert, delete and rename worksheets
• create a variety of different charts
• create pivot tables
• run Excel macros
• search and replace
• print ranges, worksheets and workbooks
• protect and unprotect cells, worksheets and workbooks
• freeze and unfreeze the Excel window
• close Excel
and much more.
Using combinations of the ExcelController functions the Developer can do most of the things he or she would normally need to do without needing to learn the Excel object model. But ExcelController also provides access to the native Excel.Application object exposed by Excel itself so you can code additional functionality if you wish.
ExcelController also offers sophisticated error handling. Many things can go wrong when working with a 3rd party application on a machine over which you have no control. ExcelController allows you to trap errors in a professional manner and return friendly error codes and text to the calling application to be acted upon as the Developer chooses. For more information on this see the Error Handling section later in this document.
When the ExcelController component is installed on the workstation, a Developer building an application with a product which supports the use of ActiveX components (such as Visual Basic or Delphi) can access any of the ExcelController methods (functions) and properties from within their code.
ExcelController is a 32-bit in-process OLE server DLL which may be used on any 32-bit Windows platform (NT/2000/XP or 95/98/Me/XP). ). It can be used with Excel versions 5.0, 95, 97, 2000, 2002 or 2003. A few ExcelController functions do not work with the pre-97 versions as they offer functionality which was not available in these versions but where this is the case it is clearly pointed out in the documentation (see individual function descriptions for details) and the functions do not fail – instead they return a trappable error indicating that that version of Excel does not support that functionality. This allows the Developer to handle the exception smoothly and avoid unpleasant application failures.
ExcelController includes a sample Visual Basic project which demonstrates how to call the component functions.
ExcelController is a companion product to WordController which is for Microsoft Word™ integration. The two products work in a similar way but are completely independent.
2 Installation and Use
To use the ExcelController Plus component during development it is first necessary to install it on the developer workstation. This is done by executing the installation program. The setup program installs the DLL and makes the appropriate entries in the system registry. It is NOT sufficient merely to copy the DLL onto the workstation. Note that, as with any other ActiveX component or custom control, it will also be necessary to deploy the component with any application which uses its functions.
Once the component has been installed on the developer workstation, it can be referenced by any development tool that supports the containment of ActiveX controls. This document contains example code for Microsoft Visual Basic or VBA (Visual Basic for Applications, as supported by Microsoft Word, Access and Excel itself).
If the development tool supports object references then the component reference should be added to the project. In Visual Basic this is done by selecting Project…References and choosing the ExcelController component from the list supplied. In the Microsoft Office Visual Basic Editor the appropriate menu item is Tools…References.
If the development tool does not support project references then it will usually offer a CreateObject command or equivalent which will load the DLL into memory and make its methods and properties available for use. The CreateObject statement requires a string class identifier parameter which is resolved through the Windows registry to obtain the physical location of the DLL to load. An example CreateObject in Visual Basic would be
Dim xlcObj as Object
Set xlcObj = CreateObject("ExcelController.ExcelControl5")
Note: The above code is for ExcelController v5.x, earlier versions used
Set xlcObj = CreateObject("ExcelController.ExcelControl")
This would load the component into memory and establish a reference to it so that its functions and properties could be manipulated. Such a statement would not be required if a project reference had been defined. In this case it would simply be necessary to use the New keyword when declaring the object variable:
Dim xlcObj as New ExcelController.ExcelControl5
or simply
Dim xlcObj as New ExcelControl5
Now the component methods and properties can be accessed by making reference to the declared object variable. For example:
‘to launch Excel
RetCode = xlcObj.xlcLaunch
‘to set error display on
xlcObj.DisplayErrors = True
3 Object, Property and Function Reference
ExcelController exposes a single object named ExcelControl5. The following sections define the Properties and Methods of this object.
All example code shown is Visual Basic. In the examples xlcObj refers to a ExcelControl5 object that has been previously declared with
Dim xlcObj as New ExcelControl5
1 ExcelControl Object Properties
DisplayErrors
ErrorText
IsV2000OrLater
IsV2002OrLater
IsV2003OrLater
IsV97OrLater
ResultValue
ReturnCode
ExcelApp
ExcelVersion
1 DisplayErrors
xlcObj.DisplayErrors
Determines whether error messages are automatically displayed by the component. Set to True to turn error messages on. Default value is False.
See also Error Handling.
2 ErrorText
xlcObj.ErrorText
Returns the error text (if any) from the most recent function/method call.
See also Error Handling.
3 IsV2000OrLater
xlcObj.IsV2000OrLater
Returns True (-1) if the version of Excel currently being managed by ExcelController is Excel 2000 or later, otherwise returns False (0). Excel must have been previously been launched with xlcLaunch or xlcLaunchNewInstance.
See also IsV97OrLater, IsV2002OrLater, IsV2003OrLater.
4 IsV2002OrLater
xlcObj.IsV2002OrLater
Returns True (-1) if the version of Excel currently being managed by ExcelController is Excel 2002 or later, otherwise returns False (0). Excel must have been previously been launched with xlcLaunch or xlcLaunchNewInstance.
See also IsV97OrLater, IsV2000OrLater, IsV2003OrLater.
5 IsV2003OrLater
xlcObj.IsV2003OrLater
Returns True (-1) if the version of Excel currently being managed by ExcelController is Excel 2003 or later, otherwise returns False (0). Excel must have been previously been launched with xlcLaunch or xlcLaunchNewInstance.
See also IsV97OrLater, IsV2000OrLater, IsV2002OrLater.
6 IsV97OrLater
xlcObj.IsV97OrLater
Returns True (-1) if the version of Excel currently being managed by ExcelController is Excel 97 or later, otherwise returns False (0). Excel must have been previously been launched with xlcLaunch or xlcLaunchNewInstance.
See also IsV2000OrLater, IsV2002OrLater, IsV2003OrLater.
7 ResultValue
xlcObj.ResultValue
Returns the result value from the most recent function/method call. Where methods do not return any value and in these cases ResultValue is set to an empty string.
See also ReturnCode.
8 ReturnCode
xlcObj.ReturnCode
Returns the numeric return code from the most recent function/method call. This value is used to determine whether or not the method completed successfully. Negative values for ReturnCode indicate that an error has occurred. A ReturnCode of zero indicates success. Occasionally a non-zero positive return code can also indicate success so the best way to test for an error condition is to use
If xlcObj.ReturnCode < 0 Then …
See also ResultValue.
9 ExcelApp
xlcObj.ExcelApp
Returns the Excel.Application object for the instance of Excel being managed by ExcelController. This is provided so that the Developer can make additional calls to Excel to perform any required functions supported by Excel but not supported directly by ExcelController.
For information on the objects, properties and methods associated with the Excel.Application object see the Microsoft Excel Visual Basic Reference.
10 ExcelVersion
xlcObj.ExcelVersion
Returns the version of Excel currently being managed by ExcelController. Excel must have been previously been launched with xlcLaunch or xlcLaunchNewInstance.
The version is returned as a string. The version for the first release of Excel 2000, for example, is “9.0”.
See also ReturnCode, xlcLaunch, xlcLaunchNewInstance.
2 ExcelControl Object Methods
Square brackets [] in this section indicate optional parameters.
xlcClearRange([RangeName As String]) As Long
xlcClearRangeContents([RangeName As String]) As Long
xlcClose() As Long
xlcCloseWorkbook([WorkbookName As String] [, PromptForSave As Boolean]) As Long
xlcCopyOrMoveSheet(CopyOrMove As String [, SourceSheet As Variant] [, NewPosition As Variant] [, NewSheetName As String]) As Long
xlcCopyRange([SourceRange As String] [, DestinationRange As String]) As Long
xlcCreateNewWorkbook([TemplateName As String]) As Long
xlcDeleteRowsOrColumns(DeleteType As String, StartNumber As Integer, DeleteNumber As Integer) As Long
xlcDeleteSheet([Sheet As Variant]) As Long
xlcDeriveRangeName(LeftColumn As Integer, TopRow As Integer, RightColumn As Integer, BottomRow As Integer) As String
xlcFormatRange(StyleName As String [, RangeName As String]) As Long
xlcFormatTable([RangeName As String] [, HeaderRow As Boolean] [, ApplyBorders As Boolean] [, AutoFitCells As Boolean] [, WrapText As Boolean] [, CellStyleName As String] [, HeadingStyleName As String]) As Long
xlcFreeze() As Long
xlcGetExcelWindowHandle() As Long
xlcGetRangeBoundary(BoundaryType As String [, RangeName As String]) As String
xlcGetValueFromCell([RangeName As String]) As Variant
xlcGetValueFromCellByRC(RowNumber As Integer, ColumnNumber As Integer) As Variant
xlcGetWorkbookName() As String
xlcGetWorkbookPath([IncludeFileName As Boolean]) As String
xlcGoto(DestinationType As String, Destination As Variant) As Long
xlcGotoRCRange(Top As Integer, Left As Integer, Bottom As Integer, Right As Integer) As Long
xlcImportTextFile(TextFilePath As String [, RangeName As String] [, Delimiter As String] [, Shift As String]) As Long
xlcInsertChartAsNewSheet(Gallery As Integer [, SourceRange As String] [, ChartFormat As Integer] [, PlotBy As Integer] [, CategoryLabels As Integer] [, SeriesLabels As Integer] [, HasLegend As Boolean] [, Title As String] [, CategoryTitle As String] [, ValueTitle As String] [, ExtraTitle As String]) As Long
xlcInsertChartOnSheet(Left As Integer, Top As Integer, Width As Integer, Height As Integer, Gallery As Integer [, SourceRange As String] [, ChartFormat As Integer] [, PlotBy As Integer] [, CategoryLabels As Integer] [, SeriesLabels As Integer] [, HasLegend As Boolean] [, Title As String] [, CategoryTitle As String] [, ValueTitle As String] [, ExtraTitle As String]) As Long
xlcInsertPageBreak([HorizontalOrVertical As String] [, RangeName As String]) As Long
xlcInsertPictureAtRange(PictureFileName As String [, RangeName As String] [, SizeToRange As Boolean]) As Long
xlcInsertPivotTable([SourceRange As String] [, Destination As String] [, PivotTableName As String] [, RowFieldNames As String] [, ColumnFieldNames As String] [, DataFieldNames As String] [, PageFieldNames As String] [, NameDelimiter As String] [, ShowRowGrandTotals As Boolean] [, ShowColGrandTotals As Boolean]) As Long
xlcInsertRowsOrColumns(InsertType As String [, StartNumber As Integer] [, InsertNumber As Integer]) As Long
xlcInsertSheet([InsertPosition As Variant] [, NewSheetName As String]) As Long
xlcInsertValue(Value As Variant [, RangeName As String]) As Long
xlcLaunch([WindowState As Integer]) As Long
xlcLaunchNewInstance([WindowState As Integer]) As Long
xlcMergeCells([RangeName As String]) As Long
xlcOpenWorkbook(WorkbookName As String [, ReadOnly As Boolean] [, Password As String]) As Long
xlcPasteRange([DestinationRange As String]) As Long
xlcPasteSpecialRange([DestinationRange As String] [, PasteType As String] [, SpecialOperation As String] [, IgnoreBlanks As Boolean]) As Long
xlcPrintRange([RangeName As String] [, NumberOfCopies As Integer] [, Portrait As Boolean]) As Long
xlcPrintSheet([Sheet As Variant] [, NumberOfCopies As Integer]) As Long
xlcPrintWorkbook([NumberOfCopies As Integer]) As Long
xlcProtectWorkbook([Password As String]) As Long
xlcProtectWorksheet([Password As String] [,Objects As Boolean] [,Contents As Boolean] [, Scenarios As Boolean]) As Long
xlcRenameSheet(NewSheetName As String [, Sheet As Variant]) As Long
xlcReplaceAll(StringToReplace As String, ReplacementString As String [, HeaderAndFooter As Boolean] [, MatchWholeCell As Boolean] [, MatchCase As Boolean]) As Long
xlcRunMacro(MacroName As String [, NumArguments As Integer] [, Arg1 As Variant] [, Arg2 As Variant] [, Arg3 As Variant] [, Arg4 As Variant] [, Arg5 As Variant]) As Long
xlcSaveWorkbook([SaveAsFilePath As String] [, Password As String]) As Long
xlcSaveWorkbookAsType(OutputFileFormat As String [, SaveAsFilePath As String] [, Password As String] [, Overwrite As Boolean]) As Long
xlcSaveWorksheetAsType(OutputFileFormat As String [, SaveAsFilePath As String] [, Password As String] [, Overwrite As Boolean]) As Long
xlcSetAlignment([RangeName As String] [, HorizontalAlignment As Integer] [, IndentLevel As Integer] [, VerticalAlignment As Integer] [, WrapText As Boolean]) As Long
xlcSetAutocalculateOff() As Long
xlcSetAutocalculateOn() As Long
xlcSetBackgroundColor(ColorIndex As Long [, RangeName As String]) As Long
xlcSetBackgroundColorRGB(RGBColor As Long [, RangeName As String]) As Long
xlcSetCellProtection([RangeName As String] [, Locked As Boolean] [, Hidden As Boolean]) As Long
xlcSetFont([RangeName As String] [, FontName As String] [, FontSize As Integer] [, Bold As Integer] [, Italic As Integer] [, Underline As Integer] [, Strikethrough As Integer] [, FontColor As Long]) As Long
xlcSetNumberFormat(FormatString As String [, RangeName As String]) As Long
xlcSetRangeName(RangeName As String, LeftColumn As Integer, TopRow As Integer, RightColumn As Integer, BottomRow As Integer) As Long
xlcSetRowOrColumnSize(ObjectType As String, StartNumber As Integer, NumberOfRowsOrColumns As Integer, NewSize As Single) As Long
xlcSetStatusBarMessage([MessageText As String]) As Long
xlcSetWindowSplit([Column As Integer] [, Row As Integer] [, FreezePanes As Boolean]) As Long
xlcSortRange([RangeName As String] [, ByRowOrColumn As String] [, Key1 As String] [, Order1 As String] [, Key2 As String] [, Order2 As String] [, Key3 As String] [, Order3 As String] [, HeaderRow As Boolean] [, CaseSensitive As Boolean]) As Long
xlcUnfreeze() As Long
xlcUnprotectWorkbook([Password As String]) As Long
xlcUnprotectWorksheet([Password As String]) As Long
1 xlcClearRange
xlcObj.xlcClearRange([RangeName])
Purpose:
Clears an entire range within the active worksheet. Clears cell formatting as well as values and formulae.
RangeName (optional, default "") specifies the area to clear. It may be a single cell. Valid values for RangeName are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
See also xlcClearRangeContents.
Returns:
0 on success
-9001 if Excel is not launched
-91 if command unavailable
-1004 if range not found
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example clears the range B5 to AA12. The second clears the entire worksheet. The third clears the currently selected range.
RetCode = xlcObj.xlcClearRange("B5:AA12")
RetCode = xlcObj.xlcClearRange("UsedRange")
RetCode = xlcObj.xlcClearRange
2 xlcClearRangeContents
xlcObj.xlcClearRangeContents([RangeName])
Purpose:
Clears an entire range within the active worksheet. Clears cell values and formulae but leaves formatting unaffected.
RangeName (optional, default "") specifies the area to clear. It may be a single cell. Valid values for RangeName are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
See also xlcClearRange.
Returns:
0 on success
-9001 if Excel is not launched
-91 if command unavailable
-1004 if range not found
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example clears the contents of range B5 to AA12. The second clears the the contents of the entire worksheet. The third clears the contents of the currently selected range. In all cases formatting (font, size, bold, etc.) is left unchanged.
RetCode = xlcObj.xlcClearRangeContents("B5:AA12")
RetCode = xlcObj.xlcClearRangeContents("UsedRange")
RetCode = xlcObj.xlcClearRangeContents
3 xlcClose
xlcObj.xlcClose([PromptForSave])
Purpose:
Closes the instance of Excel being managed by ExcelController.
The PromptForSave parameter (optional, default False) specifies whether or not Excel should prompt the user to save any changes which may have been made to any open workbooks before closing.
False do not prompt for save (changes are not saved)
True prompt for save
Tip: Note that when a User manually closes an instance of Excel which has been launched via OLE Automation (i.e. via ExcelController) the instance may actually remain running invisibly (some versions of Excel only), so it is a good idea to always have your application close Excel if you have started it.
Returns:
0 on success.
-9001 if Excel is not found.
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
RetCode = xlcObj.xlcClose
4 xlcCloseWorkbook
xlcObj.xlcCloseWorkbook([WorkbookName] [, PromptForSave])
Purpose:
Closes a workbook. May be used to close the active workbook or a named workbook.
The WorkbookName parameter (optional, default "") is the name of the workbook to be closed. A value of "" causes the active workbook to be closed. If a name is specified it should be the full name as appears in the caption bar e.g. "TEST.XLS" or "MYDATA.TXT" or "Book7".
The PromptForSave parameter (optional, default False) specifies whether or not Excel should prompt the user to save any changes which may have been made to the workbook before closing. This parameter is only relevant if changes have been made.
False do not prompt for save (changes are not saved)
True prompt for save
Returns:
0 on success.
-9001 if Excel is not found.
-9004 invalid parameter value(s)
-91 if command unavailable
-1004 if workbook not found
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The first example closes the currently active workbook without prompting for save. Any changes made will be lost. The second example closes workbook "Book1" and the user is prompted to save changes if any have been made since the last Save operation.
RetCode = xlcObj.xlcCloseWorkbook
RetCode = xlcObj.xlcCloseWorkbook("Book1", True)
5 xlcCopyOrMoveSheet
xlcObj.xlcCopyOrMoveSheet(CopyOrMove [, SourceSheet] [, NewPosition] [, NewSheetName])
Purpose:
Moves or copies a worksheet within the active workbook.
CopyOrMove specifies the type of operation to be performed. Valid values are:
• "Copy"
• "Move"
SourceSheet (optional, default "") may be a sheet number, a sheet name or blank. A blank value will cause the function to operate on the currently active sheet.
NewPosition (optional, default "") specifies where to move or copy the sheet within the workbook. It may be a sheet number, a sheet name, blank, or the special value "End". Passing a value of "End" to the function will cause the sheet to be inserted as the last sheet of the workbook. Passing a blank value, which is only valid for a copy operation, will cause the new copy sheet to be inserted immediately prior to the source sheet. With any other value the sheet will be inserted at the position specified by the sheet number or name and any sheets after it will be shifted to the right. To place the sheet as the first sheet, use a value of 1 for NewPosition.
NewSheetName (optional, default "") specifies the name for the new sheet. This parameter is only valid for a copy operation. If omitted ("") Excel will give the new copy sheet a default name.
Returns:
0 on success
-9 subscript out of range (if a specified sheet number does not exist)
-91 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
-9087 a sheet with the specified new name already exists in the active workbook
-1004 sheet not found
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example takes the first sheet and moves it to the end. The second copies the sheet named "My Sheet" to a new sheet named "New Sheet" and inserts it before the third sheet in the workbook. The third example creates a copy of the active sheet immediately before it in the workbook, giving it the default name chosen by Excel.
RetCode = xlcObj.xlcCopyOrMoveSheet("Move", 1, "End")
RetCode = xlcObj.xlcCopyOrMoveSheet("Copy", "MySheet", 3, "New Sheet")
RetCode = xlcObj.xlcCopyOrMoveSheet("Copy")
6 xlcCopyRange
xlcObj.xlcCopyRange([SourceRange] [, DestinationRange])
Purpose:
Copies the contents of a entire range within the active worksheet to another range within the same worksheet or the clipboard. This is a powerful function which can be used to copy both data and formulae.
SourceRange (optional, default "") specifies the area to copy. It may be a single cell. Valid values for SourceRange are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
DestinationRange (optional, default "") specifies the area to copy the range into, or that it should be copied to the clipboard. DestinationRange may be a single cell. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
• "Clipboard"
For more information on range names see "Specifying Excel Ranges" below.
Note 1: DestinationRange must either be on the active worksheet or have the special value "Clipboard". If you want to copy a range to another worksheet you must execute this function with "Clipboard" as the DestinationRange, then go to the destination sheet with xlcGoto, and finally paste the range into that sheet with xlcPasteRange. It is, however, possible to use this function to copy data from another workbook or worksheet by including the workbook/worksheet in the specification of SourceRange, e.g. "[Book2]Sheet1!A1:F20"
Note 2: An error (-1004) will be returned if SourceRange and DestinationRange are different shapes. However, any rectangular range can be pasted into a single cell - the range will be pasted with that cell at the top left of the rectangle. Also a portion of a single row or column can be pasted into a rectangular range in which the relevant side has the same number of cells - Excel is intelligent enough to copy the range across or down, as appropriate, to fill the rectangle.
Returns:
0 on success
-91 if command unavailable
-1004 if range not found or copy and paste areas are different shapes
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example copies the range B5 to AA12 to the clipboard. The second copies a portion of a column A1:A6 to the same area in columns D to F . The third copies the currently selected range to a named range "DestRange". The fourth copies the rectangle A5:D7 to a similar rectangle with the top left cell at C13.
RetCode = xlcObj.xlcCopyRange("B5:AA12", "Clipboard")
RetCode = xlcObj.xlcCopyRange("A1:A6", "D1:F6")
RetCode = xlcObj.xlcCopyRange(, "DestRange")
RetCode = xlcObj.xlcCopyRange("A5:D7", "C13")
7 xlcCreateNewWorkbook
xlcObj.xlcCreateNewWorkbook([TemplateName])
Purpose:
Creates and opens a new Excel workbook, optionally based on a template (.XLT file).
TemplateName (optional, default "") specifies the file path of the template on which the new workbook is to be based. This parameter is optional - if a value of "" is supplied the new workbook will be based on the default template.
Returns:
0 on success
-1004 if the template was not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The first example creates a new blank workbook, the second creates a new workbook based on a template "c:\mytemp.xlt".
RetCode = xlcObj.xlcCreateNewWorkbook
TemplatePath = App.Path & "\mytemp.xlt"
RetCode = xlcObj.xlcCreateNewWorkbook(TemplatePath)
8 xlcDeleteRowsOrColumns
xlcObj.xlcDeleteRowsOrColumns(DeleteType, StartNumber, DeleteNumber)
Purpose:
Deletes rows or columns in the active workbook.
DeleteType specifies whether rows or columns are to be deleted. Valid values are:
• "Rows" or "Row"
• "Columns" or "Column"
The StartNumber parameter specifies the number of the first row or column to be deleted.
The DeleteNumber parameter specifies the number of rows or columns to be deleted.
Returns:
0 on success
-1004 if no worksheet is open
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The folowing code deletes rows 5 to 7 in the active worksheet.
RetCode = xlcObj.xlcDeleteRowsOrColumns("Rows", 5, 3)
9 xlcDeleteSheet
xlcObj.xlcDeleteSheet([Sheet])
Purpose:
Delete a specified sheet in the active workbook. The sheet may be specified by a number or name.
Sheet (optional, default blank) may be a sheet number, a sheet name or blank. A blank value will cause the function to delete the currently active worksheet.
Note that Excel displays a confirmation message when deleting sheets which cannot be suppressed.
Returns:
0 on success
-9 subscript out of range (if a specified sheet number does not exist)
-91 if command unavailable
-1004 sheet not found or an attempt was made to delete the only visible sheet
-9001 if Excel is not launched
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
'deletes the active worksheet
RetCode = xlcObj.xlcDeleteSheet
'deletes the 3rd worksheet
RetCode = xlcObj.xlcDeleteSheet(3)
'deletes the worksheet named "Sheet3"
RetCode = xlcObj.xlcDeleteSheet("Sheet3")
10 xlcDeriveRangeName
xlcObj.xlcDeriveRangeName(LeftColumn, TopRow, RightColumn, BottomRow)
Purpose:
Creates a string containing a valid range name of the form "A5:C7" from row and column numbers. Strings returned from this function can be used as RangeName parameters in other functions.
LeftColumn specifies the number of the column at the left hand side of the range. Column A would have a value of 1, column B would be 2, etc.
TopRow specifies the number of the row at the top of the range. Row 1 would have a value of 1, row 2 would be 2, etc.
RightColumn specifies the number of the column at the right hand side of the range. Column A would have a value of 1, column B would be 2, etc.
BottomRow specifies the number of the row at the bottom of the range. Row 1 would have a value of 1, row 2 would be 2, etc.
Returns:
A string containing the range specification, or "" if an error occurs.
ReturnCodes (obtainable from xlcObj.ReturnCode):
0 on success
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The first example will return a string "A5:C7". The second will return "C1".
ReturnValue = xlcObj.xlcDeriveRangeName(1, 5, 3, 7)
ReturnValue = xlcObj.xlcDeriveRangeName(3, 1, 3, 1)
11 xlcFormatRange
xlcObj.xlcFormatRange(StyleName [, RangeName])
Purpose:
Apply a style to a range within the active worksheet.
Pre-defined styles may be held in Excel templates (some are contained in the default template). Styles allow number and text formatting, borders, shading, etc. For more information on how to work with Excel templates see the section "Excel Templates and Data Sources" below.
StyleName specifies the style to be applied to the range.
RangeName (optional, default "") specifies the area to which the style should be applied. It may be a single cell. Valid values for RangeName are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
See also xlcSetFont, xlcSetAlignment, xlcSetNumberFormat.
Returns:
0 on success
-91 if command unavailable
-450 or -1005 if style not found
-1004 if range not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example applies the style " MyNumericStyle" to range B5 to AA12. The second applies it to the entire worksheet. The third applies it to the currently selected range. The fourth applies it to the range of columns C to F.
RetCode = xlcObj.xlcFormatRange("MyNumericStyle", "B5:AA12")
RetCode = xlcObj.xlcFormatRange("MyNumericStyle ", " UsedRange ")
RetCode = xlcObj.xlcFormatRange("MyNumericStyle")
RetCode = xlcObj.xlcFormatRange("MyNumericStyle", "C:F")
12 xlcFormatTable
xlcObj.xlcFormatTable([RangeName] [, HeaderRow] [, ApplyBorders] [, AutoFitCells] [, WrapText] [, CellStyleName] [, HeadingStyleName])
Purpose:
Formats an area of an open worksheet as a table.
RangeName (optional, default "") specifies the area of the worksheet which is to be formatted. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
HeaderRow (optional, default False) specifies whether or not to format the first row of the table differently from the rows below it. This is normally useful if the first row contains column headings.
False do not format the first row of the table differently
True format the first row of the table differently
Note: this parameter has no effect if ApplyBorders = False and HeadingStyleName is blank
ApplyBorders (optional, default True) specifies whether or not to create borders around the whole table.
False do not create borders
True create borders
If ApplyBorders is True, behaviour varies depending on the values of HeaderRow, HeadingStyleName and CellStyleName:
If HeaderRow is True and a heading style is supplied, ApplyBorders adds a border around the whole table with a line style as determined by the top border of the style HeadingStyleName. The bottom border of the header row will have a border as defined by the bottom border of the style HeadingStyleName.
If HeaderRow is True and a heading style is not supplied, ApplyBorders adds a border around the whole table with a default line style (continuous, medium). The bottom border of the header row will also be given a border with the same default line style.
If HeaderRow is False, ApplyBorders simply adds a border around the whole table with a default line style (continuous, medium).
AutofitCells (optional, default True) specifies whether or not to automatically determine appropriate cell sizes based on the length of the data contained within them.
False do not autofit cells
True autofit cells
WrapText (optional, default True) specifies whether or not to set the Wrap Text property of all cells.
False do not set the Wrap Text property
True set the Wrap Text property
Note: If WrapText is False the Wrap Text property may still be set by the styles HeadingStyleName and/or CellStyleName if these are supplied.
The CellStyleName and HeadingStyleName optional parameters (defaults "") specify Excel styles to be applied to the cells within the table. If CellStyleName is supplied and HeadingStyleName is blank (or HeaderRow is False) all cells will be given style CellStyleName. If HeadingStyleName is supplied and HeaderRow is True then the top row of the table will be given style HeadingStyleName. Styles allow a custom look to be created, allowing the user to specify fonts, borders, shading, alignment, number format, etc. They are usually held in templates.
Returns:
0 on success
-91 if command unavailable
-1004 if range not found
-1005 if style not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
RetCode = xlcObj.xlcFormatTable 'accept all defaults
RetCode = xlcObj.xlcFormatTable("CurrentRegion", True, True,
True, True, "CellStyle1", "HeadStyle1")
13 xlcFreeze
xlcObj.xlcFreeze
Purpose:
Freezes the Excel window so that drawing is disabled while subsequent Excel operations are carried out. This speeds execution and provides a smoother interface. A call to this function should always be followed, when operations are complete, by a call to xlcUnFreeze.
Important: This function should be used with caution. If a situation arises where processing is complete and the Excel window is left frozen, it can cause confusion to the User who may not be able to unfreeze it (this is only true for some versions of Excel). Care should be taken to ensure that every subsequent logic path, including error situations, includes a call to xlcUnFreeze.
Returns:
0 on success
-9001 if Excel not launched
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
RetCode = xlcObj.xlcFreeze
14 xlcGetExcelWindowHandle
xlcObj.xlcGetExcelWindowHandle
Purpose:
Determines whether or not Excel is running. If a single occurrence of Excel is running, returns the handle of the Excel window.
It is NOT necessary to use this function before launching (or obtaining a reference to) Excel with xlcLaunch. The function is provided mainly to obtain a Windows handle for the main Excel window itself which is required to do things like bring it to the top, hide it, etc. (normally done using the Windows API).
Returns:
The handle of the Excel window or “” if Excel is not running or an error occurs.
ReturnCodes (obtainable from xlcObj.ReturnCode):
0 on success
-9001 if Excel is not found.
-9002 if multiple occurrences of Excel are found.
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
ResultValue = xlcObj.xlcGetExcelWindowHandle
15 xlcGetRangeBoundary
xlcObj.xlcGetRangeBoundary(BoundaryType [, RangeName])
Purpose:
Returns a row or column boundary (top, left, bottom or right) of a range in the active worksheet, as a number.
BoundaryType specifies which boundary is required. Valid values are:
"Top"
"Left"
"Bottom"
"Right"
RangeName (optional, default "") specifies the position from which to retrieve the boundary value. Valid values for RangeName are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
Returns:
The number of the row or column at the top, left, bottom or right of the range, as specified.
0 on success
-91 if command unavailable
-1004 if range not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example returns the row number of the last used row in the worksheet.
ResultValue = xlcObj.xlcGetExcelWindowHandle
16 xlcGetValueFromCell
xlcObj.xlcGetValueFromCell([RangeName])
Purpose:
Retrieves a value from a cell in the active worksheet. The specified cell may contain any type of value including text, numeric and date. If the range specified contains multiple cells then the value is taken from the top left cell in the range.
RangeName (optional, default "") specifies the position from which to retrieve the value. If the range contains more than a single cell then the returned value is obtained from the top left cell of the range. Valid values for RangeName are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
See also xlcGetValueFromCellByRC
Returns:
A string containing the value found in the specified cell, or "" if an error occurs.
0 on success
-91 if command unavailable
-1004 if range not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
'retrieve a value from the currently selected cell
ResultValue = xlcObj.xlcGetValueFromCell
'retrieve a value from a named cell
ResultValue = xlcObj.xlcGetValueFromCell("C12")
17 xlcGetValueFromCellByRC
xlcObj.xlcGetValueFromCellByRC(RowNumber, ColumnNumber)
Purpose:
Retrieves a value from a cell in the active worksheet. The specified cell may contain any type of value including text, numeric and date.
RowNumber specifies the row number of the cell.
ColumnNumber specifies the column number of the cell. A=1, B=2, etc.
See also xlcGetValueFromCell
Returns:
A string containing the value found in the specified cell, or "" if an error occurs.
0 on success
-91 if command unavailable
-1004 if range not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
'retrieve a value from a cell E3
ResultValue = xlcObj.xlcGetValueFromCell(3, 5)
18 xlcGetWorkbookName
xlcObj.xlcGetWorkbookName
Purpose:
Retrieves the name of the active workbook, not including its file path. Example values returned might include "SALES.XLS" or "Book1". If no workbook is open an error -91 is returned.
Note that it is not possible to set the workbook name without saving it. Use function xlcSaveWorkbook for this.
See also xlcGetWorkbookPath
Returns:
A string containing the name, or "" if an error occurs.
0 on success
-91 if command unavailable
-9001 if Excel is not launched
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
ResultValue = xlcObj.xlcGetWorkbookName
19 xlcGetWorkbookPath
xlcObj.xlcGetWorkbookPath([IncludeFileName])
Purpose:
Retrieves the full path of the active workbook, optionally including its name. Example values returned might include "C:\TEMP" or "C:\TEMP\SALES.XLS" depending on the value of IncludeFileName.
IncludeFileName (optional, default True) indicates whether or not you wish to return just the directory specification or the full path including the filename. IncludeFileName has values as follows:
False return just the directory without the filename (e.g. "C:\TEMP")
True return the full path including filename (e.g. "C:\TEMP\SALES.XLS")
If the active workbook has not been saved and IncludeFileName is False then the function returns "" with a 0 (success) error code. If the active workbook has not been saved and IncludeFileName is True then the function returns just the document name e.g. "Book1" with a 0 (success) error code.
See also xlcGetWorkbookName
Returns:
A string containing the path, or "" if an error occurs.
0 on success
-91 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
ResultValue = xlcObj.xlcGetWorkbookPath
ResultValue = xlcObj.xlcGetWorkbookPath(False)
20 xlcGoto
xlcObj.xlcGoto(DestinationType, Destination)
Purpose:
Go to a specified range, worksheet or workbook. Going to a range selects that range within the active worksheet. Going to a sheet is equivalent to selecting the relevant tab at the bottom of the workbook - the sheet is activated. Going to a workbook is equivalent to selecting the workbook from the Window list on the main menu - the workbook is activated.
DestinationType specifies whether you wish to go to a range, worksheet or workbook. Valid values are:
• "Range"
• "Sheet"
• "Workbook"
The Destination parameter specifies the range or sheet to go to.
If DestinationType is "Range" then valid values for Destination are:
• "CurrentRegion"
• "UsedRange"
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
If DestinationType is "Sheet" then Destination may either be a sheet number or a sheet name. If DestinationType is "Workbook" then Destination may either be a workbook number or a workbook name.
Returns:
0 on success
-1004 destination not found (range, sheet or workbook)
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
RetCode = xlcObj.xlcGoto(“range”, “A3:C5”)
RetCode = xlcObj.xlcGoto(“range”, “MyNamedRange”)
RetCode = xlcObj.xlcGoto(“range”, “UsedRange”)
RetCode = xlcObj.xlcGoto(“sheet”, 1)
RetCode = xlcObj.xlcGoto(“sheet”, “Sheet 3”)
RetCode = xlcObj.xlcGoto(“workbook”, “Book2”)
RetCode = xlcObj.xlcGoto(“workbook”, “Sales.xls”)
21 xlcGotoRCRange
xlcObj.xlcGotoRCRange(Top, Left, Bottom, Right)
Purpose:
Go to a rectangular range on the active worksheet, as defined by row and column numbers. The range may be a single cell. Going to a range selects that range within the active worksheet.
Top, Left, Bottom, and Right are the row and column numbers which define the boundaries of the range.
Returns:
0 on success
-1004 range not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The example selects the range C4:E12
RetCode = xlcObj.xlcGotoRCRange(4, 3, 12, 5)
22 xlcImportTextFile
xlcObj.xlcImportTextFile(TextFilePath [, RangeName] [, Delimiter] [, Shift])
Purpose:
This is a powerful function which inserts the contents of a delimited text file into the active worksheet at the location specified by RangeName. Any values that Excel recognises are permitted, including text, numbers and dates. The calling application will normally create the text file in the required format prior to calling this function. For more information about text data sources used by ExcelController see section "Excel Templates and Data Sources" below.
A new workbook may be created and activated prior to calling this function with function xlcCreateNewWorkbook. An existing workbook can be opened and activated using xlcOpenWorkbook.
The function leaves the worksheet open with the data imported selected. This allows the cells containing the data to be formatted easily with a subsequent call to xlcFormatTable.
The TextFilePath parameter is the path of the file containing the data to be imported into the worksheet.
RangeName (optional, default "") specifies the area of the worksheet in which the data is to be placed. Data is placed starting at the top left hand cell of the specified range. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "Bottom" (data is placed below all other data)
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
Delimiter (optional, default ",") specifies the character which separates the column values in the data source text file. Normally a comma "," should be used (this is the default). Commas contained in data will not normally cause problems if the data file is constructed as specified in section "Excel Templates and Data Sources" below.
Shift (optional, default "") specifies whether or not to shift rows or columns down or to the right to make way for the imported data, or to simply overwrite any existing cell values (the default) Valid values are:
• "Down" - rows below the insertion point are shifted down
• "Right" - columns to the right of the insertion point are shifted to the right
• "" - inserted data overwrites existing cell values
The Shift parameter is especially useful if there are cells containing totals or other formulae in the worksheet. Data rows and columns can be inserted while maintaining the validity of the formulae.
Returns:
The number of rows of data imported, or an error code
0 on success
-91 if command unavailable
-1004 if range not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
-9085 if TextFilePath not found
-9086 if TextFilePath contains no data
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example inserts the contents of the text file "c:\data\mydata.txt” starting at cell A5. Rows below row 5 are shifted down to make way for the new data.
DataSource = “c:\data\mydata.txt”
RetCode = xlcObj.xlcImportTextFile(DataSource, “A5”, , “Down”)
23 xlcInsertChartAsNewSheet
xlcObj.xlcInsertChartAsNewSheet(Gallery [, SourceRange] [, ChartFormat] [, PlotBy] [, CategoryLabels] [, SeriesLabels] [, HasLegend] [, Title] [, CategoryTitle] [, ValueTitle] [, ExtraTitle])
Purpose:
This powerful function takes the data in a range on the active worksheet and creates a chart on a new sheet. The new sheet is inserted before the active sheet (it can be moved and/or renamed with functions xlcCopyOrMoveSheet and xlcRenameSheet). The function uses the Excel ChartWizard feature. A wide variety of different chart styles and formats is available.
The Gallery parameter determines the style of the chart. Valid values are:
1 Area
2 Bar
3 Column
4 Line
5 Pie
-4151 Radar
-4169 XYScatter
-4111 Combination
-4098 3DArea
-4099 3DBar
-4100 3DColumn
-4101 3DLine
-4102 3DPie
-4103 3DSurface
-4120 Doughnut
SourceRange (optional, default "") specifies the area of the worksheet containg the chart data. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on specifying range names see "Specifying Excel Ranges" below. Note that the ability to select combination ranges (of the form "A1:B7,F1:H7") is particularly useful when creating charts.
ChartFormat (optional, default 1) determines the format of the particular chart style defined by Gallery. Valid values are between 1 and 10 and depend on the value of Gallery. To learn more about the different formats available for a chart style it is a good idea to experiment with the Excel ChartWizard. Use Insert...Chart from the main Excel menu. If in doubt use a value of 1.
PlotBy (optional, default 1) specifies whether the data for each series is in rows or columns. Valid values are:
1 rows
2 columns
CategoryLabels (optional, default 0) specifies the number of rows or columns within the source range containing category labels. Legal values are from zero to one less than the maximum number of the corresponding categories or series.
SeriesLabels (optional, default 0) specifies the number of rows or columns within the source range containing series labels. Legal values are from zero to one less than the maximum number of the corresponding categories or series.
HasLegend (optional, default True) specifies whether or not to include a chart legend. Valid values are:
False no legend
True legend
Title (optional, default "") defines the title for the chart (optional).
CategoryTitle (optional, default "") defines the title for the category (x) axis.
ValueTitle (optional, default "") defines the title for the value (y) axis.
ExtraTitle (optional, default "") defines the series axis title for 3-D charts, or the second value axis title for 2-D charts.
See also xlcInsertChartOnSheet.
Returns:
0 on success
-91 if command unavailable
-1004 if range not found or chartwizard method fails
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example creates a 3DBar chart representation of the data contained in range A1 to F20.
RetCode = xlcObj.xlcInsertChartAsNewSheet(-4099, "A1:F20",
1, 2, 1, 0, True, "chart title", "x-axis title", "y-axis title")
24 xlcInsertChartOnSheet
xlcObj.xlcInsertChartOnSheet(Left, Top, Width, Height, Gallery [, SourceRange] [, ChartFormat] [, PlotBy] [, CategoryLabels] [, SeriesLabels] [, HasLegend] [, Title] [, CategoryTitle] [, ValueTitle] [, ExtraTitle])
Purpose:
This powerful function takes the data in a range on the active worksheet and creates a chart representing that data in a defined position on the same worksheet. The function uses the Excel ChartWizard feature. A wide variety of different chart styles and formats is available.
The Left, Top, Width and Height parameters define the position of the chart in points (1/72th of an inch) relative to the top left of cell A1.
SourceRange (optional, default "") specifies the area of the worksheet containg the chart data. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on specifying range names see "Specifying Excel Ranges" below. Note that the ability to select combination ranges (of the form "A1:B7,F1:H7") is particularly useful when creating charts.
The Gallery parameter determines the style of the chart. Valid values are:
1 Area
2 Bar
3 Column
4 Line
5 Pie
-4151 Radar
-4169 XYScatter
-4111 Combination
-4098 3DArea
-4099 3DBar
-4100 3DColumn
-4101 3DLine
-4102 3DPie
-4103 3DSurface
-4120 Doughnut
ChartFormat (optional, default 1) determines the format of the particular chart style defined by Gallery. Valid values are between 1 and 10 and depend on the value of Gallery. To learn more about the different formats available for a chart style it is a good idea to experiment with the Excel ChartWizard. Use Insert...Chart from the main Excel menu. If in doubt use a value of 1.
PlotBy (optional, default 1) specifies whether the data for each series is in rows or columns. Valid values are:
1 rows
2 columns
CategoryLabels (optional, default 0) specifies the number of rows or columns within the source range containing category labels. Legal values are from zero to one less than the maximum number of the corresponding categories or series.
SeriesLabels (optional, default 0) specifies the number of rows or columns within the source range containing series labels. Legal values are from zero to one less than the maximum number of the corresponding categories or series.
HasLegend (optional, default True) specifies whether or not to include a chart legend. Valid values are:
False no legend
True legend
Title (optional, default "") defines the title for the chart (optional).
CategoryTitle (optional, default "") defines the title for the category (x) axis.
ValueTitle (optional, default "") defines the title for the value (y) axis.
ExtraTitle (optional, default "") defines the series axis title for 3-D charts, or the second value axis title for 2-D charts.
See also xlcInsertChartAsNewSheet.
Returns:
0 on success
-91 if command unavailable
-1004 if range not found or chartwizard method fails
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example creates a 3DBar chart representation of the data contained in range A1 to F20.
RetCode = xlcObj.xlcInsertChartOnSheet(300, 100, 200, 150, -4099, "A1:F20",
1, 2, 1, 0, True, "chart title", "x-axis title", "y-axis title")
25 xlcInsertPageBreak
xlcObj.xlcInsertPageBreak([HorizontalOrVertical] [, RangeName])
Purpose:
Insert a page break in the active worksheet above or to the left of the specified range.
HorizontalOrVertical (optional, default "Horizontal") indicates whether you wish the page break to go above or to the left of the range specified by the RangeName parameter. Valid values are:
• "Horizontal" (default)
• "Vertical"
RangeName (optional, default "") specifies the area of the worksheet next at which the page break is to be inserted. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
Returns:
0 on success
-91 if command unavailable
-1004 unable to insert page break (for example if specified position not valid or no worksheet open)
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The first example inserts a page break immediately to the left of the current selection. The second inserts a page break immediately above row 7.
RetCode = xlcObj.xlcInsertPageBreak("Vertical")
RetCode = xlcObj.xlcInsertPageBreak(, "A7”)
26 xlcInsertPictureAtRange
xlcObj.xlcInsertPictureAtRange(PictureFileName [, RangeName] [, SizeToRange])
Purpose:
Inserts a graphic from a file at the specified location. Various picture file types are supported, such as BMP, JPG, GIF etc., but the exact list will vary depending on what file converters are installed for Excel to use.
The PictureFileName parameter specifies the location of the graphic file.
RangeName (optional, default "") specifies the area where the picture is to be placed. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
The SizeToRange (optional, default True) parameter indicates whether or not you wish to resize the picture to fit the range. SizeToRange has values as follows:
False do not resize the picture
True resize the picture
If you choose False for the SizeToRange parameter the picture will be positioned so that its top left corner is at the top left corner of the first cell in the range. With a range value of C1:D3 the picture would be placed starting at the top left of cell C1.
If you choose True for this parameter the picture will be positioned so that fits the range exactly. With a range value of C1:D3 the picture would be so that it covered all the cells in the C1:D3 range.
Returns:
0 on success
-91 if command unavailable
-1004 if range not found or file cannot be imported
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
This example places the graphic from mypic.bmp over the cells D5:F8, resizing it to fit this range exactly.
PicFile = “c:\data\mypic.bmp”
RetCode = xlcObj.xlcInsertPictureAtRange(PicFile, "D5:F8”)
This example imports the graphic from mypic.jpg without resizing it. It is positioned so that its top left corner is at the top left corner of the first cell in the current selection.
PicFile = “c:\data\mypic.bmp”
RetCode = xlcObj.xlcInsertPictureAtRange(PicFile, , False)
27 xlcInsertPivotTable
xlcObj.xlcInsertPivotTable([SourceRange] [, Destination] [, PivotTableName] [, RowFieldNames] [, ColumnFieldNames] [, DataFieldNames] [, PageFieldNames] [, NameDelimiter] [, ShowRowGrandTotals] [, ShowColGrandTotals])
Purpose:
Inserts a pivot table into the active workbook.
SourceRange (optional, default "") specifies the area of the worksheet containing the pivot table data. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on specifying range names see "Specifying Excel Ranges" below. Note that the ability to select combination ranges (of the form "A1:B7,F1:H7") is particularly useful when creating pivot tables.
Destination (optional, default "") specifies the range where the PivotTable should be positioned. A blank value places the table starting at the active cell.
PivotTableName (optional, default "") is a name to assign to the new pivot table.
RowFieldNames, ColumnFieldNames, DataFieldNames, PageFieldNames (all optional, default "") are the sets of fieldnames to be used as ColumnFields, RowFields, DataFields and PageFields. See Excel documentation for more information on what each type of field means. These parameters are delimited strings of 0, 1 or more names of fields. Blank strings for some of these parameters are allowed.
e.g. "Employee/Salary/Hours worked" is how you might specify three field names in one of these parameters. The delimiter, in this case "/" is as defined by NameDelimiter.
NameDelimiter (optional, default "/") is the string separator used in parameters RowFieldNames, ColumnFieldNames, DataFieldNames, PageFieldNames.
ShowRowGrandTotals (optional, default True) specifies whether or not to include Grand Totals for each row. Valid values are:
False do not include the grand totals for each row
True include the grand totals for each row
ShowColGrandTotals (optional, default True) specifies whether or not to include Grand Totals for each column. Valid values are:
False do not include the grand totals for each column
True include the grand totals for each column
Returns:
0 on success
-91 if command unavailable
-1004 if range not found or Excel PivotTableWizard method fails
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The following code inserts an unnamed pivot table at the current location based on data in the range A1:G20 on Sheet2, with fields as defined. Grand totals are included for rows and columns.
RetCode = xlcObj.xlcInsertPivotTable("Sheet2!A1:G20", , ,
"Commission/Tax", "Employee", "Week", "Earnings")
28 xlcInsertRowsOrColumns
xlcObj.xlcInsertRowsOrColumns(InsertType [, StartNumber] [, InsertNumber])
Purpose:
Inserts rows or columns in the active workbook.
InsertType specifies whether rows or columns are to be inserted. Valid values are:
• "Rows" or "Row"
• "Columns" or "Column"
StartNumber (optional, default 1) specifies the row or column number where the insert is to take place. The function will insert rows or columns before the one with the number specified, pushing other rows or columns down or to the right as appropriate.
InsertNumber (optional, default 1) specifies the number of rows or columns to be inserted.
Returns:
0 on success
-91 or –1004 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The first example inserts 2 new columns before column 3 (C) in the active worksheet. The second inserts one row at the top of the sheet
RetCode = xlcObj.xlcInsertRowsOrColumns("Columns”, 3, 2)
RetCode = xlcObj.xlcInsertRowsOrColumns("Rows”)
29 xlcInsertSheet
xlcObj.xlcInsertSheet([InsertPosition] [, NewSheetName])
Purpose:
Insert a new worksheet at the specified position in the active workbook. The sheet may be specified by a number or name.
InsertPosition (optional, default "") specifies where to insert the sheet. It may be a sheet number, a sheet name, or the special value "End". Passing a value of "End" to the function will cause the new sheet to be inserted as the last sheet of the workbook. With any other value the new sheet will be inserted at that position and any sheets after it will be shifted to the right. To place the new sheet as the first sheet, use a value of 1 for InsertPosition.
NewSheetName (optional, default "") specifies the name for the new sheet. If this is omitted ("") Excel will give the sheet a default name.
Returns:
0 on success
-91 if command unavailable
-1004 sheet not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
-9087 a sheet with the specified name already exists in the active workbook
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example inserts a new sheet, with default name, at the end of the workbook. The second inserts a new sheet named "New Sheet" before the third sheet in the workbook. The third example inserts a new sheet named "Costs" before the existing sheet named "Profits". The fourth inserts a new sheet with default name before the currently selected sheet.
RetCode = xlcObj.xlcInsertSheet("End”)
RetCode = xlcObj.xlcInsertSheet(3, “New Sheet”)
RetCode = xlcObj.xlcInsertSheet("Profits”, “Costs”)
RetCode = xlcObj.xlcInsertSheet
30 xlcInsertValue
xlcObj.xlcInsertValue(Value [, RangeName])
Purpose:
Inserts a value into a cell or range of cells. This is a general function - any value that Excel recognises is permitted, including text, numbers and dates. Formulae may also be inserted.
The Value parameter specifies the value to be inserted into each cell within the range. Excel will recognise various data-types (text, numbers, dates, etc) in valid formats and treat them appropriately. If you wish a number or date value to be inserted as text you should precede the value with a single quote, just as you would if you wanted to achieve the same thing in Excel itself. For example "'123.45" will be inserted as text, whereas "123.45" will be inserted as a number. If you wish to insert a formula, prefix it with "=". Thus "A1+A2" will be entered as a text string, but "=A1+A2" will be entered as a formula.
RangeName (optional, default "") specifies the area of the worksheet which is to be formatted. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
Returns:
0 on success
-91 or -1004 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
MyText = “Hello World”
RetCode = xlcObj.xlcInsertValue(MyText)
RetCode = xlcObj.xlcInsertValue(1)
RetCode = xlcObj.xlcInsertValue(1.01)
RetCode = xlcObj.xlcInsertValue(“01-Jan-2001”, “B8”)
RetCode = xlcObj.xlcInsertValue(“hello world”, “MyNamedRange”)
31 xlcLaunch
xlcObj.xlcLaunch([WindowState])
Purpose:
Launches Excel or obtains a reference to a running occurrence for use in subsequent ExcelController method calls. Excel is launched in, or if already running converted to, a show state as defined by the optional WindowState parameter.
This function or xlcLaunchNewInstance must be called prior to making use of most other ExcelController functions such as xlcOpenWorkbook and xlcReplaceAll.
Valid values for WindowState are as follows:
0 Hidden
1 Normal (default)
2 Minimized
3 Maximized
If any other value is passed, Normal is assumed.
See also xlcLaunchNewInstance.
Returns:
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
RetCode = xlcObj.xlcLaunch ‘normal state (default)
or
RetCode = xlcObj.xlcLaunch(3) ‘maximized
32 xlcLaunchNewInstance
xlcObj.xlcLaunchNewInstance([WindowState])
Purpose:
Launches a new instance of Excel for use in subsequent ExcelController method calls. Excel is launched in a show state as defined by the optional WindowState parameter.
This function or xlcLaunch must be called prior to making use of most other ExcelController functions such as xlcOpenWorkbook and xlcReplaceAll.
Excel Version Note: In some (later) versions of Excel a new instance will NOT be launched by this function if there is already an ‘unused’ instance running - one with no documents open. In Excel 95 and earlier, however, a new instance will always be launched by this function.
Valid values for WindowState are as follows:
0 Hidden
1 Normal (default)
2 Minimized
3 Maximized
If any other value is passed, Normal is assumed.
See also xlcLaunch.
Returns:
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
RetCode = xlcObj.xlcLaunchNewInstance ‘normal state (default)
or
RetCode = xlcObj.xlcLaunchNewInstance(0) ‘hidden
33 xlcMergeCells
xlcObj.xlcMergeCells([RangeName])
Purpose:
Merges a range of cells within the active worksheet into a single cell. Data may be lost if the cells contain separate values (the merged cell will retain the data which was in the cell at the top left of the range).
Excel Version Note: This function is not available in Excel 95 and earlier. If ExcelController encounters one of these earlier versions at runtime it will return the error -9005.
RangeName (optional, default "") specifies the cells to merge. Valid values for RangeName are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
Returns:
0 on success
-91 or -1004 if command unavailable or range not found
-9001 if Excel is not launched
-9005 function not supported in this Excel version (Excel 95 and earlier)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example merges the cells B1 to B5 into a single cell.
RetCode = xlcObj.xlcMergeCells(“B1:B5”)
34 xlcOpenWorkbook
xlcObj.xlcOpenWorkbook(WorkbookName [, ReadOnly] [, Password])
Purpose:
Opens an Excel workbook.
WorkbookName specifies the file path of the workbook to open.
ReadOnly (optional, default False) specifies whether or not to open the workbook in read-only mode
False normal editing mode
True read only mode
Password (optional, default "") specifies the password required to unprotect the workbook if there is one.
Returns:
0 on success
-1004 if the workbook was not found or the specified password is incorrect
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
MyFile = “c:\data\myfile.xls”
RetCode = xlcObj.xlcOpenWorkbook(MyFile)
RetCode = xlcObj.xlcOpenWorkbook(MyFile, True) ‘read-only
RetCode = xlcObj.xlcOpenWorkbook(MyFile, , “mypass”) ‘password required
35 xlcPasteRange
xlcObj.xlcPasteRange([DestinationRange])
Purpose:
Copies the contents of the clipboard to a range within the active worksheet. May be used in conjunction with xlcCopyRange.
DestinationRange (optional, default "") specifies the area to copy the clipboard contents into. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
See also xlcCopyRange, xlcPasteSpecialRange.
Returns:
0 on success
-91 if command unavailable
-1004 if the paste operation is invalid
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example copies the contents of the clipboard into the range B5 to AA12. The second copies it into the currently selected range.
RetCode = xlcObj.xlcPasteRange(“B5:AA12”)
RetCode = xlcObj.xlcPasteRange
36 xlcPasteSpecialRange
xlcObj.xlcPasteSpecialRange([DestinationRange] [, PasteType] [, SpecialOperation] [, IgnoreBlanks])
Purpose:
Copies the contents of the clipboard to a range within the active worksheet. Similar to xlcPasteRange but gives more control over exactly what is pasted. Normally will be used in conjunction with xlcCopyRange if it is necessary to copy values, formulae or formatting between areas in a worksheet or across worksheets.
Whereas xlcPasteRange will copy the entire contents of cells, including formulae, formatting and comments, this function allows you to choose exactly what you wish to paste, through the PasteType parameter.
DestinationRange (optional, default "") specifies the area to copy the clipboard contents into. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
The PasteType parameter indicates how you want the paste operation to behave (what you want to be pasted). Valid values are:
• "Value" or "Values"
• "Format" or "Formats"
• "Formula" or "Formulas" or "Formulae"
• "Comment" or "Comments"
• "All"
• "AllExceptBorders"
SpecialOperation (optional, default "") is used to indicate that you wish a mathematical operation to be carried out during the paste operation. Valid values are:
• "Add" (values in source cells are added to the values in the destination cells)
• "Subtract"(values in source cells are subtracted from values in the destination cells)
• "Multiply" (values in destination cells are multiplied by the values in the source cells)
• "Divide" (values in destination cells are divided by the values in the source cells)
• "" (default, perform no mathematical operation)
IgnoreBlanks (optional, default False) indicates whether or not you wish blanks values found in the clipboard source range to be included in the paste operation. Valid values are as follows:
False paste blanks found in the source range, as well as non-blanks
True ignore (do not paste) any blanks found in the source range
See also xlcPasteRange, xlcCopyRange.
Returns:
0 on success
-91 if command unavailable
-1004 if the paste operation is invalid
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example copies the contents of the clipboard into the range B5 to AA12. The "Values" parameter indicates that the paste does not paste formulae. Any calculated cells in the source range are pasted as values.
RetCode = xlcObj.xlcPasteSpecialRange(“B5:AA12”, “Values”)
The second example copies the formatting from the range in the clipboard into the currently selected range, ignoring any blank cells in the source range.
RetCode = xlcObj.xlcPasteSpecialRange(, “Formats”, , True)
The third example adds the values in the cells in the clipboard source range into the cells in the currently selected range.
RetCode = xlcObj.xlcPasteSpecialRange(, “All”, “Add”)
37 xlcPrintRange
xlcObj.xlcPrintRange([RangeName] [, NumberOfCopies] [, Portrait])
Purpose:
Prints a specified range within the active worksheet.
RangeName (optional, default "") specifies the area to print. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
NumberOfCopies (optional, default 1) specifies the number of copies of the print to produce.
Portrait (optional, default True) indicates whether to print in portrait or landscape page layout. Valid values are:
True portrait orientation
False landscape orientation
See also xlcPrintSheet, xlcPrintWorkbook, xlcSetRangeName
Returns:
0 on success
-91 if command unavailable
-1004 range not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example prints one copy of the current selection to the default printer in landscape mode. The second prints three copies of range A5:C12 in portrait mode.
RetCode = xlcObj.xlcPrintRange(, , False)
RetCode = xlcObj.xlcPrintRange(“A5:C12”, 3)
38 xlcPrintSheet
xlcObj.xlcPrintSheet([Sheet] [, NumberOfCopies])
Purpose:
Prints a specified worksheet within the active workbook. The sheet may be specified by a number or name.
Sheet (optional, default "") may be a sheet number, a sheet name or blank. A blank value will cause the function to print the currently active worksheet.
NumberOfCopies (optional, default 1) specifies the number of copies of the print to produce.
See also xlcPrintRange, xlcPrintWorkbook, xlcSetRangeName
Returns:
0 on success
-91 if command unavailable
-1004 or -9 if sheet not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example prints one copy of the active worksheet to the default printer. The second prints three copies of sheet number two. The third prints one copy of the sheet named "Costs".
RetCode = xlcObj.xlcPrintSheet
RetCode = xlcObj.xlcPrintSheet(2, 3)
RetCode = xlcObj.xlcPrintSheet(“Costs”)
39 xlcPrintWorkbook
xlcObj.xlcPrintWorkbook([NumberOfCopies])
Purpose:
Prints the entire active workbook (all sheets).
NumberOfCopies (optional, default 1) specifies the number of copies of the print to produce.
See also xlcPrintSheet, xlcPrintRange, xlcSetRangeName
Returns:
0 on success
-91 if command unavailable
-1004 workbook not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example prints one copy of the active workbook to the default printer.
RetCode = xlcObj.xlcPrintWorkbook
40 xlcProtectWorkbook
xlcObj.xlcProtectWorkbook([Password])
Purpose:
Protects the active workbook against structural change. When a workbook is protected the user cannot add, delete, rename or hide sheets.
Password (optional, default "") specifies the password required to subsequently unprotect the workbook. Password may be blank ("").
See also xlcUnprotectWorkbook, xlcProtectWorksheet, xlcUnprotectWorksheet, xlcSetCellProtection.
Returns:
0 on success
-91 if command unavailable
-1004 workbook not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example protects the workbook with no password, so any user can unprotect it if they wish. The second example protects the workbook with password "ABC1".
RetCode = xlcObj.xlcProtectWorkbook
RetCode = xlcObj.xlcProtectWorkbook(“ABC1”)
41 xlcProtectWorksheet
xlcObj.xlcProtectWorksheet([Password] [,Objects] [,Contents] [, Scenarios])
Purpose:
Protects the active worksheet against various types of change. Normally used in conjunction with xlcSetCellProtection to stop the contents of cells being changed..
Password (optional, default "") specifies the password required to subsequently unprotect the worksheet. Password may be blank ("").
Objects (optional, default True) indicates whether to protect the drawing objects within the worksheet. Only cells which have their Locked property set will be affected. The Locked property can be set with xlcSetCellProtection. Valid values are:
False Do not protect drawing objects
True Protect drawing objects
Contents (optional, default True) indicates whether to protect the contents of the individual cells within the worksheet. Valid values for the Contents parameter are:
False Do not protect cell contents
True Protect cell contents
Scenarios (optional, default True) indicates whether to protect the scenarios within the worksheet. Valid values are:
False Do not protect scenarios
True Protect scenarios
See also xlcUnprotectWorksheet, xlcSetCellProtection, xlcProtectWorkbook, xlcUnprotectWorkbook.
Returns:
0 on success
-91 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example protects the worksheet object, contents and scenarios but without a password, so any user can unprotect it if they wish.
RetCode = xlcObj.xlcProtectWorksheet
The second example protects the worksheet cell contents with password "ABC1". Drawing objects and scenarios are not protected.
RetCode = xlcObj.xlcProtectWorksheet(“ABC1”, False, True, False)
42 xlcRenameSheet
xlcObj.xlcRenameSheet(NewSheetName [, Sheet])
Purpose:
Rename a specified sheet in the active workbook. The sheet may be specified by a number or name.
NewSheetName specifies the name for the new sheet.
Sheet (optional, default "") may be a sheet number, a sheet name or blank. A blank value will cause the function to rename the currently active worksheet.
Returns:
0 on success
-91 if command unavailable
-1004 or –9 if sheet not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
-9087 a sheet with the specified new name already exists in the active workbook
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example renames the sheet named "Sheet3" to "3rd Sheet". The second renames the active sheet to "Active". The third renames the fourth sheet to "Fourth".
RetCode = xlcObj.xlcRenameSheet(“3rd Sheet”, “Sheet3”)
RetCode = xlcObj.xlcRenameSheet(“Active”)
RetCode = xlcObj.xlcRenameSheet(“Fourth”, 4)
43 xlcReplaceAll
xlcObj.xlcReplaceAll(StringToReplace, ReplacementString [, HeaderAndFooter] [, MatchWholeCell] [, MatchCase])
Purpose:
Replaces all occurrences of a string in the active sheet of an open workbook with another string. This function is particularly useful when creating template based workbooks – placeholder values can be replaced at run time with real data.
StringToReplace specifies the string to search for in the worksheet. ReplacementString specifies the string to replace it with.
HeaderAndFooter (optional, default False) specifies whether or not the replace operation should be carried out in the sheet header and footer as well as the main body of the document.
False do not replace in the header and footer
True do replace in the header and footer
MatchWholeCell (optional, default False) specifies whether or not the replace operation should only replace in cells where the string contained in the cell exactly matches the string to replace.
False replace even in cells where the string to replace is just part of the cell
True only replace where there is an exact match
MatchCase (optional, default False) specifies whether or not the replace operation should be case sensitive.
False replace is not case sensitive
True replace is case sensitive
Returns:
0 on success
-91 if ReplaceAll is not a valid command (usually because no workbook is open)
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example replaces all occurrence of the string “” with “Jones, Bill”. Headers and Footers are included in the operation.
Set CustomerName = “Jones, Bill”
RetCode = xlcObj.xlcReplaceAll(“”, CustomerName, True)
44 xlcRunMacro
xlcObj.xlcRunMacro(MacroName [, NumArguments] [, Arg1] [, Arg2] [, Arg3] [, Arg4] [, Arg5])
Purpose:
Runs an Excel macro. Often used to run macro code contained in a template after populating a new workbook (created from the template) with data.
MacroName is the name of the Excel macro to be run.
NumArguments (optional, default 0) is the number of parameters required by the macro. A value must be supplied for all parameters. This value must be an integer between 0 and 5 - a maximum of 5 parameters are supported.
Arg1...Arg5 (optional) are the parameter values if required.
Returns:
0 on success
-91 if the macro command is unavailable
-449 if a mandatory argument is not supplied.
-450 or 1003 if NumArguments is an invalid number of arguments for MacroName
-1004 if macro MacroName is not found
-9001 if Excel is not launched
-9004 invalid parameter value(s)
-9083 if NumArguments > 5 or < 0
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
RetCode = xlcObj.xlcRunMacro(“Macro1”)
RetCode = xlcObj.xlcRunMacro(“Macro2”, 1, “Sales”)
45 xlcSaveWorkbook
xlcObj.xlcSaveWorkbook([SaveAsFilePath] [, Password])
Purpose:
Saves the active workbook to disk.
SaveAsFilePath (optional, default "") specifies the complete path in which to save the file.
This function operates both as a Save and a SaveAs function depending on the parameter values:
• If SaveAsFilePath is non-blank then the workbook is saved (SaveAs) to the specified location. Note that before saving SaveAsFilePath is tested for existence and an error is returned if an existing file with the same name is found (it is the developer’s responsibility to identify and delete old versions if desired before calling this function).
• If SaveAsFilePath is blank then the workbook is saved (Save) either to its current file, if it already exists on disk, or to a default filename and location.
If Password (optional, default "") is specified then the saved workbook will be password protected with the supplied string. Password is ignored if SaveAsFilePath is blank.
See also xlcSaveWorkbookAsType, xlcSaveWorksheetAsType.
Returns:
0 on success
-68 device unavailable
-76 path not found
-91 if command unavailable
-1004 is usually a file access error - more information available in detailed error message, obtainable with ErrorText
-9001 if Excel is not launched
-9004 invalid parameter value(s)
-9088 if file specified by SaveAsFilePath already exists
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example saves the currently active workbook. If it has been previously saved, or opened from disk, then it will be saved to its current location. If not, it will be saved with an Excel default name to an Excel default location.
RetCode = xlcObj.xlcSaveWorkbook
The second example saves the active workbook to the file location specified. It will be protected with password "mypass".
RetCode = xlcObj.xlcSaveWorkbook(“NewName.xls”, “mypass”)
46 xlcSaveWorkbookAsType
xlcObj.xlcSaveWorkbookAsType(OutputFileFormat [, SaveAsFilePath] [, Password] [, Overwrite])
Purpose:
Saves the active workbook to disk in one of a variety of file formats.
The OutputFileFormat parameter determines what type of file should be created. Some file formats such as TEXT and CSV do not support multiple sheets - only the data on the active sheet will be written to the file. Valid values are:
"CSV"
"DIF"
"EXCEL5"
"EXCEL7"
"EXCEL9795"
"HTM" or "HTML" (Excel97 and later versions only)
"TXT" or "TEXT"
"XLT" or "TEMPLATE"
SaveAsFilePath (optional, default "") specifies the complete path in which to save the file.
• If SaveAsFilePath is non-blank then the workbook is saved to the specified location.
• If SaveAsFilePath is blank then the workbook is saved either to its current file, if it already exists on disk, or to a default filename and location.
If Password (optional, default "") is specified then the saved file will be password protected with the supplied string. Password is ignored if the specified file format does not support password protection.
Overwrite (optional, default False) determines whether any existing file should be overwritten. If Overwrite is False then SaveAsFilePath is tested for existence and an error is returned if an existing file with the same name is found. Valid values are:
False Do not overwrite
True Overwrite
See also xlcSaveWorksheetAsType, xlcSaveWorkbook.
Returns:
0 on success
-68 device unavailable
-70 permission denied
-76 path not found
-91 if command unavailable
-1004 is usually a file access error - more information available in detailed error message, obtainable with ErrorText
-9001 if Excel is not launched
-9004 invalid parameter value(s)
-9005 format not supported in this Excel version.
-9088 if file specified by SaveAsFilePath already exists and Overwrite is false
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example saves the currently active workbook to a CSV file, overwriting if a file with the same name already exists.
RetCode = xlcObj.xlcSaveWorkbookAsType(“CSV”, “c:\myfile.csv”, , True)
47 xlcSaveWorksheetAsType
xlcObj.xlcSaveWorksheetAsType(OutputFileFormat [, SaveAsFilePath] [, Password] [, Overwrite])
Purpose:
Saves the active worksheet to disk in one of a variety of file formats.
The OutputFileFormat parameter determines what type of file should be created. Valid values are:
"CSV"
"DIF"
"EXCEL5"
"EXCEL7"
"EXCEL9795"
"HTM" or "HTML" (Excel97 and later versions only)
"TXT" or "TEXT"
"XLT" or "TEMPLATE"
SaveAsFilePath (optional, default "") specifies the complete path in which to save the file.
• If SaveAsFilePath is non-blank then the worksheet is saved to the specified location.
• If SaveAsFilePath is blank then the worksheet is saved either to its current file, if it already exists on disk, or to a default filename and location.
If Password is specified then the saved file will be password protected with the supplied string. Password is ignored if the specified file format does not support password protection.
The Overwrite (optional, default False) parameter determines whether any existing file should be overwritten. If Overwrite is False then SaveAsFilePath is tested for existence and an error is returned if an existing file with the same name is found. Valid values are:
False Do not overwrite
True Overwrite
See also xlcSaveWorkbookAsType, xlcSaveWorkbook.
Returns:
0 on success
-68 device unavailable
-76 path not found
-91 if command unavailable
-1004 is usually a file access error - more information available in detailed error message, obtainable with ErrorText
-9001 if Excel is not launched
-9004 invalid parameter value(s)
-9005 format not supported in this Excel version.
-9088 if file specified by SaveAsFilePath already exists and Overwrite is false
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example saves the currently active worksheet in Excel 5 format, with password "mypass", but without overwriting. If a file with the same name already exists an error (-9088) will be returned.
RetCode = xlcObj.xlcSaveWorkbookAsType(“EXCEL5”, “c:\myfile.xls”,
“mypass”, True)
48 xlcSetAlignment
xlcObj.xlcSetAlignment([RangeName] [, HorizontalAlignment] [, IndentLevel] [, VerticalAlignment] [, WrapText])
Purpose:
Sets the font properties for the specified range within the active worksheet. These properties relate to those found in the Excel Format…Cells…Alignment dialog.
RangeName (optional, default "") specifies the area of the worksheet which is to be formatted. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
HorizontalAlignment (optional, default 1) specifies how the text is to be aligned horizontally. Valid values are:
1 General
5 Fill
7 Center across selection
-4108 Center
-4130 Justify
-4131 Left
-4152 Right
IndentLevel (optional, default 0) specifies the indent level to be used in combination with Left HorizontalAlignment (-4131). Valid values are 0 to 15.
VerticalAlignment (optional, default -4160) specifies how the text is to be aligned vertically. Valid values are:
-4107 Bottom
-4108 Center
-4130 Justify
-4160 Top
WrapText (optional, default False) indicates whether or not to apply the WrapText property to the selection. Valid values are:
False Do not wrap
True Wrap
See also xlcFormatRange, xlcSetFont, xlcSetNumberFormat.
Returns:
0 on success
-91 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example sets the alignment of the current selection to be centred vertically and horizontally, with wrapping text.
RetCode = xlcObj.xlcSetAlignment(, -4108, 0, -4108, True)
49 xlcSetAutocalculateOff
xlcObj.xlcSetAutocalculateOff
Purpose:
Puts Excel into manual calculation mode, usually for performance reasons. Automatic calculation is the normal mode for most Excel users. However, if you wish to populate a workbook with a lot of related data then the most efficient way will normally be to set autocalculate off at the start of the operation, load the data, and then set autocalculate back on again.
See also xlcSetAutocalculateOn.
Returns:
0 on success
-1004 if Excel cannot set the property (this usually means that no workbook is open)
-9001 if Excel not launched
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
RetCode = xlcObj.xlcSetAutocalculateOff
50 xlcSetAutocalculateOn
xlcObj.xlcSetAutocalculateOn
Purpose:
Puts Excel into automatic calculation mode, the normal mode for most Excel users.
See also xlcSetAutocalculateOff.
Returns:
0 on success
-1004 if Excel cannot set the property (this usually means that no workbook is open)
-9001 if Excel not launched
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
RetCode = xlcObj.xlcSetAutocalculateOn
51 xlcSetBackgroundColor
xlcObj.xlcSetBackgroundColor(ColorIndex [, RangeName])
Purpose:
Sets the background colour or shading for the specified range within the active worksheet using a colour from the colour palette.
ColorIndex determines what colour to apply to the background. This is an index into the current colour palette. For more information on applicable values see Excel help on the ColorIndex property.
RangeName (optional, default "") specifies the area of the worksheet which is to be formatted. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
See also xlcSetBackgroundColorRGB.
Returns:
0 on success
-91 if command unavailable
-1004 if range not found or ColorIndex not found
-9001 if Excel not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The example sets the background colour of cells A5 to A20 to colour 6 on the colour palette.
RetCode = xlcObj.xlcSetBackgroundColor(6, “A5:A20”)
52 xlcSetBackgroundColorRGB
xlcObj.xlcSetBackgroundColorRGB(RGBColor [, RangeName])
Purpose:
Sets the background colour or shading for the specified range within the active worksheet using a colour specified in terms of a Red/Green/Blue (RGB) colour value.
.
RGBColor determines what colour to apply to the background. This is a long integer equivalent to the result of the Visual Basic RGB function.
Tip: To find the RGB value for a desired colour, apply that colour to the background of a selected cell in Excel. Then enter and run the following Excel macro.
Sub ColorTest()
MsgBox Selection.Interior.Color
End Sub
RangeName (optional, default "") specifies the area of the worksheet which is to be formatted. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
See also xlcSetBackgroundColor.
Returns:
0 on success
-91 if command unavailable
-1004 if range not found
-9001 if Excel not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The example sets the background colour of cells A5 to A20 to Red which has an RGB value of 255.
RetCode = xlcObj.xlcSetBackgroundColorRGB(255, “A5:A20”)
53 xlcSetCellProtection
xlcObj.xlcSetCellProtection([RangeName] [, Locked] [, Hidden])
Purpose:
Sets the Locked and/or Hidden properties of cells in a specified range.
Note: These properties only take effect when the parent worksheet is protected. This may be done with a subsequent call to xlcProtectWorksheet.
RangeName (optional, default "") specifies the area of the worksheet which contains the cells to be operated on. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
Locked (optional, default False) indiciates whether to set or unset the Locked property. Valid values are:
False The cells are unlocked
True The cells are locked
Hidden (optional, default False) indiciates whether to set or unset the Hidden property. This is not the same as visibly hiding the cells, it merely means that the user will not be able to view the formulae from which the cells are derived. Valid values are:
False The formulae behind the cells are not hidden
True The formulae behind the cells are hidden
See also xlcProtectWorksheet, xlcUnprotectWorksheet, xlcProtectWorkbook, xlcUnprotectWorkbook.
Returns:
0 on success
-91 or -1004 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The first example sets the Locked and Hidden properties for all the cells in the currently selected range. If a subsequent call to protect the worksheet contents is made with xlcProtectWorksheet then the formulae for the cells in this range will not be visible and their values will not be editable.
RetCode = xlcObj.xlcSetCellProtection(, True, True)
The second example unlocks and unhides all the cells in a named range "TableData". If all other cells are protected (locked) and a subsequent call to protect the worksheet contents is made with xlcProtectWorksheet then only the cells in this range will be editable.
RetCode = xlcObj.xlcSetCellProtection(“TableData”)
54 xlcSetFont
xlcObj.xlcSetFont([RangeName] [, FontName] [, FontSize] [, Bold] [, Italic] [, Underline] [, Strikethrough] [, FontColor])
Purpose:
Sets the font properties for the specified range within the active worksheet.
RangeName (optional, default "") specifies the area of the worksheet which is to be formatted. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
FontName (optional, default "") is the name of the font to apply to the range e.g. "Arial". A value of "" indicates that the font should be unchanged.
FontSize (optional, default -1) is the desired size of the font in points. A value of -1 indicates that the font size should be unchanged.
Bold (optional, default -1) indicates whether or not to apply the Bold property to the font, if applicable. Valid values are:
-1 Leave the Bold property unchanged
0 Not Bold
1 Bold
Italic (optional, default -1) indicates whether or not to apply the Italic property to the font, if applicable. Valid values are:
-1 Leave the Italic property unchanged
0 Not Italic
1 Italic
Underline (optional, default -1) determines what kind of underlining to apply to the font. Valid values are:
-1 Leave the underline property unchanged
0 No underlining
1 Single underlining
2 Double underlining
3 Single Accounting style underlining
4 Double Accounting style underlining
Strikethrough (optional, default -1) indicates whether or not to apply the Strikethrough property to the font, if applicable. Valid values are:
-1 Leave the Strikethrough property unchanged
0 Not Strikethrough
1 Strikethrough
FontColor (optional, default -1) determines what colour to apply to the font. This is a long integer equivalent to the result of the Visual Basic RGB function. A value of -1 indicates that the font colour should be unchanged.
Tip: To find the RGB value for a desired colour, apply that colour to the font of a selected cell in Excel. Then enter and run the following Excel macro.
Sub ColorTest()
MsgBox Selection.Font.Color
End Sub
See also xlcFormatRange, xlcSetAlignment, xlcSetNumberFormat.
Returns:
0 on success
-91 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
This example sets the font of the current selection to Arial, Bold, 16 points, Red. 255 is the RGB value for Red.
RetCode = xlcObj.xlcSetFont(, ”Arial”, 16, 1, , , , 255)
This example sets the current selection to bold but leaves all other font properties unchanged.
RetCode = xlcObj.xlcSetFont(, , , 1)
55 xlcSetNumberFormat
xlcObj.xlcSetNumberFormat(FormatString [, RangeName])
Purpose:
Sets the number format property for the specified range within the active worksheet.
FormatString is the format string to apply. See Excel documentation for details of valid number format strings.
Tip: To find the FormatString value for a desired number format, apply that format to a selected cell in Excel. Then enter and run the following Excel macro.
Sub FormatTest()
MsgBox Selection.NumberFormat
End Sub
RangeName (optional, default "") specifies the area of the worksheet which is to be formatted. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
See also xlcFormatRange.
Returns:
0 on success
-91 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
This example sets the number format of the current selection to scientific format.
RetCode = xlcObj.xlcSetNumberFormat(”0.00E+00”)
This example sets the number format of the current selection to 2 decimal places.
RetCode = xlcObj.xlcSetNumberFormat(”0. 00”)
This example sets the number format of cells B8, B9, C8 and C9 to medium date.
RetCode = xlcObj.xlcSetNumberFormat(”dd-mmm-yy”, “B8:C9”)
56 xlcSetRangeName
xlcObj.xlcSetRangeName(RangeName, LeftColumn, TopRow, RightColumn, BottomRow)
Purpose:
Assigns a name to a rectangular range specified in terms of the boundary row and column numbers. Similar to Insert…Name…Define on the Excel menu. Named ranges can be used as parameters in many other ExcelController functions.
RangeName specifies the name that will be assigned to the range.
LeftColumn specifies the number of the column at the left hand side of the range. Column A would have a value of 1, column B would be 2, etc.
TopRow specifies the number of the row at the top of the range. Row 1 would have a value of 1, row 2 would be 2, etc.
RightColumn specifies the number of the column at the right hand side of the range. Column A would have a value of 1, column B would be 2, etc.
BottomRow specifies the number of the row at the bottom of the range. Row 1 would have a value of 1, row 2 would be 2, etc.
Returns:
0 on success
-91 or -1004 if command unavailable
-450 wrong number of arguments or invalid property assignment. This error can occur if the rangename has spaces or invalid characters.
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The first example sets the range name of range A5:C7 to "Myrange".
RetCode = xlcObj.xlcSetRangeName(”Myrange”, 1, 5, 3, 7)
The second example sets the range name of range A5:C7 to "Print_Area". This is an Excel reserved name. Subsequent requests to print will by default print this range.
RetCode = xlcObj.xlcSetRangeName(”Print_Area”, 1, 5, 3, 7)
57 xlcSetRowOrColumnSize
xlcObj.xlcSetRowOrColumnSize(ObjectType, StartNumber, NumberOfRowsOrColumns, NewSize)
Purpose:
Sets the height of a range of rows or the width of a range of columns in the active workbook. Optionally applies Excel's Autofit feature to a range of rows or columns.
ObjectType specifies whether to operate on rows or columns. Valid values are:
• "Rows" or "Row"
• "Columns" or "Column"
The StartNumber parameter specifies the number of the first row or column to be operated on.
The NumberOfRowsOrColumns parameter specifies the number of rows or columns to be operated on.
The NewSize parameter indicates the required height of the specified rows in points or the required width of the specified columns in width units. A column width unit is equivalent to the width of one character in the normal style.
Entering a special value of 0 for the NewSize parameter indicates that the Autofit method should be applied. Excel will then choose 'best' sizes for the rows or columns based upon the data contained in them.
Returns:
0 on success
-91 or -1004 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
The following code sets the height of rows 5, 6 and 7 to 20.5 points
RetCode = xlcObj.xlcSetRowOrColumnSize(”Rows”, 5, 3, 20.5)
The following code Autofits column C.
RetCode = xlcObj.xlcSetRowOrColumnSize(”Columns”, 3, 1, 0)
58 xlcSetStatusBarMessage
xlcObj.xlcSetStatusBarMessage([MessageText])
Purpose:
Sets the text in the Excel status bar. Usually used to inform the User of progress during a complex or long operation.
MessageText (optional, default "") is the text to display in the status bar. A blank value will remove any currently displayed text.
Returns:
0 on success
-9001 if Excel is not launched
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
RetCode = xlcObj.xlcSetStatusBarMessage(”Processing, Please Wait…”)
59 xlcSetWindowSplit
xlcObj.xlcSetWindowSplit([Column] [, Row] [, FreezePanes])
Purpose:
Splits the Active Window into multiple panes, or removes a split.
Column (optional, default 0) indiciates the number of columns to the left of the split. A value of 0 indicates no vertical split.
Row (optional, default 0) indiciates the number of rows to the left of the split. A value of 0 indicates no horizontal split.
FreezePanes (optional, default False) indiciates whether to freeze panes after a split. This allows header rows and/or columns to be fixed in position while the spreadsheet is scrolled up and down and left and right. Valid values are:
False Set FreezePanes off
True Set FreezePanes on
To remove a split, call the function with default values or without supplying any parameter values.
Returns:
0 on success
-91 if command unavailable
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Examples:
This example splits the window after column B and row 3, and freezes the top and left panes.
RetCode = xlcObj.xlcSetWindowSplit(2, 3, True)
This example removes any split that may be in operation.
RetCode = xlcObj.xlcSetWindowSplit
60 xlcSortRange
xlcObj.xlcSortRange([RangeName] [, ByRowOrColumn] [, Key1] [, Order1] [, Key2] [, Order2] [, Key3] [, Order3] [, HeaderRow] [, CaseSensitive])
Purpose:
Sorts the data in the specified range by rows or columns. Supports up to 3 sort keys.
RangeName (optional, default "") specifies the area to sort. Valid values are:
• "CurrentRegion"
• "UsedRange"
• "" (defaults to the current selection)
• other valid range name
For more information on range names see "Specifying Excel Ranges" below.
ByRowOrColumn (optional, default "Rows") indicates whether the sort should be top to bottom (by rows) or left to right (by columns). Valid values are:
• "Row" or "Rows"
• "Col" or "Cols" or "Column" or "Columns"
Key1, Key2, and Key3 indicate which rows or columns to sort on. They should be cell references which identify the key fields. Suppose for example that the range to be sorted top to bottom (by rows) is A1:F20 and it is to be sorted by columns B, C and F. Then Key1, Key2 and Key3 would be "B1", "C1" and "F1" respectively.
Key1 is mandatory, Key2 and Key3 may be omitted.
Order1 Order2,Order3 specify the sort sequence for Key1, Key2, and Key3. Each has value:
• "Asc" or “Ascending” (Ascending)
• "Desc" or “Descending” (Descending)
Order1 is mandatory. Order2 and Order3 may be omitted if Key2 and Key3 are omitted.
HeaderRow (optional, default False) indicates whether the top row of the range should be treated as a header row and not sorted:
False Top row is not a header row
True Top row is a header row
CaseSensitive (optional, default False) indicates whether the sort should take case into consideration.
False Sort is not case sensitive
True Sort is case sensitive
Returns:
0 on success
-91 if command unavailable
-1004 range not found or sort key invalid
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The example sorts range A5:C12 by rows. Two keys are used (columns B and A, both in descending sequence) and the sort is case insensitive. The sort does not assume a header row.
RetCode = xlcObj.xlcSortRange(“A5:C12”, “Rows”, “B5”, “Desc”, “A5”,
“Desc”)
61 xlcUnFreeze
xlcObj.xlcUnfreeze
Purpose:
Unfreezes the Excel window which has previously been frozen by a call to xlcFreeze. See the documentation for xlcFreeze for important considerations regarding use of this function.
Note: Windows only allows one window on the desktop to be frozen at a time. This function actually unfreezes whichever window is frozen at the time. If no window is frozen no error occurs.
See also xlcFreeze.
Returns:
Always returns a value of 0
Example:
RetCode = xlcObj.xlcUnFreeze
62 xlcUnprotectWorkbook
xlcObj.xlcUnprotectWorkbook([Password])
Purpose:
Unprotects the active workbook.
Password (optional, default "") specifies the password required to unprotect the workbook. Password may be blank ("").
See also xlcProtectWorkbook, xlcProtectWorksheet, xlcUnprotectWorksheet, xlcSetCellProtection.
Returns:
0 on success
-91 if command unavailable
-1004 incorrect password supplied
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The first example unprotects a workbook that has been protected with no password. The second example unprotects a workbook that has been protected with password "ABC1".
RetCode = xlcObj.xlcUnprotectWorkbook
RetCode = xlcObj.xlcUnprotectWorkbook(“ABC1”)
63 xlcUnprotectWorksheet
xlcObj.xlcUnprotectWorksheet([Password])
Purpose:
Unprotects the active worksheet.
Password (optional, default "") specifies the password required to unprotect the worksheet. Password may be blank ("").
See also xlcProtectWorksheet, xlcProtectWorkbook, xlcUnprotectWorkbook, xlcSetCellProtection.
Returns:
0 on success
-91 if command unavailable
-1004 incorrect password supplied
-9001 if Excel is not launched
-9004 invalid parameter value(s)
ReturnCode 0 indicates success.
ReturnCodes less than zero are failure codes.
Example:
The first example unprotects a worksheet that has been protected with no password. The second example unprotects a worksheet that has been protected with password "ABC1".
RetCode = xlcObj.xlcUnprotectWorksheet
RetCode = xlcObj.xlcUnprotectWorksheet(“ABC1”)
4 Specifying Excel Ranges
Several of the ExcelController methods, such as xlcFormatTable, take a RangeName parameter. These parameters specify Excel ranges to be operated on by the methods and all take the same permissible set of values as follows:
• If RangeName is "CurrentRegion" the range will be determined by taking the current selection and expanding it in all directions until a border of empty cells is found. Thus if the selection is anywhere within a table, the entire table will be used.
• If RangeName is "UsedRange" the range will cover all cells used in the active worksheet.
• If RangeName is blank ("") the current selection will be used.
• If RangeName is anything else it will be assumed to be a valid range name as defined in the current workbook. This may be an absolute reference to a cell or block of cells in the form "B3" or "A5:D20" or a named range. Ranges may be given meaningful names in Excel by selecting the desired cells and choosing [Insert…Name…Define] from the main menu. If a named range is specified but does not exist an error (-1004) will be returned by the method.
Some examples of valid range references:
"B3" (refers to a single cell)
"A5:D20" (refers to a block of cells)
"C:C" (refers to a single column)
"16:20" (refers to a block of rows)
"A1:B5,D7:D12" (refers to a combination range containing two areas)
The combination range containing multiple area is particularly useful when creating charts, if the data to be charted and/or the column and row labels are contained in non-contiguous areas of the worksheet.
• In some cases a RangeName of "Bottom" is also valid. For example in xlcImportTextFile. In this case a value of "Bottom" will cause the data to be inserted starting at the first unused row at the bottom of the worksheet, in column A.
If you are using Excel 97 or later, in most cases (depending on context) ExcelController ranges can also refer to ranges in worksheets other than that which is currently active, or even other open workbooks. The following examples show ranges which include sheet and workbook references:
" Sheet3!A1:B3"
" Sales!H23"
"[MyOtherBook]Sheet1!A1:B3"
If you are using Excel 95 or earlier, however, passing this sort of range specification will normally result in a -1004 "Range method of Application class failed" error.
The xlcGotoRCRange function also allows selection of a rectangular range (or a single cell) using row and column numbers.
Note: the xlcDeriveRangeName function can be used to return a string of the form "A5:D9" from row and column numbers.
5 Excel Templates and Data Sources
Data and formulae can be directly inserted into an Excel spreadsheet from a calling application using the xlcInsertValue function. However, the quickest and most efficient way to insert a block of data is from a text data source file using the xlcImportTextFile function.
It is not necessary to master Excel templates in order to create new workbooks containing data. A new workbook can be created from the Excel default template with xlcCreateNewWorkbook. However, templates provide a great deal of power and flexibility which makes the creation of complex, formatted workbooks both quick and extremely simple.
1 Text Files and xlcImportTextFile
The xlcImportTextFile function requires a TextFilePath parameter. The text file contains the data to be inserted into the workbook at the specified position. In order for this function to work correctly the text file must be prepared in a particular way prior to calling the function.
When the xlcImportTextFile function executes it inserts the entire contents of the text file into the workbook in a single operation. This is a quick and efficient process.
The fields in the data source file should be contained in double quotes and separated by comma delimiters. Thus the data source file may contain records as follows:
"Name","Company","JobTitle","Age","Last Order Date"
"A. Brown","Brown Engineering","Director","56","5/12/96"
"B. Smith","SmithCo Foods","IT Manager","40","1/2/97"
"C. Jones","Jones Incorporated","","32","5/5/97"
The first record in this example contains column headings. Note that these are not necessarily required - if the columns are static it may be simpler to include the header row (and all its formatting) in a template.
An example datasource text file is included in the installation pack: DATASRC.TXT.
Numeric, date and time values in recognised formats will be interpreted correctly by Excel. If you wish a non-text value to be interpreted as a text string you should precede the value with a single quote, just as you would if you wanted to achieve the same thing in Excel itself. For example "'123.45" will be inserted as text, whereas "123.45" will be inserted as a number. If you wish to insert a formula from a text file, prefix it with "=". Thus "A1+A2" will be entered as a text string, but "=A1+A2" will be entered as a formula.
Additional Considerations:
• If your text data is liable to contain double quote characters embedded within it, you should replace these within the text file by two double quotes together. When Excel imports the data it will interpret the two characters as one.
• If your text file contains blank values then these may be included as "" (as in the 4th row 3rd column of the example above). However, if your text file could potentially contain rows or columns which have nothing but blanks this may cause problems as data below or to the right of the blank row or column will be ignored. If this is a possibility be sure to put a space between the double quotes (" " instead of "") and you will not have this problem.
• The xlcImportTextFile function takes a Delimiter parameter which allows you to use a character other than the comma to separate the fields. However, this should not normally be required as commas embedded in data will not normally cause any problems if each field is contained in double quotes.
2 Using Templates
Excel templates are files with the extension .XLT which are used as a basis for creating new workbooks in a particular format. An application can insert variable data into a worksheet which is based on a template. The power of templates lies in the fact that much of the content of a desired workbook can be created in advance - number of sheets, headers and footers, page setup, row and column labels, formulae, cell formatting, etc. Many cosmetic, and more fundamental, changes can subsequently be made to the template by developers or by Users themselves without the need to change the calling application in nay way.
Another powerful feature of templates is that they can contain "styles". Styles are essentially collections of formatting properties which can be applied as desired to individual cells or ranges. A template may, for example contain a style named "TableData" which defines the font name and size, text format and alignment, background colour, shading, and various other properties which are desired for the cells within a table. This style, defined and held in the template itself, can later be applied (using one of the ExcelController functions such as xlcFormatTable or xlcFormatRange) to a block of data inserted into a new workbook created from the template. The template may contain other styles such as "SheetTitle" and "ColumnHeadings". By changing the styles in a template it can be possible to completely change the look of the workbook, again without any change to the controlling application.
Templates should be created during system design or by Users. They must exist prior to application execution. Data source files, however, are often created dynamically during application execution using standard text file manipulation functions.
Note that to edit an existing template within Excel you must hold down the shift key as you open it. Otherwise you open a new workbook based in the template. This is different to the way Word operates.
An example Excel template is included in the installation pack: EXAMPLE.XLT.
6 Error Handling
Whenever an ExcelController procedure is executed the resulting return code and any error text and/or result value are stored in the ReturnCode, ErrorText and ResultValue properties of the ExcelControl object. They remain available until the next procedure executes or the ExcelControl object is destroyed.
After executing any ExcelController procedure it is advisable to test for a negative ReturnCode. If one is found it is simple to then retrieve and display the relevant ErrorText.
By default, no error messages are displayed to the user by ExcelController. It is left to the Developer to decide when to display messages and when not to. It is possible, however, to switch on automatic error display by using the DisplayErrors property of the ExcelControl object:
xlcObj.DisplayErrors = True
Now when an error is encountered by ExcelController a message box including the error number and description will automatically be displayed. It can be useful to set DisplayErrors on during development as any errors are then more obvious.
The ExcelController reference documentation lists common return codes for each function but the full list of possible error codes is long. For more information see the Microsoft Excel Visual Basic Reference help file – search for “trappable errors”.
7 ExcelController in Action - Creating a Formatted Spreadsheet
The scenario here is that of creating a simple worksheet from a two-dimensional array of data. This section shows how, with the help of the ExcelController xlcImportTextFile and xlcFormatTable functions, the worksheet may be created with a minimum of effort. The code is Visual Basic but could easily be ported to other development tools.
The example does not make use of a custom Excel template, it creates a new workbook from the default Excel template. However, using templates adds a great deal of power and in particular formatting capability. Because ExcelController can take advantage of template styles for headers, table cells and any other specified ranges, sophisticated formatting can be easily achieved. To see an example look at EXAMPLE.XLT which can be found in the installation pack.
When the xlcImportTextFile function executes it reads data from a data source text file. Each record in the file contains data which is inserted in a separate row in the spreadsheet. Records are delimited and each delimited value is placed in a separate column.
The example uses an array Data() which is dimensioned Data(1 to NumColumns, 1 to NumRows) to hold our data. It then writes delimited records from the array to a file using the BuildFile subroutine defined below.
Add a command button to a VB Form, establish a reference to ExcelController with Project…References (ExcelController must be installed first) and then paste the following code into the form module:
Option Explicit
Dim Data() 'the array to hold the data
Dim NumRows%, NumColumns% 'in our data table
Dim FilePath As String 'where we'll write our temporary text data file
Private Sub Command1_Click()
FillDataArray
FilePath = App.Path & "\TEMP.TXT"
BuildFile (FilePath)
BuildWorkBook
End Sub
Sub BuildWorkBook()
'declare the main object variable for ExcelController
Dim xlcObj As New ExcelControl
Dim RetCode As Long
'launch Excel (default state is maximized)
RetCode = xlcObj.xlcLaunch
'check to see if an error occurred and deal with it
If RetCode < 0 Then GoTo XLC_ERROR
'create an empty workbook
RetCode = xlcObj.xlcCreateNewWorkbook
If RetCode < 0 Then GoTo XLC_ERROR
'import the data starting at cell A3
RetCode = xlcObj.xlcImportTextFile(FilePath, "A3")
If RetCode < 0 Then GoTo XLC_ERROR
'format the table - header row, borders, autofit
RetCode = xlcObj.xlcFormatTable("CurrentRegion", True, True, True)
'add a heading, set font properties 18pt bold
RetCode = xlcObj.xlcInsertValue("Sales Figures", "A1")
RetCode = xlcObj.xlcSetFont("A1", "Arial", 18, 1)
‘move the selection to cell A1
RetCode = xlcObj.xlcGoTo("Range", "A1”)
MsgBox "The workbook was built successfully", , "Complete"
Exit Sub
'error handling
XLC_ERROR:
MsgBox "Error " & xlcObj.ReturnCode & ": " & xlcObj.ErrorText
End Sub
Sub BuildFile(FilePath)
Dim FNumOut% 'the file handle
Dim i%, j%, WorkString
FNumOut% = FreeFile
Open FilePath For Output As FNumOut%
For i% = 1 To NumRows%
WorkString = ""
'build concatenated string with column values for this
'row
'will look like this "Name","Company",...
For j% = 1 To NumColumns%
WorkString = WorkString & """" & Data(j%, i%) & ""","
Next j%
'Remove the last delimiter character
WorkString = Left$(WorkString, Len(WorkString) - 1)
Print #FNumOut%, WorkString
Next i%
Close FNumOut
End Sub
Sub FillDataArray()
'populates the data array for the example
NumRows% = 6 'including header row
NumColumns% = 5
ReDim Data(1 To NumColumns%, 1 To NumRows%)
'put the header row in
Data(1, 1) = "Name"
Data(2, 1) = "Company"
Data(3, 1) = "JobTitle"
Data(4, 1) = "Age"
Data(5, 1) = "Last Order Date"
'record 1
Data(1, 2) = "John Burridge"
Data(2, 2) = "Acme Engineering"
Data(3, 2) = "Manager"
Data(4, 2) = "45"
Data(5, 2) = "10/1/2000"
'record 2
Data(1, 3) = "Susan Brown"
Data(2, 3) = "Greenside Fabrics"
Data(3, 3) = "Sales Director"
Data(4, 3) = "34"
Data(5, 3) = "2/6/1999"
'record 3
Data(1, 4) = "Ed Franklin"
Data(2, 4) = "Franklin PR"
Data(3, 4) = "MD"
Data(4, 4) = "50"
Data(5, 4) = "5/4/1998"
'record 4
Data(1, 5) = "Susan Hardy"
Data(2, 5) = "Holts Communications"
Data(3, 5) = "Production Manager"
Data(4, 5) = "46"
Data(5, 5) = "4/11/2000"
'record 5
Data(1, 6) = "Shreya Patel"
Data(2, 6) = "NorthWest Water"
Data(3, 6) = "HR Executive"
Data(4, 6) = "33"
Data(5, 6) = "8/8/2000"
End Sub
Note that just a few ExcelController functions are required to create the report. These are shown in bold. Two properties, ReturnCode and ErrorText, are also used for error handling.
Most ExcelController functions require Excel to be launched before they can run, otherwise they return an error code -9001 (“Microsoft Excel is not Open”). xlcLaunch may be used for this purpose. Note that if Excel is already running, xlcLaunch will not start a new instance but will return a reference to the current instance. This means that it can be used safely in situations where you are not sure whether or not Excel will be running. You also have the option of specifying that a new instance of Excel be launched even if one is already running, with xlcLaunchNewInstance.
8 Sample Code
A complete Visual Basic sample application is included with ExcelController. The application shows simple and more complex uses of ExcelController and incorporates a Workbook Generator. It also shows how to handle errors and how to use ExcelController in combination with native calls to Excel to add additional functionality.
[pic]
This section presents snippets of code from the sample application to show how ExcelController may be used.
1 Declaring the main ExcelController object variable
Dim xlcObj As New ExcelControl
2 Setting Error Display On
xlcObj.DisplayErrors = True
3 Launching Excel
RetCode = xlcObj.xlcLaunch 'default state is maximized
4 Opening a workbook
'this example opens read-only
RetCode = xlcObj.xlcOpenWorkbook("C:\mydocs\book1.xls", True)
5 Checking for errors after an ExcelController function call
'... ExcelController function call
'check to see if an error occurred
If RetCode < 0 Then 'this signifies error
'use the ExcelController error properties to display the error as desired
MsgBox "Error (" & xlcObj.ReturnCode & "): " & xlcObj.ErrorText, _
vbCritical, "Error reported by ExcelController"
End If
6 Complete Workbook Generation Procedure
This is taken from the sample application. It uses the xlcImportTextFile function (an alternative would be to fill cells individually using xlcInsertValue). See the application code for more details of subroutines etc.
Private Sub btnGenWorkbook_Click()
'generates a template-based workbook using data for customers in the listbox
'requires Excel template EXAMPLE.XLT (supplied)
Dim TemplatePath$, DataFileName$, RetCode As Long, ChartDataRange$, TopRow%, BottomRow%
'get the customer data into a text file, one delimited record per customer
'See reference documentation on xlcImportTextFile for more
'information.
DataFileName$ = App.Path & "\TEMPDATA.TXT"
PutCustomerInfoInTextfile (DataFileName$)
'specify which Excel template we are going to use
TemplatePath$ = App.Path & "\EXAMPLE.XLT"
'launch Excel or obtain a handle to a running instance
RetCode = xlcObj.xlcLaunch(2) 'minimized
If RetCode < 0 Then GoTo xlc_ERROR 'negative return code indicates error
'if desired Freeze the Excel window so that updates are not displayed as
'they happen. This speeds execution and looks smoother
If chkFreeze Then xlcObj.xlcFreeze
'create the workbook from the template
RetCode = xlcObj.xlcCreateNewWorkbook(TemplatePath$)
If RetCode < 0 Then GoTo xlc_ERROR 'negative return code indicates error
'insert the data
'(note that data and formulae can also be directly inserted into cells
'using xlcInsertValue)
RetCode = xlcObj.xlcImportTextFile(DataFileName$, "A5", , "Down")
If RetCode < 0 Then GoTo xlc_ERROR 'negative return code indicates error
'format the table entries according to a predefined style
RetCode = xlcObj.xlcFormatRange("TableEntry")
'set the date format for column C
RetCode = xlcObj.xlcFormatRange("DateFormat", "C:C")
'add a chart of average order value v organisation
'see the documentation for more info on the parameters for this function
TopRow% = xlcObj.xlcGetRangeBoundary("Top")
BottomRow% = xlcObj.xlcGetRangeBoundary("Bottom")
ChartDataRange$ = "B" & TopRow% & ":B" & BottomRow% & ",F" & TopRow% & ":F" & BottomRow%
RetCode = xlcObj.xlcInsertChartOnSheet(50, 300, 500, 400, -4100, ChartDataRange$, 4, , , 1, True, "Average Order Values")
If RetCode < 0 Then GoTo xlc_ERROR 'negative return code indicates error
'position the selection in cell A1
RetCode = xlcObj.xlcGoTo("Range", "A1")
'unfreeze the Excel window if we froze it
If chkFreeze Then xlcObj.xlcUnFreeze
'now we've finished maximize Excel again (it was minimized while we built the workbook)
RetCode = xlcObj.xlcLaunch(3) 'maximized
'if desired ensure that our application comes back to the front
'after the document is built (note that this routine
'is nothing to do with ExcelController)
If chkBringToFront Then BringThisApplicationToFront
Exit Sub
xlc_ERROR:
'always unfreeze the Excel window in error situations
If chkFreeze Then xlcObj.xlcUnFreeze
If RetCode < 0 Then 'this signifies error
'use the ExcelController error properties to display the error as desired
MsgBox "Error (" & xlcObj.ReturnCode & "): " & xlcObj.ErrorText, vbCritical, "Error reported by ExcelController"
End If
End Sub
7 Combining ExcelController functions with native calls to Excel for additional functionality
This code is taken from the sample application. It demonstrates how to access the Excel native Excel.Application object via the ExcelController ExcelApp object and how to code additional functionality which will work with multiple Excel versions. See the application code for more details.
Private Sub btnBuildDoc_Click()
'demonstrates combination of ExcelController calls and native calls to Excel
'First inserts some data into a new non-template workbook using ExcelController.
'
'Then goes on to add a star shape to the worksheet
'this has to be done natively as ExcelController does not include
'this capability
Dim RetCode As Long, i%, tmpXlObj As Object, tmpRange$, tmpShape As Object
'launch Excel or obtain a handle to a running instance
RetCode = xlcObj.xlcLaunch(2) 'minimized
If RetCode < 0 Then GoTo xlc_ERROR 'negative return code indicates error
'create a new empty workbook
RetCode = xlcObj.xlcCreateNewWorkbook
If RetCode < 0 Then GoTo xlc_ERROR 'negative return code indicates error
'insert some text
For i% = 1 To 10
tmpRange$ = "A" & i%
RetCode = xlcObj.xlcInsertValue("This text was inserted using the ExcelController xlcInsertValue function", tmpRange$)
Next i%
'format the text (bold,15 pt)
RetCode = xlcObj.xlcSetFont("A1:A10", "Arial", 15, 1, , , , 3)
'so far so good but now we want to do something not directly supported by ExcelController
'ExcelController offers the Excel.Application object as xlcObj.ExcelApp
Set tmpXlObj = xlcObj.ExcelApp
'now we can use the Excel object natively
'remember to test this type of code with all versions of Excel
'you need to support. If necessary add different code for
'different versions based on xlcObj.ExcelVersion
'add a star shape
Set tmpShape = tmpXlObj.ActiveSheet.Shapes.AddShape(94, 100, 250, 100, 100)
With tmpShape.Fill
.ForeColor.RGB = RGB(255, 0, 0)
.BackColor.RGB = RGB(0, 0, 255)
.TwoColorGradient 4, 1 'diagonal
End With
RetCode = xlcObj.xlcInsertValue("But the star was created using the native Excel object via ExcelController", "A12")
'maximize Excel
RetCode = xlcObj.xlcLaunch(3) 'maximized
'ensure that our application comes back to the front
'after the document is built (note that this routine
'is nothing to do with ExcelController)
BringThisApplicationToFront
MsgBox "The workbook was successfully created.", vbInformation, "ExcelController Demo"
Exit Sub
xlc_ERROR:
If RetCode < 0 Then 'this signifies error
'use the ExcelController error properties to display the error as desired
MsgBox "Error (" & xlcObj.ReturnCode & "): " & xlcObj.ErrorText, vbCritical, "Error reported by ExcelController"
End If
1 End Sub
END.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.