MicrosoftExcel

The MicrosoftExcel Connector component allows users to perform various commonly used MicrosoftExcel related operations.

How To Use

To access the MicrosoftExcel connector

  1. In the Toolbox expand Connectors

    Screenshot

  2. Drag the MicrosoftExcel component and drop it under Global Objects

    Screenshot

  3. Expand Global Objects

  4. Double click on the MicrosoftExcel component under Global Objects, user will see all the Methods for MicrosoftExcel available under Object Explorer section.

    Screenshot

  5. Each method from the list can be dragged and dropped on to the design surface.

  6. It is recommended to rename the connector using the properties window to match the name of the Excel file the connector will be working on. This helps when the user is working on multiple Excel files simultaneously, as each method displays the name of the connector, i.e. the file name.

Open

Allows users to Open a specified Excel file, with this option users can also create a new excel file. The open method must be used prior to performing any other operations in an Excel document.

Input: Filepath

Screenshot

  • To Create New Excel, users must double click on False textbox then Port Properties Window will be display.
  • Select True from Static value dropdown box and click OK.

AddNewWorksheet

Allows users to add a new worksheet into an existing excel document.

Input: SheetName

Screenshot

Close

Allows the user to close an excel document.

Screenshot

ColumnsCount

Allows users to count the number of columns used in a worksheet.

Input: SheetName

Screenshot

Copy

Allows users to copy data from a specified range.

Input:SheetName, CellRange

Screenshot

CreateChart

Allows users to create a specified chart for a defined dataset.

Input: SheetName, CellRange, ChartType.

Screenshot

Screenshot

CreatePivotTable

Allows user to Create PivotTable in the specified Worksheet.

Input: SheetName, PivotTableName

Screenshot

Screenshot

Sheet: The user has to select the existing or New sheet from dropdown list. (If the user selects New Sheet option then they have to specify the SheetName in NewSheetName field).

Location: Specify the range.

PivotTableVersion: Select the version of pivotTable from dropdown list.

Consolidation Function: The user has to select the function from dropdown list which is to be performed on column data. (Ex: Sum, Minimum, Maximum, Validation etc.)

Column Name: The user has to give the column name from work sheet and select the required field from dropdown list based on requirement.

Row Field: Specifies a field name (or an array of field names) to be added as rows, or to be added to the category axis.

Column Field: Specifies a field name (or an array of field names) to be added as columns, or to be added to the series axis.

Page Field: Specifies a field name (or an array of field names) to be added as pages, or to be added to the page area.

Hidden: Object that represents either a single hidden pivot table item or a collection of all the hidden items in the specified field.

DataField: Object that represents either a single pivot table field or a collection of all the fields that are currently shown as data fields.

DeleteColumn

Allows users to delete column data from a specified column index on the worksheet.

Input: SheetName, ColumnIndex

Screenshot

DeleteContentFromRange

Allows users to delete column data from a specified range on the worksheet.

Input: SheetName, CellRange

Screenshot

DeleteRow

Allows users to delete Rowdata from a specified row index on the worksheet.

Input: SheetName, RowIndex

Screenshot

DeleteWorksheet

Allows users to delete worksheet from a specified excel.

Input: SheetName

Screenshot

FindAndReplace

Allows users to find and replace from a specified Excel Sheet.

Input: SheetName, FindText, ReplaceTo

Screenshot

GetActiveSheetName

Allows users to find the name of the active worksheet in a specified Excel.

Screenshot

GetCellAddressIfRowHas

Allows users to find a specified search value in a specified column using the search functionality.

Input: SheetName, SearchText, SearchColumn, StartRowIndex

Screenshot

User can provide search column name where address is to be retrieved (the user has to double click on the component to add/delete value of column names).

Screenshot

GetCellFormula

Allows users to get the formula from specified cell in excel.

Input: SheetName, CellName

Screenshot

GetCellValue

Allows users to find the cell value of a specified cell in a worksheet.

Input: SheetName, CellName

Screenshot

Double click on title bar of the GetCellValue then SelectvalueType window will display.

Select the value type from dropdown list and click Ok.

Screenshot

GetCellValueIfRowHas

Allows users to find a specified search value in a specified column using the search functionality.

Input: SheetName, SearchText, SearchColumn, StartRowIndex

Screenshot

Users can provide search column names and value column names where data is to be retrieved (double click on the component to add/delete value of column names).

Screenshot

GetCellValues

Allows users to retrieve values of multiple cells at a time.

Input: SheetName, CellName & Index

Screenshot

Users can double click on this component to add/delete cell indexes as shown below and select the value type from drop down list.

Screenshot

GetCellColor

Allows users to find the cell color of a specified cell in a worksheet.

Input: SheetName, CellName

Screenshot

GetFilterData

Allows users to retrieve filtered data from specified worksheet.

Input: SheetName

Screenshot

GetFilteredDataAddress

Allow users to retrieve the filtered data addresses from specified worksheet.

Input: SheetName

Screenshot

GetRowValues

Allow users to retrieve row values from specified worksheet.

Input: SheetName, RowIndex

Screenshot

Screenshot

GetSelectedRange

Allows users to get the selected range from a specified Excel.

Input: SheetName

Screenshot

GetSheetNames

Allows users to get all the sheet names from a specified Excel.

Screenshot

GetUsedRange

Allows users to get the cell range which is used in the specified worksheet.

Input:SheetName

Screenshot

GetValuesByColumn

Allows users to get multiple values of single column at a time.

Input: SheetName, ColumnName, Distinct, ValueType

Screenshot

InsertNewColumn

Allows users to insert a new column to a specified worksheet.

Input: SheetName, ColumnIndex

Screenshot

InsertNewRow

Allows users to insert a new row to a specified worksheet.

Input: SheetName, RowIndex

Screenshot

IsOpen

Allows users to verify if the specified excel is currently open or closed and manipulate based on the output.

Input: Filepath

Screenshot

Paste

Allows users to paste data to a specified Cell location.

Input: SheetName, Cell Location

Screenshot

ReadFromRange

Allows users to read data to a specified range.

Input: SheetName, CellRange

Screenshot

ReadFromSheet

Allows users to read data from a specified Excel sheet.

Input: SheetName

Screenshot

RemoveFilter

Allows users to remove the filter in a specified worksheet.

Input: SheetName

Screenshot

RenameSheetName

Allows users to rename an excel sheet.

Input: OldSheetName, NewSheetName

Screenshot

ReSize

Allow users to resize the specified excel sheet.

Input: Left, Top, Width, Height

Screenshot

RowsCount

Allows users to count the number of rows used in a worksheet.

Input: SheetName

Screenshot

RunMacro

Allows users execute a specified macro.

Input: MacroName

Screenshot

Save

Allows users to Save a specific excel sheet.

Screenshot

SaveAs

Allows users to perform Saveas activity to a specific excel sheet.

Input: FileName

Screenshot

SaveCopyAs

Allows users to Save a copy of a specific excel sheet.

Input: FileName

Screenshot

SelectRange

Allows users to select a specific range from a worksheet.

Input: SheetName, CellRange

Screenshot

SetActiveWorksheet

Allows users to set a specified worksheet as current.

Input: SheetName

Screenshot

SetAlignment

Allow users to set the alignment in specified worksheet.

Input: SheetName, CellRange, HorizontalAlignment, VerticalAlignment

Screenshot

SetCellColor

Allows users to set a specific color to a specified cell name.

Input: SheetName, CellName, Color

Screenshot

Double click on the title bar of the setCellColor the port properties window will display.

Select the color from static value dropdownlist.

Screenshot

SetCellFormula

Allows users to set a formula to a specified cell.

Input: SheetName, CellName, Formula

Screenshot

SetCellValue

Allows users to set a value to a specified cell.

Input: SheetName, CellName, Value

Screenshot

SetCellValues

Allows users to set values to multiple cells at a time. Users can double click on this component to add/delete cell indexes as show below.

Input: SheetName, CellName & Index

Screenshot

Screenshot

SetCellValuesIfRowHas

Allows users to set values in a specifies column using the search functionality. User can provide search column and value column names where data is to be set.

Input: SheetName, SearchText, SearchColumn, StartRowIndex

Screenshot

(double click on the component to add/delete value of column names).

Screenshot

Screenshot

SetColumnType

Allows users to set column type in specified worksheet.

Input: SheetName, FormatType, ColumnName

Screenshot

SetColumnWidth

Allows users to set column width in specified worksheet.

Input: SheetName, Width, Start Column, EndColumn

Screenshot

SetFilter

Allows users to Set Filter Value to column in specified worksheet.

To select only blank fields FilterValue as “=”.

To select all values ‘FilterValue’ as “<>”.

To set Number Filters(Ex: Salary data) then FilterValue as “<50,000” (<, <=, =,>)

Input: SheetName, ColumnName, FilterValue

Screenshot

SetFontStyle

Allows users to set font style in the specified worksheet.

Input: SheetName, CellRange, Color, FontSize, FontName, Bold, Underline

Screenshot

SetRowHeight

Allows users to set the row height in specified worksheet.

Input: SheetName, Height, StartIndex, EndIndex

Screenshot

SetRowValues

Allows users to set the multiple cell values at a time.

Input: SheetName, RowIndex

Screenshot

Users can double click on this component to add/delete column as show below.

Screenshot

Screenshot

Sort

Allows users to sort the data in the specified worksheet.

Input: SheetName, CellRange, ColumnName, SortingOrder

Screenshot

WrapText

Allows users to set row as wrap in the specified worksheet.

Input: SheetName, RowIndex

Screenshot

WriteToRange

Allows users to write data to a specified range.

Input: SheetName, CellName, Data

Screenshot