Automating Microsoft Excel – Part 1 – How to Create an Excel Object, Enter and Retrieve Values from it.

This is Part 1 of the Series where I’ll be explaining how to Automate MSExcel using QTP. First of all, one needs to understand the Excel Object Model in order to use the numerous functions provided by Excel API. So here’s a quick snapshot of it :-

The Detailed Object Model can be found at – http://msdn.microsoft.com/en-us/library/bb149081(v=office.12)

Following Code in QTP utilizes this object model to create an Excel File, write some text into it and save it :-

‘Creating the Excel Application Object and Making it Visible

Set objExcel=CreateObject(“Excel.Application”)
objExcel.Visible=True ‘If this property is False, all operations will work but it’ll not be visible to us

‘We can create a New WorkBook with the Add Function
Set objBookNew=objExcel.Workbooks.Add

‘or we can open an Existing WorkBook
‘Set objBookExisting=objExcel.Workbooks.Open(“C:\DummyFolder\Sample.xls”)

‘This creates the Sheet Object with which we can Access the contents within the Excel File
Set objSheet=objBookNew.ActiveSheet

‘Setting the Value for a Cell
objSheet.Cells(1,1)=”Excel Automation is Easy”

‘Accessing the Value in the Cell
msgbox “New Value in the 1st Row and 1st Column is – ” & objSheet.Cells(1,1)

objBook.Save
objBook.Close ‘Saving and Closing the WorkBook

objExcel.Quit ‘This Quits Excel and Kills the Process

‘Freeing the Memory
Set objSheet=Nothing
Set objBook=Nothing
Set objExcel=Nothing

You can find part 2 here – https://learn2automate.wordpress.com/2014/01/17/automating-microsoft-excel-part-2-formatting-an-excel-file/

Advertisements

Eat, Sleep, Automate. REPEAT !

Tagged with: , , ,
Posted in MSOffice Automation
15 comments on “Automating Microsoft Excel – Part 1 – How to Create an Excel Object, Enter and Retrieve Values from it.
  1. deepika bagai says:

    Hi , Can i get to know difference between data driven,keyword and hybrid framework.Can i download folder structures for the same so that I can understand the difference between the 3.

    Thanks in advance,
    Deepika Bagai

  2. Hi Deepika, you will get sufficient info on Google regarding QTP Frameworks…but here’s a summary of what you want to know :-

    1. Data Driven Framework – uses external files/Database to run a script on different sets of Data. Eg: Fetching a list of usernames from an excel file and trying to log in through these usernames one by one on the same page with the help of the script.

    2. Keyword Driven Framework – uses different Keywords to drive the flow of the Test. Eg; An Excel file stores the Object names and the type of operation to be performed and a Driver Script is used to read the File and execute the steps.

    3. Hybrid – this is a mixture of the two approaches.

    There are several websites like http://www.open2test.org where you can find more info on Frameworks…

    Thanks,
    Harshit Kohli

  3. Divya says:

    instead of using Datatable from the QTP, I want use direcly the excel sheet from its location for read and write is it possible to do the same way as we do the global and action sheet

  4. Vijay says:

    Yes…you can use Excel to read/write instead of using DataTable,however, it is not possible use it in the same way as one use datatable. DataTable has separate QTP inbuilt functions as it is native to QTP.

    You have to create excel object to open excel sheet from a location and then read/write values in it.

  5. roshni says:

    hi harshit,
    can u tell me how to search data and rename the data in excel using function n dat to in qtp?
    PLZ rply soon,

    Thnx in advance

  6. Hi Roshni,

    You can find data in an Excel File in many ways. Here are two ways of doing it :-

    1. You can use just a simple for loop that iterates through the cells and does a string comparison of the data found in the cell. The code for this would go something like :-

    For i=1 to objSheet.UsedRange.Rows.Count
    For j=1 to objSheet.UsedRange.Columns.Count
    If objSheet.Cells(i,j)=”Value that you are looking for” Then
    msgbox “Success”
    End If
    Next
    Next

    This approach would be appropriate if there are less rows/columns in your excel. Use the next approach if you want to have it done quickly.

    2. Use the Find command similar to how we use the Ctr+F Manually :-

    ‘This command will find the required text from A1 to A500
    Set findRow=objSheet.Range(“A1:A500”).Find(“Text you need to find”)

    If findRow is Nothing Then
    msgbox “Not Found”
    Else
    msgbox “Data found at “& findRow.address
    End If

    Let me know if this works for you.

  7. Lalitha says:

    usefull informtion, well presented

  8. maheshwari says:

    Hi Harshit,

    your blog really helping me to brush up my skills and to improve too.
    Looking forward for more things.

    Thanks,

  9. Eeshan says:

    Where is Part 2?

  10. rajkumar says:

    Hi Friends,
    You can find data in an Excel File
    Set xl=createobject(“Excel.application”)
    set wb=xl.workbooks.open(D:\sanjay.xls”)
    set ws=wb.worksheets(“Sheet1″)
    For each cell in ws.usedrange
    If cell.value=”Sanjay” Then
    cell.interior.colorindex=44 or Msgbox “Specified Data Found or cell.Address
    End If
    Next

  11. pradeep says:

    Hello Harshit,This is really a cool blog yaar.I need a help on automating excel addin and some ribbons under it.Can you please help me on this.it’s very urgent.

    Thanks.Pradeep

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog Stats
  • 168,130 hits
Categories

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 796 other followers

%d bloggers like this: