Approach to find last row or column using VB script in Excel?

We are sharing these techniques so that we can work with dynamic set of data in an Excel.
Finding last row of excel using code


Do follow us for more learning’s Fb,G+,Twitter.

Technique 1:- 
Using UsedRange property:- 
This approach is frequently implemented and one of our favorite. But sometime we have seen that incase some formatting is present in the sheet, it points till the last range of the excel (like 1,048,576 rows in excel -2013) itself rather than the last occupied row of the sheet. 

Technique 2:-
  • Using “CTRL+SHIFT+END” to find the last row 
  • Using “CTRL+SHIFT+Down Arrow” to find the last row 
  • Using “CTRL+SHIFT+left/Right arrow” to find the last column
Code:-
'*******************************************Function*********************************
'Function Name:-        fn_CalRange
'Function Description:- Function to Calculate the last row & last column
'Input Parameters:-     N/A
'Output Parameters:-    N/A
'************************************************************************************
Sub fn_CalRange()
    
    Dim iLastRow
    Set objExcel = CreateObject("Excel.Application")
    'Making Excel visible to user
    objExcel.Visible = True
    Set oCountWB = objExcel.Workbooks.Open("D:\Test.xlsx")
    Set oCountWS = oCountWB.Worksheets("Sheet1")
    
    '****************UsedRange********************
    'Counting the Last row using 'Range' Property
    iLastRow = oCountWS.UsedRange.Rows.Count
    'Counting the Last Column using 'Range' Property
    iLastCol = oCountWS.UsedRange.Columns.Count
    MsgBox "Using Range Property Last Row: " & iLastRow & " Last Column:" & iLastCol
    
    '*****************CTRL+SHIFT+END*************
    '-4162 meaning xlUp
    'Using CTRL+SHIFT+END to find the last row
    iLastRow = oCountWS.Cells(oCountWS.Rows.Count, "A").End("-4162").Row
     MsgBox "Using CTRL+SHIFT+END, Last Row:" & iLastRow
     
     '*****************CTRL+SHIFT+Left Arrow*************
    'Using CTRL+SHIFT+left arrow to find the last Column
    '-4159 for xlToLeft
    iLastCol = oCountWS.Cells(1, oCountWS.Columns.Count).End("-4159").Column
     MsgBox "Using CTRL+SHIFT+Left Arrow,  Last Column:" & iLastCol
     
     '*****************CTRL+SHIFT+Down/Right Arrow************
     iLastRow = oCountWS.Range("A1").CurrentRegion.Rows.Count
     iLastCol = oCountWS.Range("A1").CurrentRegion.Columns.Count
     MsgBox "Using CTRL+SHIFT+Down/Right Arrow,Last Row:" & iLastRow & " Last Column:" & iLastCol
     
End Sub

More Excel Examples

SQL Interview Questions (Set-1)

This is our first set of SQL interview Questions.

Prepare well and crack your dream job. It is said,“Fall seven times, stand up eight.”(Japanese proverb), so work hard to achieve heights.
Work hard to crack SQL interview
Work Hard

Follow us on Facebook for more updates and learning!!!


  1. What are various types of SQL statements?
  2. What is the difference between Truncate, Drop and Delete?
  3. Difference between Local and Global Temp tables?
  4. What are indexes and types of indexes?
  5. How to copy only structure of Table A to Table B?
  6. Meaning of BCP in SQL?


Hints:-

1.
a. Data definition language
  1. Create
  2. Alter
  3. Drop
  4. Truncate

b. Data Manipulation language 
  1. Select
  2. Insert
  3. Delete
  4. Update

c. Data Control Language
  1. Grant 
  2. Revoke

d. Transaction control statements
  1. Commit
  2. Rollback
  3. Save point 


2. Delete, is used to remove rows from a table, we can use where clause to limit the rows to be deleted. After delete we need to perform Commit or Rollback operation to make the changes permanent, thus we can say we have chance to undo the delete operation .It is a DML command. Delete will cause delete triggers on the table to be fired.

Truncate, it is used to remove all rows from a table. The operation cannot be rolled back and no triggers are fired. It is a DDL command and faster as It does not use the concept of undo operation thus even logs are not maintained for the deleted data.

Drop, is more dangerous as it removes table from the database unlike above commands which were only removing rows from the table. Like truncate it cannot be rolled back and no triggers are fired in this case, further this also a DDL command.

3. Temporary tables (temdb) are automatically deleted when they are not in use. 
  1. Local, It is prefixed with  single ‘#’ when while creation. Like create table #Study(ID int not null). Further they are available only to the connection that creates it and are deleted when the connection is closed. We can create a same local temp table with the same name but in a different connection.
  2. Global, It is prefixed by ‘##’ values, like create table ##Study (ID int not null).They are visible to all the connections of SQL server and are destroyed only when the last connection referencing the table is closed.


4Index: - It is an structure associated with a table or view which speed up the query retrieval time. An index contains keys which are combination of one or more columns of table or view. These keys are stored in B-Tree structure for efficient search of data from a table.

Types:-
Clustered Index:- Example like phone directory, where each entry in the directory represents one row of the table. A table can have only one clustered index.
Non Clustered Index: - Example , index at the back of the book, where indexed values contains pointers to the actual rows(page number in a book).

Syntax:-Create index (index name) ON table_Name (Column name)


5. Select * into TableB from TableA where 0=1

6. BCP (Bulk copy Program) is a command line utility to bulk copy data between an SQL server and a data file (both in or out).We can use table or query to export/import data. Example we need to move data from database A to file B , so we would write bcp database A .TABLE A out file B.


How to randomize rows of data in a Excel?

Problem:- How to randomize the given set of data in an Excel?

Solution: - We would try to shuffle the rows of data in a given excel. We would try it manually first followed by code written in Vb-script.

Using RAND() FUNCTION to shuffle excel data
Manual Approach:- 
Step1:- Add column(say Random) in the starting of the excel

Step2:- Apply formula to the first cell (=Rand ()) and drag it across all the cells of the same column in the sheet.

Step3:- Apply on the Filter on the first row of the excel (Data - > Filter)

Step4:- Now sort the Column one data in ascending order .All set we have our randomize data in the end.

















Automation Approach:- 
We would use Vbscript code to automate the randomization of excel data. We would replicate the above manual steps by using coding.Please refer our tutorial on Excel automation to understand the below code.


Note:-
Please do the following changes to run the below code:-

  • sFileName = Change it to Your file that need to be randomized
  • Change the sheetname in the code line   Set oReadSheet = oReadWB.Worksheets("Sample")

'Function Call to Randomize data in Excel
fn_Randomize

'*******************************************Function*********************************
'Function Name:-        fn_Randomize
'Function Description:- Function to shuffle Excel data
'Input Parameters:-     N/A
'Output Parameters:-    N/A
'************************************************************************************
Sub fn_Randomize()
    'FileName of the Excel
    Dim sFileName
    sFileName = "D:\Automate\DataSheet\RandomTest.xlsx"
    'Creating the Excel Object
    Set oExcel = CreateObject("Excel.Application")
    'Making Excel Visible
    oExcel.Visible = True
    'Creating the Workbooks object
    Set oReadWB = oExcel.Workbooks.Open(sFileName)
    'Creating the Read sheet object
    Set oReadSheet = oReadWB.Worksheets("Sample")
    'Inserting 'Random' column as our first column in the sheet
    oReadSheet.Columns(1).Insert
    oReadSheet.Range("A1").Value = "Random"
    'Running the loop till for all the rows of excel which have data
     For iRow = 2 To oReadSheet.UsedRange.Rows.Count
        'Applying Random formula on the first column of the cell
         oReadSheet.Cells(iRow, 1) = "=Rand()"
        'Copying only the value and removing the formula from the cell
        'Try commenting the below line of code and see the cell still contains "=Rand"
        oReadSheet.Cells(iRow, 1).Value = oReadSheet.Cells(iRow, 1).Value
     Next
    'Now sorting the Data
    'Clearing any formuales on the Temp sheet
    oReadSheet.Sort.SortFields.Clear
    'Sorting on the basis of Colunm =1 in the asencding order
    Call oReadSheet.Sort.SortFields.Add(oReadSheet.Columns(1), , xlAscending)
    'Giving the range to be sorted(till the end of the sheet)
    'Start cell is A2 as we have header on the Top
    'End of sheet is taken as XFD1048576 as per Excel-2007
    Call oReadSheet.Sort.SetRange(oReadSheet.Range("A2:XFD1048576"))
    'Applying the sort
    oReadSheet.Sort.Apply
    MsgBox "Data is Randomized..Cheers!!"
    Set oExcel = Nothing
End Sub

Designing Hybrid Framework in Automation (Part-2)

We have already discussed the implementation part of our Hybrid Framework(Part 1),Now we are going to focus on the the new enhancements that we have introduced while converting our keyword driven framework to hybrid framework.

New Capabilities:-
   1. Data drive the test (Parameterization)meaning running the same test case with varying set of data.
For this we implemented the concept of HLookup in Excel
Most crucial factor is taking our test data from from “Data” sheet of Excel.
Data Sheet in Hybrid Framework
Example: - Incase we wont to Launch "chrome" browser, in the “Value column” i.e. F3 cell of the sheet(Keyword) we would fetch data from “Data” sheet of our excel by using the following formula:-
HLookUp in Excel in UFT

Note:- 

  • we have used cell F2 ( named as STARTROW) to change the test data, like incase we want to run the test case for both browsers(IE and Chrome) we would change its value to 2, else we can mark it as 3 for running our test case only on chrome browser.
  • Further we have created Name Manager (of Excel) to set the range of "DATASHEET" .

          2.Error handling with user having flexibility to take screenshots incase of errors
In the properties of test case(open test in UFT to set Properties), user can set the value NO or Yes based on the preference whether screenshots are required or not.
Path: - Screenshot are saved in the word document at following location
Screenshot path in UFT
Note: - We have placed the corresponding code in “Word” library of the test case.

        3.Email options to send run status mail after execution
We have created a basic template structure of email to inform stakeholders for the run results (Please enhance this feature as per your requirements)
Code: - Email library of the test case contains the code
Note: - We have used outlook object for this purpose

Before using this feature please change the To and CC email id’s inside “fn_StopTest” (inside FrameworkRun library).




        4.  Code optimization with driver scripts just contains the call to triggering function
'Driver Function call
fn_RunTestCase 

          5.Better structuring of code into function libraries like Word,Email,Excel
This part is just extension of above point(4) .

We have segregated our code into logical divisions by creating appropriate libraries.
Test Case structure in Hybrid Approach
       6. Improvement in error reporting through UFT result viewer.
Our result viewer contains improvement in reporting.


Note: - We thought of creating Executionlogs for the run rather than using result viewer of UFT by using notepad file or any text editor ( we might be working on this in future.)

UFT Tutorials
Learn Selenium

Hybrid Framework in Automation (Part-1)

We started our journey to grasp the concept of framework in automation using keyworddriven approach, today we are going to shape our existing code into a more versatile form known as Hybrid framework.

Note:- Please read our post on Keyword Driven framework before starting with Hybrid concept.

Follow us on Facebook for more updates and learning!!!
Moving from Keyword to Hybrid Framework in UFT
Moving from Keyword to Hybrid Framework

What is Hybrid Framework?
In laymen terms it is the combination of all the frameworks (Keyword + Data + Modular), which takes the best of all of the available frameworks to create a robust approach for automation.

Thus hybrid approach takes the features of all the available frameworks plus the user specific modification to achieve an easier route for automation testing of the application.

Note: - There is no standard definition or guidelines for Hybrid approach and its components are the vision of the automation tester to make automation task least maintainable, easy to implement and adapt.

Steps for using the above framework:-
Note: - Similar to our keyword driven framework code, after downloading the code -> Extract the zip files -> open the "HybirdApproach" folder -> paste it inside c:\Temp -> open the Driver script in UFT/QTP.
Folder structure Hybrid Framework
Folder structure of Framework

Before Run:-
Enable or disable the required features:-

  1.    Test Properties change set “Screenshot ” value to “Yes”, incase we want to capture error screenshots
  2. Similarly set “Email” value to “Yes”, to send test case run email with Pass/Fail status


Change the TestCase property to the required test sheet name of the test case.
Test Properties in UFT
Properties tab in UFT
After Run:-
Reset the counter in the excel to the starting data row, else it would always run for the last set of test data in “Data” tab of excel sheet.
Keyword sheet in Hybrid Framework
Reset the counter to 2 
What changes we need to do for using the framework for different test case?
To achieve this we need to do few basic changes:-

  • Simply save the given test case with the required new test case name say “TC_02_Search”
  • Input parameters:- change the value of “TestCase” to the required test case sheet say “TC_02_Search” and we are ready to run.

Summary:- 
We learned the implementation part of our Hybrid Framework.
Hope You are able to successfully execute the test cases using this approach.
Please share your feedback or queries as comments below (further You can connect with us using 


In our next post we are going to discuss how we converted our Keyword driven framework to Hybrid Framework.



ArrayList in Java with Examples

We are already aware that in Java, array size are of fixed-size. Meaning that we have to assign a size to an array, which we cannot increase or decrease.Therefore to change the size, we have to make a new array and copy the data that we want - which is inefficient and a painful technique.

To overcome this we can implement Arraylist in Java.

For more updates do like our Facebook page.

What is an ArrayList?
In essence, an ArrayList is a variable-length array of object references. That is, an ArrayList can dynamically increase or decrease in size. Array lists
would increase in size when objects are added and shrinks as objects are removed similar to Dictionary object in VBscript.

We use ArrayList incase we are not sure on the upper bound on the number of elements. On the other side, a simple Array in java is a static data structure,
because the initial size of array cannot be changed, meaning it can be used only when the data has a known number of elements.

How to use it?
By calling the ArrayList constructor.

  1. ArrayList( ) -- An Array without any initial capacity
  2. ArrayList(Collection c) -- It creates a list containing the elements of the specified collection
  3. ArrayList(int capacity)--An Array with initial capacity

Syntax:-
ArrayList Learn= new ArrayList();
ArrayList Learn= new ArrayList(10);

To create an array list in Java, we need declare an ArrayList variable (Learn) and call the ArrayList constructor to instantiate an ArrayList object and
assign it to the variable. We have taken 10 as the initial size of an Arraylist.
Arraylist Methods in Java
Methods in ArrayList

Adding Elements to Arraylist:-
We need to use the Add() method to add object(element) to the array list.


Learn.Add("Selenium");

Note:-
Incase we have specified a type of an Arraylist,the objects that we add via the add method must be of the correct type.
Arraylist of String type dont accept Int in java
Adding Int element to Arraylist of String Type

We can insert an object at a specific position in the list by listing the position in the add method

Learn.Add(1,"UFT"); //Thus index 1 have UFT in it .

Updating value of ArrayList:-
We need to use Set method for the same,

Learn.Add(0,"Java")
Learn.Set(0,"JavaScript")// we updated the index(0) to contain the value of "JavaScript" instead of "Java"

Deleting Elements in ArrayList:-
To remove all the elements, use the clear method:
Learn.clear();
To remove a specific element based on the index number, use the remove method:
Learn.remove(0);

Note:-
Incase we don’t know the index of the object we want to remove, we can remove it by using the reference name,

Learn.remove(Selenium); //instead of using Learn.remove(0);

Count of elements in Arraylist:-
We call the size() method on the ArrayList instance. This returns the number of elements in the collection.

int iCnt = Learn.Size();

How to fetch element value from a given index:-
We use get() method, which receives the index of the element to get.


String value = Learn.get(0);

we can loop and iterate to get the value in each element of the arraylist.

Element sorting:-
Collections.sort. We can sort an ArrayList with this method.  This method sorts in ascending (low to high) order.

Collections.sort(Learn);


CODE:-
Example1:-

  • Creating ArrayList by using Add Method
  • Displaying ArrayList
  • Further using Index to Add elements into ArrayList 
  • Using Get Method
  • And getting the Size of Arraylist. 

import java.util.ArrayList;

public class LearnArrayList {
 public static void main(String[] args) {
  //Creating Arraylist
  ArrayList arrList = new ArrayList();
  //Adding Elements into Arraylist
  arrList.add("Selenium");
  arrList.add("UFT");
  arrList.add("JAVA");
  arrList.add("VBSCRIPT");
  //Taking output of Arraylist
  System.out.println("Values in ArrayList ="+arrList);
  //Using Index to add Elements to ArrayList
  arrList.add(1,"QTP");
  //Get method to fetch value at given index
  System.out.println("New Value at Index '1' ="+arrList.get(1));
  //Size of ArrayList
  System.out.println("Size = "+arrList.size());
 }
}

#Output:-
Values in ArrayList =[Selenium, UFT, JAVA, VBSCRIPT]
New Value at Index '1' =QTP
Size = 5

Example2:-

  • Removing particular Element from Arraylist --Remove method 
  • Deleting all the elements of Arraylist--Clear method 

import java.util.ArrayList;

public class LearnArrayList {
 public static void main(String[] args) {
               //Creating String ArrayList of initial capacity of 3
  ArrayList<String> arrString = new ArrayList<String>(2);
  //add 4 elements
  arrString.add("JAVASCRIPT");
  arrString.add("HTML");
  arrString.add("CSS");
  arrString.add("ANGULAR");
  System.out.println("ArrayList="+arrString);
  //Remove particular element 
  arrString.remove("CSS");
  arrString.remove(0);
  System.out.println("New List after Remove="+arrString);
  //Clear the list
  arrString.clear();
  System.out.println("Empty List"+arrString);
 }
}

#Output:-
ArrayList=[JAVASCRIPT, HTML, CSS, ANGULAR]
New List after Remove=[HTML, ANGULAR]
Empty List[]

Example3:-

  • Shuffle Arraylist elements
  • Sort Arraylist
  • Convert Arraylist into Array

import java.util.ArrayList;

public class LearnArrayList {
 public static void main(String[] args) {
              //Creating String ArrayList of initial capacity of 3
  ArrayList<String> arrString = new ArrayList<String>(2);
  //add 4 elements
  arrString.add("JAVASCRIPT");
  arrString.add("HTML");
  arrString.add("CSS");
  arrString.add("ANGULAR");
  System.out.println("Original ArrayList="+arrString);
  //Shuffle Arraylist
  Collections.shuffle(arrString);
  System.out.println("Arraylist after shuffle="+arrString);
  //Sort Arraylist
  Collections.sort(arrString);
  System.out.println("Sorted Arraylist"+arrString);
  //Creating an Array from ArrayList
  String[] arrLearn = new String[arrString.size()];
  //Using toarray method
  arrString.toArray(arrLearn);
  System.out.println("Newly Created Array=");
  for(int iCnt =0;iCnt<arrLearn.length;iCnt++)
  {
   System.out.println(arrLearn[iCnt]);
  }
 }
}

#Output:-
Original ArrayList=[JAVASCRIPT, HTML, CSS, ANGULAR]
Arraylist after shuffle=[ANGULAR, HTML, CSS, JAVASCRIPT]
Sorted Arraylist[ANGULAR, CSS, HTML, JAVASCRIPT]
Newly Created Array=
ANGULAR
CSS
HTML
JAVASCRIPT

Creating Pivot chart for ALM reporting.

How we created our automation progress  report from ALM to Email in 30 minutes?

Requirement:-
We need to showcase our management regarding the  progress in Automation.So rather than giving them quantitative data we thought of sharing it in more presentable manner using Pivot charts.

Hope it help our fellow automation friends in creating easy and rapid reports.
Please feel free to share Your experience as comments, as we always believe sharing is caring.

Do follow us for more updates FB,LinkedIn,G+

Download dummy Excel

Solution:-
Step1:- Getting Data from ALM(Application life cycle management),

Note:- It can vary depending on the management tool which is used in the organization but still the approach would be same.
A)Testing -> Test Plan -> Select the Test Folder -> Live Analysis Tab -> Click on 'Add Graph'

Opening Live Analysis view in ALM

B)Select 'Summary Graph' -> Click Next

Summary Graph creation from ALM

C) Select any value for X-axis field -> Click Finish

Selecting X-axis Field in ALM summary graphs

D)Graphical view would be generated -> Click on the bar graph tower

Live Analysis graph in ALM

E)Drill down Results window opens

Drill down results ALM

This is what we were waiting for, we can change what all the columns we need in our report.Further we would export the report in Excel format.


Select columns in Drill down results
Selecting columns in Drill down results view

Export Excel from ALM
Exporting excel from Drill down results


Step2:-Creating Pivot Chart
A)We have our required data in Excel,we can edit to make any required changes like headers or any other modification.
B)Insert -> Pivot Chart

Opening Pivot Chart in Excel

C)Select the Table/Range for Pivot Chart -> Click Ok

Create Pivot Table, range selection Excel

D)Select from "Pivot Chart" Fields, the columns that we need to displayed in Pivotchart report
Pivot chart in Excel

Check the two variations of graph when we changed the data under Axis and Values.



Selecting Pivotchart fields in Excel
Pivot chart view-1





Pivot Chart view-2


Step3 :- Playing with the design of the Pivot chart
One's our chart is ready we can change the color or display design (Like pie chart) to make it look more impressive and easy to comprehend

Just select chart -> click on brush


Changing color in pivot chart



Changing chart styles in Pivot chart



We can also change the shape of graph, INsert -> Charts -> Select the chart option as suitable(Do select the chart below to check the live effect)


Selecting different chart styles in pivot charts Excel



ReRun ALM test
Log BUG in ALM using OTA
Learn Selenium