MSOffice Automation

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 –

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


15 replies »

  1. 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 where you can find more info on Frameworks…

    Harshit Kohli

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

    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”
    msgbox “Data found at “& findRow.address
    End If

    Let me know if this works for you.

  7. Hi Harshit,

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


  8. Hi Friends,
    You can find data in an Excel File
    Set xl=createobject(“Excel.application”)
    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

  9. 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’s very urgent.


Leave a Reply

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

You are commenting using your 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