Global Bilgi RPA (EN)

Excel

Excel

Aktiviteler

Excel Hide Alerts

The Hide Alerts feature prevents popups in Excel operations and keeps the flow going. This feature is available in all related activities within the Excel module.

“True” parameter works when Hide Alerts line is selected and “False” parameter works when it is not selected. It will be sufficient to have the value line selected to prevent the alerts that appear.

Cell Format Alignment

Using the Cell Format Alignment activity, you can improve the visual representation of your data in Excel, align cells and change the text direction. You can also align a column of numbers to the decimal point or to a specific character.

Cell Format Border

With the Cell Format Border activity, you can add borders to cells in various styles and colors to highlight or define parts of a worksheet or table in Excel.

Cell Format Font

The Cell Format Font activity is used to format text or numbers and change the default formats. You can also change the font color, style, size, text alignment in a cell or apply formatting effects, and make the text or number more visible.

Cell Format Set

Using the Cell Format Set activity, you can change the desired format in Excel cells. For example, if you want to convert a value in the “Currency” format to the “Number” format, you can use the Cell Format Set activity.

Cell Value Clear

The Cell Value Clear activity is used to clear the data in the cell.

Cell Value Copy

The Cell Value Copy activity is used to copy the data in the cell to the clipboard. In case you select the “Paste After” line as “True”, you can paste the copied data into a different Excel or sheet, if you do not leave the “Paste After” line selected (False), you can only copy and use the copied data in the scenario.

Note: If there is any Excel object (Chart, Picture, Shapes, etc.) in the given cell range or cell, you can copy and paste it. If you want to copy the entire column or a specific field, it will be sufficient to write as in the example.

Example 1: “A:A” or “A:D”

Example 2: “A1:A10” or “A1:D10”

Cell Value Cut

The Cell Value Cut activity is used to cut the data in the specified cell and copy it to the clipboard. If you select the “Paste After” line as “True”, you can paste the cut data into a different Excel or sheet, if you do not leave the “Paste After” line selected (False), you can only cut it.

Note: If there is any Excel object (Chart, Picture, Shapes, etc.) in the given cell range or cell, you can cut and paste it. If you want to cut the entire column or a specific area, it will be enough to write as in the example.

Example 1: “A:A” or “A:D”

Example 2: “A1:A10” or “A1:D10”

Cell Value Paste

The Cell Value Paste activity is used to paste the data copied to the clipboard into the specified cell.

Cell Value Paste Special

When you want to paste a copied data into a different sheet or excel file, you can use the “Paste Special” feature in excel with the Cell Value Paste Special activity.

Input Parametreleri

Workbook Name: Excel adı

Worksheet Name: Sheet adı

Cell: Satır bilgisi

Paste Type, Operation Type, Is Transpose, Is Skip Blanks are the same as their usage in Excel.

Note:

If no selection is made, the Paste Type default value will be “All” and the Operation Type default value will be “None”.

Cell Value Read

The Cell Value Read activity is used to read the data in the specified cell. You can transfer the read data to a variable and use it in the scenario.

When the Trim Cell Value line is selected as “True”, if there are spaces on the right or left of the read data, you can delete these spaces and transfer them to the variable.

Cell Value Write

The Cell Value Write activity is used to enter data into the specified cell.

Collection To Excel

The Collection to Excel activity is used to convert the data in the collection variable to Excel format. You need to enter the sheet name you want the Collection to be written into the Sheetname parameter, and the Collection variable you want to be written to the Collection parameter.

When you mark the Trim Cell Value parameter, if there are spaces in the cells in the collection variable, Excel trims these cells and deletes the leading and trailing spaces before writing them to Excel.

Collection Write Excel

With the Collection Write Excel activity, you can print the data you have received in the collection variable into any cell of any Excel you want.

Column Auto Fit

Using the Excel Column Auto Fit activity, you can increase the width of the specified column range or a single column in Excel according to the maximum font size in that column.

Column Delete

The Column Delete activity is used to delete the specified column or columns.

Column Sort

With the Column Sort activity, you can use the values in the specified column to sort from A to Z, Z to A, or the smallest to the largest, or the largest to the smallest. You can sort in descending order when the Sort Descending line is selected as “True” and ascending when it is left as “False”. If the column you want to sort has a header and you want it to remain constant, the Has Header line should be selected as “True”.

Create Pivot Table

The Create Pivot Table activity allows you to calculate, summarize, and analyze data to see comparisons, patterns, and trends in your data. The following steps should be observed while using the activity.

  • When performing pivot operation, the range of the table should be checked and written correctly.
  • If there is values in the Column field, the Move Sum Values To Column line should be selected as “True”.
  • If there are values in the Rows field, the Move Sum Values To Column line should be left as “False”.
  • The following values should be written correctly in the Pivot Data Field And Function line and abbreviations should not be used.

 

  • Average
  • Count
  • Countnums
  • Distinctcount
  • Max
  • Min
  • Product
  • Stdev
  • Stdevp
  • Sum
  • Unknown
  • Var
  • Varp

Custom Auto Filter

Using the Custom Auto Filter activity, you can quickly find values by filtering the information in the worksheet. With filtering, you can control not only what you want to see but also what you want to exclude, or you can create specific filters to focus on exactly the data you want to see.

Custom Sort

Using the Custom Sort Activity, you can sort through the Cell Values, Cell Color and Font Color options according to a certain range. Sort Descending or Bottom parameter works as “True” by default. Sorting takes place from the smallest to the largest. If you want to perform the sorting process from the largest to the smallest, the Sort Descending or Bottom line must be selected.

If more than one sort operation is to be performed at the same time, the Sort Descending Or Bottom option must be checked. The Column Name line should be left blank and the names of the columns to be sorted should be written on the Sort By Multi line with a semicolon “;” between them.

The names of the types to be sorted (Cell Color, Font Color, Cell Values) should be written on the Sort Type Multi line. Semicolon “;” between typed type names must be written in the same order as the column names in the Sort By Multi line.

In the Order Multi line, the ordering directions of the values should be “Top” or “Descending” in the specified columns.

If color sorting is to be done, color codes should be written in parentheses on the Order Multi line. The ordering directions of the values in the specified color code should also be written as “- Top” or “- Descending”.

With the Edit Link activity, you can update the folder index information of another Excel file that is referenced in the opened Excel file. The “Update Values” option must be selected in order to update the values that come after the index information is updated. In order to remove the link between the two files, the “Break Link” option must be checked.

Excel Auto Fill

With the Excel Auto Fill activity, you can fill the desired range of cells with the value in the specified cell. The cell line must contain the cell address with the desired value to be filled, and the cell range in which the values will be entered must be entered in the Cell To Be Filled line.

Excel To Collection

Using the Excel To Collection activity, you can transfer the data contained in your Excel file to a variable without opening it. It is important that the variable you define is in collection format. When the Trim Cell Value line is selected as “True”, if there is a space at the beginning or end of the value, trimming will be done.

Export Excel Chart

Using the Export Excel Chart activity, you can export the charts in Excel in the format of “.bpm”, “.jpg”, “.jepg” or “.png” to the file path you want. If there is more than one chart in the Excel sheet, different files will be created by putting 1,2,3… next to the file name you have given in the Chart Save As parameter.

Filter

The Filter activity allows you to filter data ranges based on criteria you define. You can perform your filtering operations through the Text you write in the Filter Value line or according to the color selection from the Color line. If you are going to make filtering with more than one value, you may perform operation by adding a vertical straight line “|” between the values.

Find Value

With the Find Value activity, you can search for a value in the column while operating on Excel and transfer the information about the location of this value to the variables.

Note: Cell, Column, Cell Number, Only Cell&Column columns are included in the collection variable as the output of the activity. When you want to use cell location information in the scenario, for example, you can use it as ßVariable 1|Only Cell&Column|0ß.

When you check the Is Equals parameter, it brings only the cells that are equal, not the cells containing the value you entered in the Search Value parameter.

Formula

Formula activity is used to run formula in Excel file. When writing a formula in the activity, it is sufficient to write it in the format you use in Excel.

Note: If the formula contains a semicolon “;”, the comma must be replaced with “.” Also, even if the language of the Excel application you are using is Turkish, the formula you write must be in English.

Get Cell Value

With the Get Cell Value activity, you can transfer the data in the specified cell to the variable.

Get Cell Value By Color

With the Get Cell Value By Color activity, you can read cells by color via Excel. It is important to select the variable type as “Collection” while transferring the read value to the variable. If the Is Text Color line is selected as “True”, the text color of the cell is selected, and if it is selected as “False”, the reading process is performed according to the background color of the cell.

For colors that cannot be found on the color picker screen or the tone of which cannot be captured exactly, the code of the desired color must be entered in the Red-Green-Blue fields in the pop-up window. Then, the color selection is completed by clicking the “Add To Custom Colors” button.

In order to determine the color code via Excel, the order in the image should be followed.

Get Checkbox Value

The Get Checkbox Value activity is used to bring the value on the selection box in Excel.

Get Column

Using the Get Column activity, you can access the index number from the name information of a column or the name information from the index number and transfer it to a variable. You can use the Get Column activity to scroll right, left, and down the Find Value activity.

Get Column Value

With the Get Column Value activity, you can get the sum (total), count (numbers), average (average) values of the columns and cells on your Excel files without using formulae and transfer them to variables via output.

Get Formula

Get Formula Activity: With this activity, you can transfer the formula in the cell to a variable in Excel.

Group Pivot Table

Using the Group Pivot Table activity, you can group values of date type in pivot tables created in Excel.

HTML File To Excel

Using the HTML File To Excel activity, you can convert your HTML-formatted files to Excel-type files.

Insert Chart

Insert chart activity allows you to create a new chart with data. With the Chart activity, you can create charts in Pie, Pie3D, Line, Doughnut, Area3D, Clustered Column formats.

While creating the chart, you must first create your chart on Excel and then fill in the relevant fields in the activity. When filling out the inputs in the activity, it is important that you fill in the “Chart Elements” tab of your chart in Excel.

The CheckBoxes in the Chart Elements are based on the fields where the True/False selection is made. For single selections without checkbox, inputs are opened by listing.

Note: Inputs must be filled in accordance with the selected Chart Type. If you fill in an option that is not included in the Chart Elements section of the chart you created in Excel, the activity will give an error because there is no such option. For example, if you fill in the Gridlines input for Pie Type, you will encounter an error because there is no line option in Pie Chart Type.

Insert Column

Using the Insert Column activity, you can add one or more new columns in Excel. To do this, it will be sufficient for you to write the column range in the Column Range line.

Insert Row

With the Insert Row activity, you can add one or more new rows in Excel. In order to be able to do this, it will be sufficient to write the number of the row in the Row Number line, whichever row you want to add a row to.

Macro Run

Using the Macro Run activity, you can run a macro saved in Excel. If the macro you want to run has parameter values, you can use the “Parameter” inputs.

PDF Export

With the Pdf Export activity, you can convert your Excel files to pdf format.

Pivot Change Data Source

With the Excel Change Pivot Source activity, you can change the source range of your Pivot tables.

Pivot Worksheet Name: The name of the Sheet containing the pivot table the source of which will be changed should be written.

Pivot Table Name: The name of the pivot table should be written. For example: “PivotTable1”

New Pivot Worksheet: The name of the Sheet containing the data to be updated should be written.

New Pivot Table Range: This field is only used with row and column values.

Sample writing format: R1C1:R5C2 Data starting from Row 1 Column 1 to Row 5 Column 2.

R: stands for Row information.

C: stands for Column information.

Pivot Filter

With the Pivot Filter activity, you can filter for multiple values in the filters section of your pivot tables. You can find the input values that you will write in the Table Name and Field Name sections in the “Analyze” tab on Excel.

Note: If the field to be filtered is in date format, it is important to set the Filter Format Is Date line to “True”.

Pivot Sort

Using the Pivot Sort activity, you can sort from the largest to the smallest and the smallest to the largest according to the column name you specify on the pivot table. In case Sort Descending being “True”, sorting will be done from the largest to the smallest.

The name of the pivot table should be written in the Pivot Table Name line, the name of the active title in the pivot should be written in the Pivot Field Name line, and the name of the title to be sorted in the Sorting Field Name field.

Using the Print Out activity, you can print out the specified Excel sheet. You can find the details of the activity lines in the table below.

  • Printer: It is the parameter required to print out on the specified printer. When left blank, “Microsoft Print to PDF” will be selected by default.
  • Print Option: It is the parameter to be selected to print out on the specified Sheet or the specified range or the entire Workbook. The default value is “Print Active Sheets”.
  • Page Start: It is the parameter where the starting value of the page(s) to be printed out is specified.
  • Page End: It is the parameter where the end value of the page(s) to be printed out is specified.
  • Collated: It is the parameter for selecting the Collated information. It is Collated when True and Uncollated when False.
  • Orientation: It is the parameter where the page(s) to be printed out are specified vertically or horizontally. The default value is “Portrait”.
  • Paper Size: It is the parameter where the page size of the page(s) to be printed out is selected as A4, A5, B5 etc. Default selections for Office 2016 have been added.
  • Margins: It is the parameter to set the page margins. Its default value is “Normal Margins”.
  • Scaling: It is the parameter in which the scales of the page(s) to be printed out (Fix Sheet On One Page, Fit All Columns On One Page, Fit All Rows On One Page) are determined. The default value is “No Scaling”.
  • File Path: It is the parameter that determines the path information when the page(s) to be printed out is requested to be printed to a file.
  • File Name: It is the parameter in which the file name is determined when the page(s) to be output is requested to be printed to a file.
  •  

Refresh Pivot Table

With the Refresh Pivot Table activity, you can update the changes made in the previously created pivot table.

In order to fill the “Pivot Table Name” field in the activity, the order in the image should be followed.

In the sample image, the name “Joe” has been changed to “Emre”. Afterwards, the changes made with the Refresh Pivot Table are updated in the pivot table.

Remove Duplicates

With the Remove Filters activity, you can remove filters from previously filtered columns in Excel.

Remove Filters

Remove Filters aktivitesiyle, excelde daha önce filtrelenmiş sütunların filtrelerini kaldırabilirsiniz.

Replace Values

With the Replace Value activity, you can change the value in a row or column in your Excel file.

Row Delete

With the Row Delete activity, you can delete one or more rows in Excel.

Set Cell Color

With the Set Cell Color activity, you can change the background color or text color of the specified cell or range of cells in Excel. If “True” is written in the Is Text Color line, you can change the text color of the cell, and if “False” is entered, you can change the color of the cell’s background.

For colors that cannot be found on the color picker screen or the tone of which cannot be captured exactly, the code of the desired color must be entered in the Red-Green-Blue fields in the window that opens, and then the color selection must be completed by clicking the “Add To Custom Colors” button.

In order to determine the color code via Excel, the order in the image should be followed.

Text To Columns

With the Text to Column activity, you can divide a written text into different cells according to their delimited or fixed width properties.

Ungroup Pivot Table

Using the Ungroup Pivot Table activity, you can ungroup the pivot tables you have created.

Workbook Close

The Workbook Close activity is used to close the open Excel file. If the Close Without Save line is selected as “True”, you can close the excel file without saving it.

Workbook Close All

The Workbook Close All activity is used to close all open Excel files.

Workbook Count

With the Workbook Count activity, you can count how many Excel files are open and export them to a variable.

Workbook Create

With the Workbook Create activity, you can create a new Excel file and transfer the filename to the variable. If you want the Excel file you created to open full screen, it will be sufficient to set the Is Full Screen line to “True”.

Workbook Open

The Workbook Open activity is used to open the previously created Excel file. If the file you want to open is saved as encrypted, you can open the file using the Password and Password To Modify lines.

When we want to open an Excel encrypted as Read Only, the password must be entered in the password parameter and if Read Only is true, it must be marked as true in the encrypted Excel. The parameters here must be the same as Excel’s encryption logic. For example, if it is encrypted with modify and there is a password, the password is entered in the modify parameter and Read Only is marked as “False”.

If there is an add-in that is opened while opening the Excel file and you want to turn it off, the DisableAdd Ins line must be selected as “True”. In addition, if you want the file to be opened in the background, it will be sufficient to select the Invisible line as “True”.

Note: After the Excel application is opened in the background as Invisible, it must be closed using the Workbook Close or Workbook Close All activities at the end of the process. Otherwise, since the application remains open in the background, it will give an error when asked to open it again.

After opening the Excel file using the Workbook Open activity, you can transfer the WorkSheet information to a variable. It is important that the variable you will create is in Collection format.

Workbook Refresh All

The Workbook Refresh All activity is used to refresh the Excel sheet.

Workbook Save

The Workbook Save activity is used to save the open Excel sheet. If you want to encrypt the Excel you want to save, password information must be entered in the “Password to Open” parameter.

Workbook Save As

With the Workbook Save As activity, you can save your Excel files under a different name and to the desired destination. You can enter sheet information and cell range during the saving process, select the appropriate one formats from “.html”, “.htm”, “.xlsx”, “.xls” “.xlsm”,”.xlsb” or “.csv” formats from the list in the Save As Type line.

Note: You can open the excel spreadsheets you have saved as HTML with the Open Browser activity without losing the format, transfer the html code information to a variable with the Get Source Code activity and use it in the body line of the SMTP Send Mail activity. It is important that the Is Body Html line of the SMTP Send Mail activity is “True”.

If you want to encrypt Excel that you have saved in save as format, you can use Password to Open, Password to Modify and Read only Recommended parameters.

  • Password to Open: Password information must be written to encrypt the Excel you have saved.
  • Password to Modify: Valid password information must be entered when processing is allowed on Excel.
  • Read only Recommended: It should be marked as True when we want the encrypted Excel to be opened as Read only.

Worksheet Copy

The Worksheet Copy activity is used to move the specified worksheet in the Excel file to a different Excel file after copying it. When using this activity, Excel files must be opened with the Workbook Open activity.

Worksheet Count

With the Worksheet Count activity, you can find out how many worksheets there are in the previously created Excel file and transfer them to the variable.

Worksheet Create

Using the Worksheet Create activity, you can create a new worksheet within the Excel file.

Worksheet Delete

Using the Worksheet Delete activity, you can delete the worksheet you have typed in the Worksheet Name line.

Worksheet Exists

With the Worksheet Exists activity, you can check if a worksheet with the specified name exists in the opened Excel file.

Worksheet Rename

With the Worksheet Rename activity, you can change the existing Worksheet name to a different one.

Worksheet To Collection

The Worksheet to Collection activity is used to convert all data in the Excel file into a collection. If more than one data is to be read from the file and many cell value read activities need to be used, you can transfer the contents of the worksheet to a variable in table format. It is important that the variable you define is in collection format.

In order to be able to use the data in the variable in the activity, it must be written in an appropriate format.

ßVariable 1|Test|2ß
Variable 1: Collection variable name
Test: The name of the column from which data will be retrieved
2: Line number to receive data from

If you want only filtered values to be received, Only Filtered Values line should be selected as “True”. When the Trim Cell Value line is selected as “True”, if there is a space at the beginning or end of the received value, the trim operation will be performed.

Workbook Create Special

Using the Workbook Create Special activity, you can create a new Excel file with any name you want, to any destination you want. Unlike the Workbook Create activity, you can predetermine the destination and name information that you want to save.

Input Parameters

Workbook Name: Excel name

Workbook Path Text: The path to the file where you want to create Excel

Workbook Path: Choosing the file you want to create Excel with

Worksheet Name: Sheet name

Hide Alerts: If you want to hide the alert, it should be chosen as True.

Is Open Workbook: If you want to open the newly created Excel, it should be chosen as True.

Is Full Screen: If you want to open the newly formed Excel full screen, True should be chosen.

Do you want to have Digital Employees?