Quick test professional

QTP Tips QTP codes QTP Faqs and more

How To open Password Protected Excel sheets

Function UnprotectXL(filePath,fileName,pwd,writeresPwd)
   Set objExcel=CreateObject(“Excel.Application”)
   Set oWorkbook=objExcel.Workbooks
   Set myWkbook=objExcel.Workbooks.open (testData,0,False,5,pwd,writeresPwd)
   For Each w in objExcel.Workbooks
        w.SaveAs testData,,””,””
   Set oWorkbook=Nothing
   Set objExcel=Nothing
End Function
Function ProtectXL(filePath,fileName,pwd,writeresPwd)
     On Error Resume Next
     Set objExcel=CreateObject(“Excel.Application”)
     Set oWorkbook=objExcel.Workbooks
     Set outputWkbook=objExcel.Workbooks.open (testData,0,False)
     outputWkbook.SaveAs testData,,pwd,writeresPwd
     Set outputWkbook=Nothing
     Set objExcel=Nothing
End Function

‘Call ProtectXL(“C:\Documents and Settings\kmohankumar\Desktop”,”4.xls”,”test123″,”test123″)
‘Call UnprotectXL(“C:\Documents and Settings\kmohankumar\Desktop”,”4.xls”,”test123″,”test123″)

Author: Mohan Kakarla


November 27, 2008 - Posted by | Excel Automation


  1. Hi Mohan,

    very understandable code.

    Best Regards,

    Comment by ramji | November 29, 2008 | Reply

  2. Hello Mohan,

    Would you be able to advise how to parameterize an Excel Report CELL for automated verification by QTP?

    Thanks very much in advance!


    Comment by Sean | March 11, 2009 | Reply

  3. This is very much useful


    Comment by Rami | June 2, 2009 | Reply

  4. Hello Mohan,
    Useful understandable code !!!!!!!!!
    I really liked it and very useful to me. I’m doing a bit research about test automation and also found macrotesting http://www.macrotesting be a good resource.
    Thanks for the easy and understandable code!

    Comment by Poornima | July 2, 2009 | Reply

  5. Hi Mohan,

    I want to convert the word document which is Test case template in to Excel. Help me in this regard.

    Comment by Geets | September 16, 2009 | Reply

  6. Hi Mohan,
    This is Rajendra , pls help me for the below QTP requirement.

    1) I want to schedule “myscript.vbs” file which exist in remote machine through local machine.

    help me any workaround to the requirement.

    Comment by Rajendra | March 10, 2010 | Reply

  7. Hi Anna

    I am using QTP 10.0 version for WPF application. I am trying to identify the buttons in a tab by using OR. But they are identified as WPF List, not the individual item. If we try to do record and play back on those buttons QTP is not providing any script in its test pane.

    I have tried to indentify the tab items by using SPY. Attached are the screen shots of objects and also Iam sending you my sample tab objects. Assume that there is image present in each starting of the tab object name (I have marked one circle treat that it is a image).

    For this could you please give me any VB script code without related with the object spy and OR?

    Thanks and Regards

    Comment by Anand | August 18, 2010 | Reply

  8. Hi Mohan,
    need some help
    related to Excel User Form.
    in excel based application, there is a button on excel sheet, when u click it opens a Pop up which is USERFORM of excel having text box textbutton etc.
    how to access to those objects using QTP or Vb script, so that we can input values in textbox or click button etc
    Please define it in details
    thanks in advance

    Comment by Abhimanyu | September 13, 2010 | Reply

  9. Hi Mohan,

    your blog has been a ready reference many times i wanted some code. The main difference between your blog and other blogs is your code runs rightaway after deleting and typing the single quotes and double quotes which are corrupted.

    Can you suggest a goos readign material to work on excels mainly and also on word, pdf, xml and other file formats, please reply me on my e-mail reddy1240 at yahoo dot com

    Comment by vinod | November 8, 2010 | Reply

  10. Hi,
    can you please guide me how to automated Macro Excel using QTP.

    Comment by atul singh | February 15, 2011 | Reply

  11. Hi. I am unable to find how to get in touch wiht you. I apologize for entering this here. I am new to Excel in QTP and having issues. Any help would be greatly appreciated. I am trying to work with Excel and struggling with the objects and parameter passing between functions and QTP actions. It seems objects cannot be passed to QTP action.

    1). I made a function that creates the excel worksheet and creates a workbook (code to follow). (I’ve tried using Environment variables with no success.)
    2). I created a QTP action and am trying to get the object using the Environment variables.

    Created a “Main” QTP action that calls the action below:

    Function XL_CreateXLSheetvwe2(byref objExcel, byref objWorkbook)

    ‘Create the Excel object(“Excel.Application”)
    Set objExcel = CreateObject(“Excel.Application”)

    ‘Set environment variable
    Environment.Value(“objExcel”) = objExcel
    objExcel.Visible = True

    ‘Create a workbook where the worksheet will go
    Set objWorkbook = objExcel.Workbooks.Add()

    End Function

    “Main” action then calls QTP action “AcctMonth_Test”:
    Action AcctMonth_Test:(called from Main action)

    Dim objExcel, objWorkSheet, objWorkBook, objSheets

    set objExcel= Environment.Value(“objExcel”) ‘in debugger this shows as empty
    set objWorkSheet= Environment.Value(“objWorkSheet”) ‘in debugger this shows as empty

    call XL_WorkSheetName(objExcel, objWorkSheet,objWorkBook,objSheets, “1-AcctMont”,1) ‘another function

    The objExcel is not initialized to the Environment value when run per viewing with the debugger.

    Any help would be greatly appreciated.

    Comment by Vince | February 20, 2012 | Reply

  12. Hello! gefdabg interesting gefdabg site! I’m really like it! Very, very gefdabg good!

    Comment by Pharma984 | May 11, 2012 | Reply

  13. Hello! eedbked interesting eedbked site! I’m really like it! Very, very eedbked good!

    Comment by Pharmd927 | May 11, 2012 | Reply

  14. Hello! dcfgdea interesting dcfgdea site! I’m really like it! Very, very dcfgdea good!

    Comment by Pharma69 | May 11, 2012 | Reply

  15. Hi
    need help of writing a code in which i have to find a string in a word file and then copy a table which comes under tthat string and then paste it to the excel sheet

    Comment by sahil sachdeva | September 2, 2014 | Reply

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 )

Google+ photo

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


Connecting to %s

%d bloggers like this: