In the previous practice exercise, you build a workflow using the Read Range and Append Range activity to read data from a workbook and append that data to another workbook. In this video, you will understand Excel Application Scope. Let's get started. The integration with Excel is enabled by using an Excel Application Scope container. All the other activities used to work with a specified Excel file are placed inside the container. The purpose of the container is to open the Excel workbook and provide a scope for Excel activities. When the execution ends, the specified workbook and the Excel application are closed. It can be configured to write the output of the activities in the container in a different file. There are some important properties of Excel Application Scope. The Workbook path property is used for the full path to the Excel workbook. The Read-only property helps prevent editing or writing to the file in scope. The Visible property if checked, helps open and read the file using Microsoft Excel. There are some activities specific to Excel automation that are valid only if placed inside an Excel Application Scope activity. Now let's have a brief understanding of those activities. Range, these activities can read data, insert, and delete rows and columns, and even copy and paste entire ranges. They are similar to the corresponding activities under DataTable, but they work directly in the Excel file. The activities under this category are : Delete Columns, Insert Columns, Insert and Delete Columns, Insert or Delete Rows, Select Range, Get Selected Range, Delete Range, Auto Fill Range, Copy Paste Range, Lookup Range, Remove Duplicate Range. Delete Column, removes a column from an Excel file based on the name. Insert Column, inserts a blank column in an Excel file at a certain position. Insert and Delete Columns, either adds blank columns or removes existing columns based on the specified change type. Insert or Delete Rows, either adds blank rows or removes existing rows based on the specified change type. Select Range, selects a specific range in an Excel file. In general, it is paired with another activity that performs a certain manipulation over the selected data. Get Selected Range, outputs a given range as String. Delete Range, removes a specified range from an Excel file. Auto Fill Range, applies a given formula over a given range in an Excel file. Copy Paste Range, copies and pastes an entire range, values, formulas, and formatting from a source sheet to a destination sheet. Lookup Range, searches for a value in all the cells in a given range. Remove Duplicate Range, deletes all duplicate rows from a given range. Table, these activities create, filter, and sort tables directly in an Excel file. The activities under this category are: Filter Table, Sort Table, Create Table. Filter table, applies a filter on all the values from a column in a table inside an Excel file. Once the file is saved, only the rows that meet the filter will be displayed. This activity does not remove the rows that do not meet the criteria, but only hides them. Sort Table, sorts a table in an Excel file based on the values in a given column. Create Table, creates a table with name in a range specified in the Properties panel. File, these activities work directly with the Excel files, either by saving or closing them. The activities under the category are: Close Workbook, Save Workbook. Close workbook closes an open excel workbook. Save workbook, saves changes to the workbook specified in the workbook path property of the excel application scope. It can only be used in the excel application scope activity. Cell color, these activities can capture and modify the background color of cells in excel file. The activities under this category are get cell color, set range color. Get cell color, reads the background color of a given cell in an excel file and stores it as a color variable output. Set range color changes the background color of all the cells in a given range. The input is a color variable. Sheet, these activities can perform various actions over the sheets in an excel file. The activities under the category are : get workbook sheet, get workbook sheets, copy sheet. Get workbook sheet, reads the name of the sheet by its index. Get workbook sheets, extracts the sheet names, and stores them ordered by index. Copy sheet copies a sheet in an excel file, and paste it either in the same excel file or in a different one specified. PivotTable these activities facilitate working with pivot tables in excel files, the activities under this category are refresh pivot table, create pivot table. Refresh pivot table refreshes a pivot table in an excel file. This is useful when the pivot table's source data changes as the table doesn't refresh automatically. Create pivot table, creates a pivot table using a specified sheet and given parameters. Macro, these activities can execute macros already defined in the excel file or invoke macros from other files. These activities work with.xslm files. The activities under this category are execute macro, invoke VBA. Execute macro, executes the macro within a workbook. The workbook file needs to be a macro-enabled workbook. Invoke VBA, invokes a macro from an external file containing VBA code and runs it against an excel file. Now let us understand how to read from an excel spreadsheet. In the first step, open studio, search for an excel application scope activity, and drag-drop it in the designer panel. In the second step, search for a read range activity in the excel category and drag-drop this activity within the excel application scope activity. In the third step, specify the location to read the excel file. In the fourth step, provide the name of the sheet within the excel file. In the fifth step, go to the properties panel and click the output box and type control K. It prompts the user to give a name to the variable. It creates a data table to store the read data. In the first step, open studio, search for an excel application scope activity, and drag-drop it in the designer panel. In the second step, search for a write range activity and drag-drop this activity within the excel application scope activity. In the third step, specify the path of the new excel file to write data. In the fourth step, provide the data of the data table variable with the previous lead read data. On execution, data is written to a new excel. In the next video, you will see a demonstration of how to read and write in excel by comparing the first two columns and inserting results in the third column using excel activities. I would encourage you to go through the demonstration and attempt to repeat it on your own. And that's it for this video. Thanks for watching.
