Microsoft Excel connector methods

  • Release version: Washingtondc
  • Updated February 1, 2024
  • 10 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of Microsoft Excel connector methods

    The Microsoft Excel connector methods allow users to interact with Excel documents within automation flows. These methods enable creating, modifying, and retrieving data from Excel files, streamlining integration with other applications.

    Show full answer Show less

    Key Features

    • Open: Creates or opens an existing Excel file. Must be executed first.
    • AddNewWorkSheet: Adds a new worksheet to the opened file.
    • GetSheetNames: Retrieves the names of worksheets in the opened document.
    • Close: Closes the active Excel document, with an option to save changes.
    • ColumnsCount: Returns the number of columns in a specified worksheet.
    • Copy: Copies cell contents from a specified range.
    • CreateChart: Generates charts based on specified data.
    • DeleteColumn/DeleteRow: Removes specified columns or rows from a worksheet.
    • ExportToPDF: Exports the document to a PDF format.
    • FindAndReplace: Finds and replaces text in a specified worksheet.
    • WriteToRange: Writes data to a specified range in the worksheet.
    • Save: Saves modifications to the Excel document.

    Key Outcomes

    By utilizing these methods, ServiceNow customers can efficiently manage Excel documents, automate data processing tasks, and enhance reporting capabilities. This integration facilitates better data handling and decision-making processes within enterprise applications.

    The Microsoft Excel connector methods perform various actions on Microsoft Excel documents. In an automation flow, the methods are connected with other methods and components. To use the methods, you must first expose them.

    Open

    Creates or opens an existing Microsoft file depending on the inputs.
    Note:
    You must execute this method before any other method.
    Table 1. Open method parameters
    Parameter Description Data port type Data type Default value Mandatory?
    Filepath

    Path to the existing file that the method opens.

    To create a file, you must set the value of the CreateNewFile parameter to True.

    Note:
    The path must contain the filename.
    		 Data in String None Yes
    CreateNewFile Creates a file.

    To create a file, set the value to True and provide the filepath and the filename in the Filepath parameter.

    		 Data in Boolean None No
    DisplayAlerts Displays an alert in the task bar when a file is opened or created.

    To display an alert, set the value to True and then the value of the IsVisible parameter to True.

    		 Data in Boolean None No
    IsVisible Works with the DisplayAlerts parameter to display an alert in the task bar when a file is opened or created.

    To display an alert, set the value to True and then the value of the DisplayAlerts parameter to True.

    		 Data in Boolean None No
    ReadOnlyMode If the value is True, the method opens the file in a read-only mode, else, opens the file in an editable mode. Data in Boolean None No
    Password Use a password if the document is password protected. Data in String None No
    Return If the value is True, the file is opened or created, else, the value is False. Data out Boolean Not applicable Not applicable

    AddNewWorkSheet

    Adds a worksheet to the file.
    Note:
    You must execute the Open method before executing this method.
    Table 2. AddNewWorkSheet method parameters
    Parameter Description Data port type Data type Default value Mandatory? Notes
    SheetName Name of the worksheet that the method adds to the file. Data in String None Yes

    GetSheetNames

    Returns the names of the sheets in the opened Microsoft Excel document.
    Note:
    You must execute the Open method before executing this method.
    Table 3. GetSheetNames method parameters
    Parameter Description Data port type Data type Default value Mandatory? Notes
    Return Returns the names of the sheets in the opened Microsoft Excel document. Data Out String No default value Not applicable

    Close

    Closes the active Microsoft Excel document.
    Note:
    You must execute the Open method before executing this method.
    Table 4. Close method parameters
    Parameter Description Data port type Data type Default value Mandatory? Notes
    saveChanges If the value is set to True, changes to the file are saved before closing. Else, if set to False, no changes are saved. Data in Boolean None No

    ColumnsCount

    Returns the total count of columns in a worksheet of the active Microsoft Excel file.
    Note:
    You must execute the Open method before executing this method.
    Table 5. ColumnsCount method parameters
    Parameter Description Data port type Data type Default value Mandatory? Notes
    SheetName Name of the sheet that the method uses to count the number of columns. Data in String None Yes
    Return Returns the count of columns in the worksheet. Data out Integer Not applicable Not applicable

    Copy

    Copies the contents from a range of cells in a worksheet.
    Tip:
    You can paste the copied contents with the Paste method.
    Table 6. Copy method parameters
    Parameter Description Data port type Data type Default value Mandatory? Notes
    Sheetname Name of the sheet from which the method copies the cell contents. Data in String None Yes
    Cellrange Range of cells from which the method copies the contents. Data in String None Yes

    CreateChart

    Creates the specified chart type on the opened Microsoft Excel document.
    Note:
    You must execute the Open method before this method.
    Table 7. CreateChart method parameters
    Parameter Description Data port type Data type Default value Mandatory? Notes
    SheetName Name of the sheet on which the chart is created. Data In String No default value Yes
    CellRange Range of cells from which the method takes the data to create a chart. Data In String No default value Yes
    ChartType Type of chart that will be created. Data In ExcelChartType No default value Yes
    Return If True, the chart was successfully created. Else, if False, the chart creation failed. Data Out Boolean No default value Not applicable

    DeleteColumn

    Deletes a column based on the specified index.

    Table 8. DeleteColumn method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the sheet from which the column is deleted. Data In String No default value Yes
    ColumnIndex Index of the column that the method will delete. Data In Integer No default value Yes
    Return Returns the Boolean value that indicates whether the column is deleted. If True, the column is deleted. Else, if False, the column was not deleted. Data Out Boolean Not applicable Not applicable

    DeleteContentFromRange

    Deletes contents from the specified range of cells.

    Table 9. DeleteContentFromRange method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the sheet from which the contents will be deleted. Data In String No default value Yes
    CellRange Range of cells under the column. Data In String No default value Yes
    Return Returns the Boolean value of whether the contents were deleted. Data Out Boolean Not applicable Not applicable

    DeleteEmptyColumns

    Deletes columns with no contents.

    Table 10. DeleteEmptyColumns method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the sheet containing the empty column. Data In String No default value Yes

    DeleteEmptyRows

    Deletes empty rows from the specified worksheet.

    Table 11. DeleteEmptyColumns method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the sheet containing the empty rows. Data In String No default value Yes

    DeleteRow

    Deletes the row based on the specified index.

    Table 12. DeleteRow method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the sheet from which the rows will be deleted. Data In String No default value Yes
    RowIndex Index of the row which will be deleted. Data In Integer No default value Yes
    Return Returns the Boolean value that indicates whether the row was deleted. Data Out Boolean Not applicable Not applicable

    DeleteWorksheet

    Deletes the specified worksheet.

    Table 13. DeleteWorksheet method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the sheet which will be deleted. Data In String No default value Yes
    Return Returns the Boolean value that indicates whether the worksheet was deleted. Data Out Boolean Not applicable Not applicable

    ExportChartAsImages

    Exports the chart in the specified worksheet as an image to the specified location.

    Table 14. ExportChartAsImages method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the sheet that contains the chart. Data In String No default value Yes
    SaveFolderpath Path to the local drive where the chart will be saved. Data In String No default value Not applicable

    ExportToPDF

    Exports the contents in the opened Excel document to PDF.

    Table 15. ExportChartAsImages method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    Filepath Path to the local drive where the PDF is exported. Data In String No default value Yes

    FindAndReplace

    Finds and replaces text in the specified worksheet.

    Table 16. FindAndReplace method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the worksheet where the text will be replaced. Data In String No default value Yes
    FindText Text that will be replaced. Data In String No default value Yes
    ReplaceTo Text that will replace the existing text. Data In String No default value Yes
    Return Returns the Boolean value that indicates whether the text was replaced. Data Out Boolean Not applicable Not applicable

    GetActiveSheetName

    Returns the name of the active worksheet.

    Table 17. GetActiveSheetName method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    Return Returns the name of the active worksheet. Data Out String No default value Not applicable

    InsertNewColumn

    Inserts a new column at the specified index in the worksheet.

    Table 18. InsertNewColumn method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the worksheet where the column will be inserted. Data In String No default value Yes
    ColumnIndex Index of the new column. The new column will be inserted at the specified index. Data In Integer No default value Yes

    InsertNewRow

    Inserts a new row at the specified index in the specified worksheet.

    Table 19. InsertNewRow method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the worksheet where the row will be inserted. Data In String No default value Yes
    RowIndex Index of the row. The row will be inserted at the specified index. Data In Integer No default value Yes
    Return Returns the Boolean value that indicates whether a row was inserted. If True, the row was inserted. Else, if False, the row was not inserted. Data Out Boolean Not applicable Not applicable

    InsertObject

    Inserts an object into the specified cell of a sheet in an Excel file. Example of an object could be an excel file, an image, or a PDF file.
    Note:
    Execute the Open method before executing this method.
    Table 20. InsertObject method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Excel sheet that contains the cell where the method inserts the object. Data In String NA Yes
    CellName Name of the cell where the method inserts the object. For example, A1. Data In String NA Yes
    Filepath File path of the object that the method inserts. Data In String NA Yes

    IsOpen

    Returns the Boolean value on whether the specified excel file is open.

    Table 21. IsOpen method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    Filepath Path to the Excel file in the local drive. Data In String No default value Yes
    Return Returns the Boolean value that indicates whether the Excel file is open. Data Out Boolean Not applicable Not applicable

    MergeCells

    Merges the cells specified in the range.

    Table 22. MergeCells method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    Sheetname Name of the worksheet on which the cells will be merged. Data In String No default value Yes
    CellRange Range of cells. All cells within the range will be merged. Data In String No default value Yes

    ReadFromRange

    Reads data from the specified worksheet and returns the data.

    Table 23. ReadFromRange method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the worksheet from where the method will read the data. Data In String No default value Yes
    CellRange Range of cells. The method will read data from these cells. Data In String No default value Yes
    FirstRowAsHeader Boolean value that indicates whether the method treats the first row as the header. If True, the first row is treated as a header. Else, if False, it doesn't treat the first row as a header. Data In Boolean No default value No
    Return Returns the data table that comprises the data from the cells. Data Out Data Table Not applicable No

    ReadFromSheet

    Returns all the data in the specified worksheet.

    Table 24. MergeCells method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    Sheetname Name of the worksheet from which the method will read the data. Data In String No default value Yes
    Return Returns all the data in the specified worksheet. Data Out DataTable Not applicable Not applicable

    RenameSheetName

    Renames an existing worksheet.

    Table 25. RenameSheetName method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    OldSheetName The name of the existing worksheet. Data In String No default value Yes
    NewSheetName The new name of the worksheet. Data In String No default value Yes
    Return Returns the Boolean response that indicates whether the worksheet was renamed. Data Out Boolean No default value Not applicable

    RowsCount

    Returns the count of rows in the specified worksheet.

    Table 26. RenameSheetName method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the worksheet from where the count of rows will be returned. Data In String No default value Yes
    Return Returns the number of rows. Data Out Integer Not applicable Not applicable

    RemoveFilter

    Removes all the filters set in the Excel document.

    Table 27. RemoveFilter method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    Sheetname Name of the worksheet from which the method will remove the filters. Data In String No default value Yes

    Save

    Saves the changes to the Excel document.

    WriteToRange

    Writes data to a specified range.

    Table 28. RemoveFilter method parameters
    Parameter Description Data Port Type Data type Default value Mandatory? Notes
    SheetName Name of the worksheet on which the data is written. Data In String No default value Yes
    CellName Name of the cell on which the data is written. Data In String No default value Yes
    Data Data that will be written. Data In Data Table No default value Yes
    IncludeHeaders Boolean value that indicates whether the headers in the data table will be added. Data In Boolean No default value No