MicrosoftExcel connector properties, methods, and events
Use the MicrosoftExcel connector to integrate Excel data, functions, and events into a solution. You can use the connector in automations to write data to cells, read data from cells, and import and export data from Excel.
The MicrosoftExcel connector exposes the underlying COM components so that you can automate almost any task that you can manually do in Excel. The object model has four interfaces: Application, Workbook, Worksheet, and Range.
Note: For more information on the Excel object model, see Excel object model overview.
The MicrosoftExcel connector includes methods that you can use to work with these interfaces to perform common Excel tasks. However, by extracting a proxy for one of these interfaces you have access to all of the properties, methods, and events available in Excel. For more information, see Using the MicrosoftExcel Connector.
When you add this component to an automation, the system places it on the Global tab of the component tray. For more information, see Adding Global Components to an Automation. This component must be global to be referenced by other automations in the project.
For more information, see General Component Properties, Methods, and Events.
This topic includes information about the following:
Property |
Description |
AskToUpdateLinks 8.0 SP1 2010 or later |
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. |
Specifies the changes to occur when the workbook is closed. Choose from the following options:
|
|
DisplayAlerts 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
Specifies the Microsoft.Office.Interop.Excel.Range object that refers to the Active Cell in the connector Excel application. |
ExcelApplicationActiveSheet 8.0 SP1 2010 or later |
Specifies the Microsoft.Office.Interop.Excel.Worksheet object that refers to the Active Sheet in the connector Excel application. |
ExcelApplicationWorkbooks 8.0 SP1 2010 or later |
A reference to the connector Microsoft Excel workbook object. |
ExcelApplicationWorksheets 8.0 SP1 2010 or later |
A reference to the connector Microsoft Excel worksheet object. |
ExcelWindowHeight 8.0 SP1 2010 or later |
Returns or sets a Double value that represents the height, in points, of the main Excel window. |
ExcelWindowLeft 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
A reference to the connector Microsoft Excel worksheet object. |
ExcelWorkbookWorksheets 8.0 SP1 2010 or later |
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:
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. |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 |
Runs a Visual Basic macro or calls a function. |
String MacroName |
Object |
RunMacro |
Runs a Visual Basic macro or calls a function. All of the macro or function parameters are optional. |
String Macroname, Optional parameters |
Object |
Save |
This method saves the currently opened workbook and overwrites the existing file. |
None |
Void |
Save 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2024 or later |
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 8.0 SP1 2024 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
Clears the AutoFilter property for the Range object that you specify. |
Range range |
Void |
RangeClearFormats 8.0 SP1 2010 or later |
Clears the format information for the Range object that you specify. |
Range range |
Void |
RangeCopy 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
Gets the column width for a range. |
Range range |
Void, out Double width |
RangeGetCoordinates 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
Gets the row height for a range. |
Range range |
Void, out Double width |
RangeSetAutoFilter 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
Sets the background color for a range. |
Range range, Color backgroundColor |
Void |
RangeSetColumnWidth 8.0 SP1 2010 or later |
Sets the column width for a range. |
Range range, Double width |
Void |
RangeSetFont 8.0 SP1 2010 or later |
Sets the font for a range. |
Range range, Font font, Color color |
Void |
RangeSetRowHeight 8.0 SP1 2010 or later |
Sets the row height for a range. |
Range range, Double height |
Void |
RangeSort 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
Deletes a row from the worksheet that you specify. |
_Worksheet worksheet, Int rowNumber |
Boolean |
RowInsert 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
Activates the workbook that you specify. |
_Workbook workbook |
Void |
WorkbookGetActiveSheet 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
Clears format information for the worksheet that is in the workbookthat you specify. |
_Workbook workbook, String sheetName |
Boolean |
WorksheetDelete 8.0 SP1 2010 or later |
Deletes the worksheet that you specify from the workbook that you specify. |
_Workbook workbook, String sheetName |
Boolean |
WorksheetDelete 8.0 SP1 2010 or later |
Deletes a worksheet by index from the workbook that you specify. |
_Workbook workbook, Int32 index |
Boolean |
WorksheetGetByName 8.0 SP1 2010 or later |
Gets a worksheet object from the workbook that you specify. |
_Workbook workbook, String sheetName |
Boolean, out _Worksheet worksheet |
WorksheetGetRange 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
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 8.0 SP1 2010 or later |
Determines if the worksheet that you specify is in EditMode. |
_Worksheet worksheet |
Boolean |
If you set cell value to a named value (Insert, Name, Define, ADD) in a spreadsheet and use that named value in the GetCellValue and SetCellValue methods (instead of, for example 'B23'), then you do not have to hard code cells numbers in your automations.
If the named value represents a formula calculation result, the system returns the appropriate value for the selected range. If there is no formula, the GetCellValue method does not iterate through that result cell range because of the unexpected format.
Related information
Using the MicrosoftExcel Connector
