Write Range

UiPath.MicrosoftOffice365.Activities.Excel.WriteRangeConnections

Description

Writes a DataTable variable to a specified Excel workbook cell range.

Scopes

This activity requires the following scopes:

• Files.ReadWrite
• Files.ReadWrite.All
• Sites.ReadWrite.All

Project compatibility

Windows | Cross-platform

Configuration

• Microsoft OneDrive & SharePoint connection - The connection for the Microsoft 365 account to use in the activity.
• Workbook - Select the Excel file to write in. You can also use a variable and define a workbook template. With a template, the activity can generate the ranges dropdown list. The method to specify the folder to be retrieved: Browse, Enter Id, Enter Url. You can also select a workbook template.
  • Workbook sample - Select a file with the same ranges as the variable file. The field is visible after clicking Use workbook template. Browse the OneDrive folders and select a workbook. In Studio Desktop, the option is available from the Plus button menu next to Range. This field accepts IResource input. To learn more, see Working with templates.
  • Browse option - Click the Folder icon to browse the OneDrive or SharePoint folders and select a spreadsheet. This option accepts input as IResource.

    • Use the Plus button menu to use a variable or select the Reload Browser option if you've modified your folders and need to retrieve the latest data.

  • Enter Id option:
    • Workbook Id - The ID of the workbook of interest. This property supports String variables and string formatted values.
    • SharePoint site address - The URL of the SharePoint site that contains the file or folder. This property supports String variables and string formatted values. Open your SharePoint site in a browser and copy its URL, e.g., https://yourcompany.sharepoint.com/sites/ProductOfficial.
    • SharePoint document library - The document library within the indicated SharePoint site that contains the file or folder. This property supports String variables and string formatted values. Open the Documents tab in your SharePoint site and copy the URL, e.g., https://yourcompany.sharepoint.com/sites/ProductOfficial/Shared%20Documents/Forms/AllItems.aspx.
  • Enter Url option:
    • Workbook Url - The URL of the workbook of interest. This property supports String variables and string formatted values. Retrieve the URL by opening the file in a browser.
• Range - Indicate in which Excel Range to write or append the data. You can add a custom range, such as Sheet1!C5:F10, or a named range name, e.g. Clients. This field is prepopulated with information retrieved from the selected spreadsheet or spreadsheet sample. This property supports String variables and string formatted values.

  • Select the Update Ranges option if you've modified your sheets and need to retrieve the latest data.

• Include headers - This property supports Boolean values. Set to True to also include the DataTable headers into the workbook.

• How to write - Specifies how to add the data to the specified range. The available options are:
  • Overwrite - Overwrite any previous data that exists.
  • Append - Write the data in the first empty row found.
  • Append Right - Write the data in the first empty column found. See the Appending section below for more information.
  • Insert - Insert a row.

• What to write - The DataTable data that will be written in the workbook. This field supports DataTable variables. For example, you can use the output variable of a Read range activity.

Note: Some special character combinations in the Sheet/Range name property can cause the activity to throw an error. For example, Sheet%Test is a valid input, but Sheet%44 isn’t (%44 is transformed into D).

Appending

Appending data does not currently support named ranges.

	Append (to the bottom)	Append Right
Plain range (A1 notation)	Write the range to the first empty row found in the targeted range. It creates the number of rows necessary and fills in the range with the input range. If there isn't an empty row in the range, append the range to the bottom by inserting the necessary number of rows and adding the input range.	Write the range to the first empty column found in the targeted range. It creates the number of columns necessary and fills in the range with the input range. If there isn't an empty column in the range, append the range to the right by inserting the necessary number of columns and adding the input range.
Named range	Write the range to the first empty row found in the targeted range. It creates the number of rows necessary and fills in the range with the input range. If there isn't an empty row in the range, append the range to the bottom by inserting the necessary number of rows and adding the input range.	Write the range to the first empty row found in the targeted range. It creates the number of rows necessary and fills in the range with the input range. If there isn't an empty row in the range, append the range to the bottom by inserting the necessary number of rows and adding the input range.
Sheets	Write the range to the first empty row found in the targeted sheet. It creates the number of rows necessary and fills in the range with the input range. If there isn't an empty row in the range, append the range to the bottom by inserting the necessary number of rows and adding the input range.	Write the range to the first empty column found in the targeted sheet. It creates the number of columns necessary and fills in the range with the input range. If there isn't an empty column in the range, append the range to the right by inserting the necessary number of columns and adding the input range.

Known limitations for requests

Be aware of request/response limits when working with ranges. The limit stands at 4 MB, e.g., on average 10,000 cells with 20 characters each for Read requests, 4,000 cells with 20 characters each for Write requests. For more information, see Excel specifications and limits.

Example

See also:

• Excel Online activities

• Create Workbook
• Delete Sheet
• Read Cell