Excel
Last updated
Last updated
Autom Mate's Excel library offers a powerful set of actions designed to automate and simplify various tasks within Excel workbooks. This comprehensive guide provides detailed instructions on each available action, empowering users to enhance productivity and efficiency in Excel-based workflows.
The Autom Mate Excel Library is a feature-rich toolkit designed to streamline Excel processes, enabling users to automate repetitive tasks, generate reports, and manipulate data seamlessly. By integrating these actions into workflows, users can achieve significant time savings and operational efficiency.
Autom Mate's Excel library is designed to empower users with a robust set of tools for automating, manipulating, and optimizing Excel workbooks. Whether it's automating repetitive tasks, generating insightful reports, or managing data efficiently, this library offers a comprehensive suite of actions to streamline Excel-based workflows.
Before using the Autom Mate Excel Library actions, ensure you have integrated the library into your existing flow.
Description: The Copy Action allows users to duplicate rows, columns, or ranges from an Excel sheet.
Purpose: This action is useful for creating backups of data, manipulating data structures, or preparing data for further analysis.
Usage:
Select the data table variable of the Excel Sheet containing the data to be copied.
Choose the desired option:
"Row" or "Column": Specify the row or column data to copy, entering the corresponding value.
"Cell": Define the range to copy by entering the column and row values.
Assign a variable to store the copied data:
For "Row" or "Column" option: Select an array variable.
For "Cell" option: Select a string variable.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
Range Type: Set Custom Range or Add to last row.
Range: The specific range or cell to copy.
Row contains index: A selectable box to include row index.
Value: The value to be copied.
Description: The Create Pivot Action is used to generate Pivot Table reports from Excel data.
Purpose: This action is helpful for summarizing and analyzing data, creating visual representations, and gaining insights from large datasets.
Usage:
Select the data table variable of the Excel Sheet.
Enter or Select the Pivot Table Range with the corresponding Sheet Name (e.g., Sheet1!$B$1:$F$8).
Specify the column names for Filter, Row Tags, Column Tags, and Values, pressing Enter after each name.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
Enter Table Range: The range for the Pivot Table.
Fields to add to the report: Tags for Filter, Row Tags, Column Tags, and Values.
Description: The Delete Cell Action removes specific cells within an Excel sheet.
Purpose: This action is useful for data cleanup, removing unwanted information, or preparing sheets for further processing.
Usage:
Ensure the Excel source variable type is "data table."
Define the location of the cell(s) to delete in the "range" section.
For example: A-1 for a single range, or A-10:B-20 for multiple ranges.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
Range: The specific cell or range to delete.
Description: The Export to CSV Action saves an Excel sheet as a CSV file.
Purpose: This action is helpful for sharing data with systems that require CSV format, archiving data, or preparing data for external use.
Usage:
Select the data table variable of the Excel Sheet to be exported.
Choose the Separator character for rows and columns.
Enter the Folder Path to save the CSV file (e.g., C:\Autommate\test).
Assign a string variable to store the CSV file path.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
Separator: Comma or Semicolon.
Folder Path: The location to save the CSV file.
Assign file path to variable: A string variable to store the CSV file path.
Description: The Filter Action allows users to filter data within an Excel sheet based on specified criteria.
Purpose: This action is essential for extracting specific subsets of data, performing data cleansing, or preparing data for analysis.
Usage:
Select the data table variable of the Excel Sheet.
Define the value to assign for filtering, using an array variable.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
Value to assign: An array variable with filtering criteria.
Description: The Find Action helps users search for specific values within an Excel sheet.
Purpose: This action is useful for locating specific data points, validating data presence, or identifying trends.
Usage:
Select the data table variable of the Excel Sheet.
Specify the value to search for, using an object variable.
Inputs:
Excel Source: The datatable variable of the Excel Sheet.
Value to assign: An object variable with the search criteria.
Description: This action facilitates the addition or removal of columns within an Excel sheet.
Purpose: Users can adjust the structure of their data, prepare data for analysis, or customize data views.
Usage:
Select the datatable variable of the Excel Sheet.
Choose "Insert Column At" to add new columns or "Delete Column(s) At" to remove columns.
Enter the column value(s) accordingly.
Inputs:
Excel Source: The datatable variable of the Excel Sheet.
Insert Column at: The column(s) to insert.
Delete Column at: The column(s) to delete.
Description: The Insert / Delete Row Action allows users to add or remove rows within an Excel sheet.
Purpose: Users can adjust the length of their datasets, clean up data, or prepare for analysis.
Usage:
Select the datatable variable of the Excel Sheet.
Choose "Insert Row At" to add new rows or "Delete Row(s) At" to remove rows.
Enter the row value(s) accordingly.
Inputs:
Excel Source: The datatable variable of the Excel Sheet.
Insert Row at: The row(s) to insert.
Delete Row at: The row(s) to delete.
Description: The New File Action is used to create a new Excel sheet.
Purpose: Users can generate new workbooks, set up templates, or initialize new data structures.
Usage:
Define the File Name, Folder Path, and Sheet Name for the new Excel sheet.
Assign the file path to a specific string variable.
Inputs:
File Name: The name of the new Excel file.
Folder Path: The location to save the new Excel file.
Sheet Name: The name of the new sheet.
Assign file path to variable: A string variable to store the file path.
Description: The Paste Action is used to insert copied data into an Excel sheet.
Purpose: This action helps in transferring data from one location to another within the same sheet or between sheets.
Usage:
Select the data table variable of the Excel Sheet to paste the data into.
Choose the appropriate option:
For "Row" or "Column": Select an array variable for the data.
For "Cell": Select a string variable for the data.
Specify the location to paste the data.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
Value to Assign: For "Row" or "Column", select an array variable; for "Cell", select a string variable.
For Row:
Add to last row: Selectable box (e.g., 1)
Row Number: Example - 1
For Column:
Column Letter: Example - B
Column Name: Example - Hitchhiker
Description: The Read Sheet Action allows users to retrieve data from an Excel sheet.
Purpose: Users can extract data for analysis, reporting, or further processing within their flows.
Usage:
Specify the File Path of the Excel sheet to read.
Choose the desired Open as option (Read only or Read Write).
Select the Data Format and the Sheet to read.
Inputs:
File Path: The path to the Excel sheet to be read.
Open as: Read only or Read Write.
Date Format: Format of the date values in the sheet.
Sheets: Specify the sheet name(s) to read.
Description: The Run Macro Action executes a Macro script within an Excel sheet.
Purpose: Users can automate complex tasks, apply advanced calculations, or interact with external systems through macros.
Usage:
Select the Excel Source containing the Macro.
Check the "New Query" option if creating a new query.
Specify the Macro Name or select an existing macro.
Enter the File Path of the macro file or input the macro manually.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
New Query: Selectable box for new query creation.
Macro Name: The name of the macro to execute.
Description: The Save Action is used to save changes made to the Excel sheet.
Purpose: Users can persist changes, save results of operations, or finalize data manipulation tasks.
Usage:
Select the data table variable(s) of the Excel Sheet(s) to be saved.
Enter the Folder Path to save the Excel Sheet(s).
Assign a string variable to store the path for future use.
Inputs:
Sheets To Saved: The data table variable(s) of the Excel Sheet(s) to be saved.
Folder Path: The location to save the Excel Sheet(s).
Assign file path to variable: A string variable to store the file path.
Description: The Search Action helps users find specific values within an Excel sheet.
Purpose: Users can locate data points, verify data integrity, or filter for specific information.
Usage:
Specify the Excel Source containing the data to search.
Choose the Operator for the search criteria (e.g., =, is empty, contains).
Enter the Value to search for within the data.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
Operator: The operator for the search criteria.
Value To Search: The value to search for.
Search all data or Search in column:
If searching in a column:
Column To Search: The column to search within.
Value to Assign: An array variable for results.
Description: The Set Cell Formula Action adds formulas to cells within an Excel Sheet.
Purpose: Users can perform calculations, apply formulas, or automate data manipulation tasks.
Usage:
Select the data table variable of the Excel Sheet.
Specify the Cell Range where the formula will be added.
Enter the desired formula.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
Range: The cell range where the formula will be added.
Formula: The formula to apply to the cell range.
Description: The VLookUp Action performs a VLookUp operation within an Excel sheet.
Purpose: Users can search for and retrieve data from a specific table based on a lookup value.
Usage:
Select the data table variable of the Excel Sheet to perform the lookup.
Define the Searchable Column, Search Text, LookUp Data, and Find Column.
Check the Approximate Value option if required.
Inputs:
Excel Source: The data table variable of the Excel Sheet.
Searchable Column: The column to search within.
Searchable Text: The text to search for.
LookUp Data: The data range to search.
Find Column: The column to return the value from.
Approximate Value: Selectable box for approximate match.
Source to be Assigned: A string variable for the VLookUp value.
Description: The Write Cell Action sets a value within a specific cell or range in an Excel Sheet.
Purpose: Users can update data, insert new information, or automate cell value assignments.
Usage:
Select the data table variable of the Excel Sheet.
Choose the appropriate option:
"Set Custom Range": Specify the range for the value to be written.
"Add to Last Row": Write the value in the last row of the specified column.
Enter or choose the Value to be written to the cell(s).
Inputs:
Excel source: The data table variable of the Excel Sheet.
Range type:
Set custom range: The range for the value to be written.
Add to last row: The column to write the value in the last row.
Row Contains Index: Selectable box for row index.
Value: The value to write to the cell(s).
