Property

Description

AskToUpdateLinks

If you set this property to True, Microsoft Excel asks the user to update links when it opens files with links. 

If you set this property to False, the system automatically updates the links and displays no dialog box. This property is a read/write Boolean. The default is True.

Changes

Specifies the changes to occur when the workbook is closed. Choose from the following options:

• Save – Changes are saved when the workbook is closed.

• DoNotSave – Changes are not saved when the workbook is closed.

DisplayAlerts

If you set this property to True, Microsoft Excel displays alerts and messages while a macro is running.

If you set this property to False, the system suppresses the alerts and messages. This property is a read/write Boolean. The default is True.

ExcelApplication

A reference to the connector Microsoft Excel COM application.

ExcelApplicationActiveCell

Specifies the Microsoft.Office.Interop.Excel.Range object that refers to the Active Cell in the connector Excel application.

ExcelApplicationActiveSheet

Specifies the Microsoft.Office.Interop.Excel.Worksheet object that refers to the Active Sheet in the connector Excel application.

ExcelApplicationWorkbooks

A reference to the connector Microsoft Excel workbook object.

ExcelApplicationWorksheets

A reference to the connector Microsoft Excel worksheet object.

ExcelWindowHeight

Returns or sets a Double value that represents the height, in points, of the main Excel window.

ExcelWindowLeft

Returns or sets a Double value that represents the distance, in points, from the left edge of the screen to the left edge of the main Excel window.

ExcelWindowTop

Returns or sets a Double value that represents the distance, in points, from the top edge of the screen to the top edge of the main Excel window.

ExcelWindowWidth

Returns or sets a Double value that represents the distance, in points, from the left edge of the main Excel window to its right edge.

ExcelWorkbook

A reference to the connector Microsoft Excel workbook object.

ExcelWorkbookActiveSheet

A reference to the connector Microsoft Excel worksheet object.

ExcelWorkbookWorksheets

Indicates the Microsoft.Office.Interop.Excel.Worksheets collection for the connector Excel Workbook. This collection contains a Worksheet object for each worksheet in the connector Excel workbook.

IsRunning

Indicates whether Microsoft Excel is running.

Missing

Generates a System.Reflection.Missing.Value that you use as input for method parameters that you do not want to specify. This is required for methods called from the Range, _Worksheet, and _Workbook proxy objects.

Mode

Specifies how the workbook file should be opened. The options are:

• Create

• Open

When you set this property to Create, Studio creates a file if the file does not exist. 

If you choose Open, the system tries to open the file but does not create it if the file does not exist. If the file does not exist, the system displays an error message.

The default is Create.

OutputName

(19.1.7 and later)

Specifies one of the supported file types.

• Microsoft Excel: .xlsx, xlsm, .xltx, .xltm, .xls, .xlt
• OpenDocument spreadsheet: .ods, .ots
• Character Separated Values: .csv, .tsv

If you include a Save method, the system uses the value entered for this property unless it is set to null. If set to null, the system uses the value for the FileName property.

SupportedName

Indicates the Office name for the Excel program that is used.

SupportedVersion

Displays the version number of the Excel program.

Version

Indicates the version of Microsoft Excel that is in use.

Visible

 A Boolean property that indicates whether the main Excel window is visible when the solution is running  

Workbook

Use this property to specify the name of the workbook file you want to use in the solution. You must specify a workbook to use the component.

By default, Studio looks at the exact file path selected, however, you can use the Folder property to determine the folder file path that is resolved at runtime using predefined tokens. You select the tokens from a menu that has options such as Deployment, Temporary, Personal and so on. The system resolves the folder file path using the selected token.

Worksheet

Use this property to specify the worksheet in the workbook that you want to use in the solution. If you do not enter a worksheet, the system uses the default worksheet.

Event

Description

NewSheet

This event occurs before a new workbook sheet is created.

NewWorkbook

This event occurs when a new workbook is created.

Quit

This event occurs when Microsoft Excel is closed manually.

SheetActivate

This event occurs when a workbook sheet is activated.

SheetCalculate

This event occurs when the current workbook is recalculated.

SheetChange

This event occurs when the current workbook sheet changes.

SheetDeactivate

This event occurs when the current workbook sheet is deactivated.

WindowActivate

This event occurs when the main Excel window is activated.

WindowDeactivate

This event occurs when the main Excel window is deactivated.

WorkbookClosed

This event occurs when the current workbook is closed.

WorkbookOpened

This event occurs when a new workbook is opened.

WorkbookPrinted

This event occurs when the current workbook is printed.

WorkbookSaved

This event occurs when the current workbook is saved.

Method

Description

Parameters

Result type

Close

Closes the workbook and the Microsoft Excel application instance that you associated with this MicrosoftExcel Connector.

Depending on your entry in the Changes property, the Close method may also try to save any changes before it closes the workbook.

None

Void

ExportData

Exports range of cells from the worksheet into a .NET data table.

If you set the createHeader property to True, the method names the columns in the table based on the first row in the range.

String cellStart, String cellEnd, Boolean createHeader

DataTable

ExportData

Exports range of cells from the worksheet into a .NET data table.

If you set the createHeader property to True, the method names the columns in the table based on the first row in the range.

_Worksheet worksheet, String cellStart, String cellEnd, Boolean createHeader

DataTable

ExportData

Exports range of cells from the worksheet into the .NET data table that you specify.

This method only retains the data for the column names that are in the data table that you specify.

This method replaces DBNull.Values with String.Empty in the result table.

If you set the createHeader property to True, the method names the columns in the table based on the first row in the range.

_Worksheet worksheet, String cellStart, String cellEnd, Boolean createHeader, ref DataTable exportTable

Void

FindColumns

Returns all matching columns. The search parameter is case sensitive.

String value, out Int32[] rows

FindRows

Returns all matching rows. The search parameter is case sensitive.

GetCellValue

Returns the value in the cell that you specify using the input parameter.

You can select a specific cell, such as d4, or input the cell number from another object. You can also use a named value as the input. For more information, see Named Value.

String cell

String

ImportData

Imports a .NET data table into the workbook. The row and column are zero (0) based for importing purposes.

The system uses a header for search parameters. If you set the createHeader parameter to True, the automation creates headers for the table.

Boolean CreateHeader, Int32 Rowstart, Int32 columstart, dataTable

Void

ImportData

Imports a .NET data table into the workbook. The row and column are zero (0) based for importing purposes.

The system uses a header for search parameters. If set the createHeader parameter to True, the automation creates headers for the table.

Use the uiUpdates parameter to suspend user interface updates until the import operation finishes to have the method execute more quickly.

DataTable dataTable, Boolean createHeader, Int rowStart, Int columnStart, Boolean uiUpdates

Void

ImportData

Imports a .NET data table into the worksheet that you specify. The row and column are zero (0) based for importing purposes.

The system uses a header for search parameters. If you set the createHeader parameter to True, the automation creates headers for the table.

_Worksheet worksheet, DataTable dataTable, Boolean CreateHeader, Int Rowstart, Int columnstart

Void

ImportData

Imports a .NET data table into the worksheet that you specify. The row and column are zero (0) based for importing purposes.

The system uses a header for search parameters. If you set the createHeader parameter to True, the automation creates headers for the table.

Use the uiUpdates parameter to suspend user interface updates until the import operation finishes to have the method execute more quickly.

_Worksheet worksheet, DataTable dataTable, Boolean CreateHeader, Int Rowstart, Int columnstart, Boolean uiUpdates

Void

Open

Opens a workbook using the workbook name that you specify.

String Workbook

Void

Open

Opens a workbook using the workbook name that you specify.

This method returns True if the open succeeds and False if it fails or there was an exception. The message contains the Exception message.

String Workbook

Boolean, out String message

Open

Opens a workbook using the workbook name that you specify.

Include the readOnly parameter if you want the system to open the workbook in read only mode.

Include the Password parameter if the workbook is protected with a password.

This method returns an error message if the operation fails, but the system does not throw an exception.

String workbook, Boolean readOnly, String password

Boolean, out String message

RunMacro
(1 Parameter)

Runs a Visual Basic macro or calls a function.

String MacroName

Object

RunMacro
(6 Parameters)

Runs a Visual Basic macro or calls a function.

String Macroname, Optional parameters

Object

Save

This method saves the currently opened workbook and overwrites the existing file.

None

Void

Save

Saves the open workbook and overwrites the file.

This method returns an error message if the operation fails, but the system does not throw an exception.

None

Boolean, out String message

SaveAs

Saves the open workbook using the file name that you specify.

String filename

Void

SaveAs

Saves the open workbook using the file name that you specify.

This method returns an error message if the operation fails, but the system does not throw an exception.

String filename

Boolean, out String message

SaveAsXLSB

Use this method to save the open workbook that you specified in the fileName parameter, in XLSB format.

 If the operation fails, there is no message.

Note: This method does not apply to Microsoft Office 2002 and 2003.

string fileName

Boolean

SaveAsXLSB

Use this method to save the open workbook that you  specified in the fileName parameter, in XLSB format.

If the operation fails, the system returns False and the message that you specified with the out string message parameter.

Note: This method does not apply to Microsoft Office 2002 and 2003.

string fileName, out string message

Boolean, out String message

SetCellValue

This method sets the current workbook cell value. In addition to referencing the cell directly, you can use a named value as the input. For more information, see Named Value.

String cell, String cellValue

Void

Method

Description

Parameters

Result type

TryCastToRange

Casts a generic object into a Microsoft.Office.Interop.Excel.Range type. Use this method when you are iterating through a Range collection with a ListLoop. The ListLoop outputs a generic object without the required properties, methods, and events.

This method creates a Range object with all of its properties, methods, and events. This method returns True if the attempt succeeds and False if there is an exception.

Object obj

Boolean, out Range range

TryCastToWorkbook

Microsoft.Office.Interop.Excel.Workbook type. Use this method when you are iterating through a Workbook collection with a ListLoop. The ListLoop outputs a generic object without the required properties, methods, and events.

This method creates a _Workbook object with all of its properties, methods, and events. This method returns True if the attempt succeeds and False if there is an exception.

Object obj

Boolean, out _Workbook workbook

TryCastToWorksheet

Casts a generic object into a Microsoft.Office.Interop.Excel.Worksheet type. Use this method when you are iterating through a worksheet collection with a ListLoop. The ListLoop outputs a generic object without the required properties, methods, and events.

This method creates a _Worksheet object with all of the properties, methods, and events expected. This method returns True if the attempt succeeds and False if there is an exception.

Object obj

Boolean, out _Worksheet worksheet

Method

Description

Parameters

Result type

CellGetActive

Accepts a _Worksheet object and returns the column (string) and row (int) of the active cell.

The result is the cell address (A1 style notation).

_Worksheet worksheet

String, out String column,  out Int row

CellGetValue

Accepts a _Worksheet object, a column (string), and a row (int) and returns the string value of the cell.

_Worksheet worksheet, string column, Int row

String

CellSetValue

Accepts a _Worksheet object, a column (string), a row (int), and sets the cell value equal to the value (string) that you provided.

_Worksheet worksheet, String column, Int row, string value

Void

Method

Description

Parameters

Result type

ColumnDelete

Accepts a _Worksheet object and deletes the column that you specified in the columnLetter parameter and shifts the other columns to the left.

This method returns True if the attempt succeeds and False if there is an exception. The system logs any exceptions.

_Worksheet worksheet, String columnLetter

Boolean

ColumnGetLetter

Converts a columnNumber (int) to a string which represents the column portion of a cell address. Here is an example:  

1 = A, 2 = B, 27 = AA

For example, use this method to construct the column portion of a cell address when you are iterating through columns using a ForLoop.

Int columnNumber

String

ColumnGetNumber

Converts a columnLetter (string) to an int. Here is an example: 

A = 1, B = 2, AA = 27

For example, use this method to set the limit value of a ForLoop to a column value when you are iterating through the columns in a row.

String columnLetter

Int

ColumnInsert

Accepts a _Worksheet object and inserts a column at the location that you specify using the columnLetter parameter. It then shifts the other columns to the right.

Use the insertFormatOrigin parameter to determine where to get the format for the inserted column. You can choose from these options:

·          xlFormatFromLeftOrAbove

·          xlFormatFromRightOrBelow

This method returns True if the attempt succeeds and False if there is an exception. The system logs any exceptions.

_Worksheet worksheet, String columnLetter, XlInsertFormatOrigin insertFormatOrigin

Boolean

ColumnShowHide

Hides or shows a column.

This method accepts a _Worksheet object and sets the visible property for the column you specify.

_Worksheet worksheet, String columnLetter, Boolean visible

Void

Method

Description

Parameters

Result type

RangeClearAutoFilter

Clears the AutoFilter property for the Range object that you specify.

Range range

Void

RangeClearFormats

Clears the format information for the Range object that you specify.

Range range

Void

RangeCopy

Copies the origin range to the destination range. The origin and destination ranges must be in the same Excel application.

Range originRange, Range destinationRange

Void

RangeGetColumnWidth

Gets the column width for a range.

Range range

Void, out Double width

RangeGetCoordinates

Returns the address boundaries for a range in A1 notation format.

The cellStart parameter contains the cell address at the top left of the range.

The cellEnd parameter contains the cell address for the bottom right of the range

Range range

Void, out String cellStart, out String cellEnd

RangeGetRowHeight

Gets the row height for a range.

Range range

Void, out Double width

RangeSetAutoFilter

Sets the AutoFilter property for a single column and returns the filtered range.

Use the XlAutoFilterOperator method to specify the operator to use to associate the two criteria applied by a filter. For more information, see XIAutoFilterOperator Enum.

Range range, Int columnNumber, String filterText, XlAutoFilterOperator filterOperator, Boolean visibleDropdown

Range

RangeSetBackgroundColor

Sets the background color for a range.

Range range, Color backgroundColor

Void

RangeSetColumnWidth

Sets the column width for a range.

Range range, Double width

Void

RangeSetFont

Sets the font for a range.

Range range, Font font, Color color

Void

RangeSetRowHeight

Sets the row height for a range.

Range range, Double height

Void

RangeSort

Sorts a range.

For complete documentation on this method, see Range.Sort.

Range range, Range key1, XlSortOrder order1, Range key2, XlSortOrder order2, Range key3, XlSortOrder order3, XlYesNoGuess header, Boolean caseSensitive, XlSortOrientation sortOrientation, XlSortMethod sortMethod, XlSortDataOption option1, XlSortDataOption option2, XlSortDataOption option3

Void

Method

Description

Parameters

Result type

RowDelete

Deletes a row from the worksheet that you specify.

_Worksheet worksheet, Int rowNumber

Boolean

RowInsert

Inserts a row into the specified worksheet after the row number that you specify.

Use the XlInsertFormatOrigin method to specify where to get the format for inserted rows. For more information, see XIInsertFormatOrigin Enum.

_Worksheet worksheet, Int rowNumber, XlInsertFormatOrigin insertFormatOrigin

Boolean

Method

Description

Parameters

Result type

WorkbookActivate

Activates the workbook that you specify.

_Workbook workbook

Void

WorkbookGetActiveSheet

Returns the _Worksheet that represents the active sheet -- the visible sheet -- in the workbook that you specify.

This method returns Nothing if no sheet is active.

_Workbook workbook

_Worksheet

WorkbookGetSheets

Returns a Sheets collection that represents all of the sheets in the workbook that you specify.

For more information, see Sheets Interface.

_Workbook workbook

Sheets

Method

Description

Parameters

Result type

WorksheetClearFormats

Clears format information for the worksheet that is in the workbookthat you specify.

_Workbook workbook, String sheetName

Boolean

WorksheetDelete

Deletes the worksheet that you specify from the workbook that you specify.

_Workbook workbook, String sheetName

Boolean

WorksheetDelete

Deletes a worksheet by index from the workbook that you specify.

_Workbook workbook, Int32 index

Boolean

WorksheetGetByName

Gets a worksheet object from the workbook that you specify.

_Workbook workbook, String sheetName

Boolean, out _Worksheet worksheet

WorksheetGetRange

Gets a range object from the worksheet that you specify using cell boundaries.

The system throws an exception if it does not find the object.

_Worksheet workSheet, String cellStart, String cellEnd

Boolean, out Range range

WorksheetGetRange

Gets a range object from the worksheet that you specify using cell boundaries.

This method returns an error message if the operation fails. The system does not throw an exception.

_Worksheet worksheet, string cellStart, string cellEnd

Boolean, out Excel.Range range, out string message

WorksheetGetUsedRange

Gets the used range object from the worksheet that you specify. The system returns the number of rows and columns.

_Workbook workbook, String sheetName

Boolean, out Int rowCount, out Int columnCount, out Range range

WorksheetInsert

Inserts one or more worksheets into the workbook that you specify.

Use the insertBefore and count parameters to specify where to insert the worksheet. For example, if you enter True and 3, the system inserts the three new worksheets before the worksheet you specify with the sheetName parameter.

If you enter False and 3, then the system inserts the three new worksheets after the worksheet you specify with the sheetName parameter.

_Workbook workbook, String sheetName, Boolean insertBefore, Int32 count

Boolean

WorksheetInsert

Inserts a worksheet into the workbook that you specify.

Use the insertBefore parameter to specify where to insert the worksheet. For example, if you enter True, the system inserts the new worksheet before the worksheet you specify with the sheetName parameter.

If you enter False, then the system inserts the new worksheet after the worksheet you specify with the sheetName parameter.

_Workbook workbook, String sheetName, Boolean insertBefore, String newSheetName

Boolean, out _Worksheet newSheet

WorksheetIsInEditMode

Determines if the worksheet that you specify is in EditMode.

_Worksheet worksheet

Boolean