Skip to main content

Posts

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...

Deleting IE Temporary files, Cookies, History, Closing Browsers

Sometimes all you have to do run this Macro code to clear everything. Then I find this Macro VBA code very helpful as your process always starts from the beginning. You can add this Code in Excel Visual Basic Section and Execute in UiPath using Execute Macro Activity. It will take a few seconds and you are good to go then. It will Delete following from Browsers:  Sub DeleteFiles() Application.DisplayAlerts = False Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 255" ' (Deletes ALL History) Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 1" ' (Deletes History Only)" Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 2" ' (Deletes Cookies Only)" Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8" ' (Deletes Temporary Internet Files Only)" Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 16" ' (Deletes Form Data Only)" Shell "RunDll32.exe InetCpl.cp...

Custom Date and Time Format Strings Used in UiPath

A custom format string consists of one or more custom date and time format specifiers. Any string that is not a standard date and time format string is interpreted as a custom date and time format string. In this, we will cover some formats to COnvert the Date and Time from Different Formats to our format. This is a variable, I am using "vDatVariable= Generic". Convert.ToDateTime(vDatVariable.toString).toString("dd MMM yyyy") Now.toString("dd MM yyyy") First Format: The Date you are fetching, coming in this format Second Format: The format you want. DateTime.ParseExact(vDatVariable.ToString,"dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy") DateTime.ParseExact(vDatVariable.ToString,"dd-MMM-yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo,Globalization.DateTimeStyles.None).ToString("MM/dd/yyyy") For More Info Refer this Link: Click here...

Quick Fast Guide for UiPath Automation

As UiPath  already has so many things to teach on their website, also they have UiPath Academy to teach you everything related to UiPath  but for that you have to see each and every video on academy and then quickly pass the test to get certified.  I go through that course of UiPath academy that takes lot of my time but i learnt so may things and  i am very thankful for that, in the end they showed me some recap Pdf's of what i learnt in my whole course, and i.e. Quick Recap Chapters where i just have to read points and within minutes i can quickly revise all the things. I dont have to wait to go on internet to find some basic solutions, i juts have to read them to know the solution of my problem. And for beginners these are must to read before having hand's on experience on UiPath tool. I am just sharing the whole recap slides and you can easily download from here. Quick Fast Guide For UiPath Thankyou so much.