Skip to main content

Posts

Showing posts with the label Excel

Excel Automation Using UiPath

You can do Excel Automation in UiPath in four different ways: Using UiPath Activities. Using LinQ. Using Macros (VBA). Using Excel as a Database. Lets go with one by one : Using UiPath Activities Excel Application Scope: In this You can use Read Range Activity to read all the data into the DataTable. You can use the Read Cell Activity to read the data one by one into the Generic Format. You can use Loops on your DataTable. You can Filter DataTable Activity to filter out your data. Workbook: Even if excel is not installed in your system, then also you can use these activities to read or write excel. Using  LINQ You can use LinQ queries to Filter out the data directly on DataTable or List. In this case you cannot use Read Cell Activity. Read More About LinQ from here: Click here LinQ Syntax . LinQ Queries Used for Filter Out the Data . Using Macros (VBA) You can use macros also, not for Excel but you can do any type of work from Macros. VBA code is easy like a E...

Rename WorkSheet in Excel using VB Code || UiPath

We are going to write the full Vb code, so before doing so you have to set these settings, - Using Invoke Code Activity where you will write this code. - Use these arguments just like it mentioned in below picture. - Copy the code below and Paste it into your invoke code activity. Try app = New Microsoft.Office.Interop.Excel.ApplicationClass workbook= app.Workbooks.Open(Filename.Trim) RenameSuccessful=True  For Each worksheet In workbook.Sheets         If worksheet.Name = Sheetname.Trim Then             sheet_found = True             Exit For         Else             sheet_found = False         End If     Next     If sheet_found = True Then worksheet = CType(workbook.Worksheets(Sheetname.Trim),Microsoft.Office.Interop.Excel.Worksheet) worksheet.Name = NewWorkSheetName workbook.Save Else ...

Using Excel As Database in UiPath

We will use this method in UiPath to access the Excel as a Database and for that we need OLEDB Engine. You can download it from here : So what is OLE DB ? OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB), an API designed by Microsoft, allows accessing data from a variety of sources in a uniform manner. Follow these steps to setup connection and used the excel as a database: First of all, use the Activity Connect to Configure Connection and create its variable "conn" FileName: Full Path of Excel File  Connection String: Provider=Microsoft.ACE.OLEDB.12.0; Data Source=FileName; Extended Properties='Excel 12.0 XML;HDR=YES;ReadOnly=False'  Provider Name: System.Data.OleDb HDR=Yes -- Indicates that the first row contains column names.  Then use the Activity Execute Query and pass the connection String variable in its existing Connection and write query like this. Then you can write the dat...

Some Common LinQ used in Excel Automation

LinQ used on DataTable to Filter One Column and Copy the Data into DataTable : dataTable.Select.where(Function(x) (x.Item("Column1").ToString.ToLower.Contains("Value"))).copyToDatatable. LinQ used on DataTable to Filter More than one Column and Copy the Data into DataTable: dataTable.Select.where(Function(x)(x.Item("Column1").ToString.ToLower.Contains("Value1") Or x.Item("Column2").ToString.ToLower.Contains("Value2"))).copyToDatatable. LinQ used on DataTable to one Column and Copy the Data into List of DataRows: dataTable.Select.Where(Function(x) (x.Item("Column1").ToString.ToLower.Contains("Value"))).ToList() LinQ used on DataTable to one Column and Copy the Data into List/DataTable: dataTable.Select("[Column]='Value'") LinQ used on DataTable to one Column and check whether value present in that Column or not: dataTable.AsEnumerable().Any(Function(x) x("Column...

Understand the LinQ Syntax in Automation

LINQ means Language Integrated Query. Import the System.Linq namespace and it provides many extension methods. LINQ enables you to query data from a SQL Server database, XML, in-memory arrays and collections, ADO.NET datasets, or any other remote or local data source that supports LINQ. We are going to understand the basic Syntax of LINQ query to Filter One Column of Excel. For Example:  We have an Excel Sheet, which we already read by using activity ' Read Range in UiPath ' then all the data come into DataTable  ( Display Info in Tabular form Or Combination of Rows and Columns) DataTable.Select.Where(Function(x) x.Item("ColumnName").ToString.Trim.ToLower.Equals("y")) Now, Let's understand this LinQ query step by step: DataTable: The Excel you read comes into DataTable variable. Select: It Defines the results to return from the Query. Where: Specifies the Filtering Condition for the Query. Function(x): An anonymous (unnamed) ...

Excel Shortcuts Cheat Sheet, Used in Automation

 F1 Displays the Office Assistant or (Help > Microsoft Excel Help)  F2 Edits the active cell, putting the cursor at the end  F3 Displays the (Insert > Name > Paste) dialog box  F4 Repeats the last worksheet action (Edit > Repeat)  F5 Displays the (Edit > GoTo) dialog box  F6 Moves to the next pane in a workbook (if the window is split)  F7 Displays the (Tools > Spelling) dialog box  F8 Toggles whether to extend a selection with the arrow keys  F9 Calculates All the worksheets in All the open workbooks  F10 Toggles the activation of the Menu Bar  F11 Displays the (Insert > Chart) dialog box that creates a chart (on a chart sheet) using the highlighted range  F12 Displays the (File > Save As) dialog box. --------------------------------------------------------------------------------------------------------------------------  Shift + F2 Inserts or edits a cell comment (Insert > Comment)  Shift + F3 Displays the (Insert > Functio...

Find the Last Row, Column, in Excel using VBA

It will help you to find out last row count, column count then you can easily loop through your data according to requirement. Let's suppose your Excel contains blank cells then there is a different Code for that, you can easily use this in Visual Basic of Excel and Execute that in UiPath using activity Execute Macro. ***LastRow**** Range("A1").Select / Cells(1,1).Select lastrow = Range(Selection, ActiveCell.End(xlDown)).Rows.Count lastrow = ActiveSheet.UsedRange.Rows.Count ***LastColumn*** Range("A1").Select / Cells(1,1).Select lastcol = Range(ActiveCell.End(xlToRight), Selection).Columns.Count lastcol = ActiveSheet.UsedRange.Columns.Count ***LastRow With Spaces*** xIndex = Application.ActiveCell.Column xRowIndex = Application.ActiveSheet.Cells(Rows.Count, xIndex).End(xlUp).Row Range(Cells(1, xIndex), Cells(xRowIndex, xIndex)).Select lastrow = Selection.Count ***LastCol With Spaces*** xIndex = Application.ActiveCell.Row xCol...