Download and Save an Excel Sheet using QTP

During automation we might have came across scenarios which requires selecting group of data and downloading an Excel file and then save it on a particular location.

Automating Excel sheets requires correct steps to follow. At first you have to create an Excel object and then to save, you have to choose a dynamic location and it should not be machine dependent. So here i am presenting a quick function that serves the discussed purpose.

Step 1.
Make a vbs file i.e open notepad copy the below code and save it with a .vbs extension. [Name].[vbs].

Step 2.
Add the function in QTP resources. Browse file > Settings > Resources tab.

Step 3
Now Call the function in your code where required. Use keyword 'Call'

''''''Function initializing
Public Function ExcelExport()

''''''Check excel button existence
If (Browser("name:=.*").Page("title:=.*").WebButton("html id:=some ID").Exist(1)) then Browser("name:=.*").Page("title:=.*").WebButton("html id:=[some ID]").Click
'''''Will Open and Save Excel sheet
Browser("name:=.*").Dialog("text:=FileDownload").WinButton("text:=&Open").Click
wait 5

'''''Creating Excel object to save the exported file
Dim objExcel, xlBook

'''''Initializes user variable to Excel object
Set objExcel=CreateObject("Excel.Application")
If the above line does not work, try using GetObject method to locate already open excel file with Eval to bypass syntax error on using Getobject. It is a bug in QTP syntax checking.
 '''''Initializes user variable to Excel object
Set objExcel = Eval("GetObject(, ""Excel.Application"")")

'''''Initializes user variable to Workbook object
Set xlBook=objExcel.Workbooks

'''''Make active sheet visible
Set xlBook = objExcel.ActiveSheet

'''''Saves, add random number, to avoid duplicate names
xlBook.SaveAs "Exported" & EntityName & RandomNumber(1,99) & ".xls"

'''''Exits MS Excel application
objExcel.Quit

'''''Release object allocation from memory
Set xlBook = Nothing
Set objExcel = Nothing

'''''Prints a user defined friendly message
Print "Downloaded and Saved Exported Records successfully...." & vbnewline

''''''On no excel button availability
else
Print "Missing Export to Excel button"
Reporter.ReportEvent micFail,"Missing Export to Excel button","Missing Export to Excel button"

End If

End function
 Let me know your comments, reviews  if you found above function useful.

Comments

  1. UnknownOctober 20, 2014 at 4:46 PM

    the line code Save as is not working...geting "un known run time error"

    ReplyDelete

Post a Comment

Popular posts from this blog

LoadRunner Controller

Image
This is my second post from the HP LoadRunner series. As I discussed earlier, here I will be discussing an overview of LoadRunner Controller . So before going into detail, let me recap the prerequisites. Create script – Checked Replay Script – Checked Updating script if required (Add transaction, Parameterization and Content Check) – Checked  Runtime settings and Iterations – Checked Assuming the script is absolutely fine i.e. error free and ready to be passed on Controller, let us get familiar with the UI first. I will be using LoadRunner 9.5, the screens may vary at your end depending on the version you are using.   The UI The Controller has a Title bar, Menu bar, tool bar and a Status bar. After these we have multiple sections or panes.   The first is Scenario Groups pane. We can add multiple scripts or a single script and name them as a Group.  Next is Scenario schedule, here we define how the load will be passed and executed. We have two types of scenario group. Manual
Read more

All about Dates using QTP

Majority of applications have Calendars controls. Automating a calendar can be painful at times if the textbox is disabled, and it only works if you select the date from the calendar widget provided on your website/application. I have been working on date controls for couple of months so thought about sharing all the findings with you people. So here i am summarizing some of the checks which can be handy working on date/Calendar controls. '''''' '''''' Compare start and End date For comparing end date validation, set today's date using Date function in start date textbox and set previous date in end date, and check if the validation is fired.   '''''startdate - set as todays date using 'date' Browser("").Page("").WebEdit("").Set Date '''''End date, set prev 5 days date Browser("").Page("").WebEdit("").Set DateAdd("
Read more

Working with WebRadioGroup

QTP WebRadioGroup means a radio button on the web page. Identifying and working with them can be easy or difficult. Sometimes they works out fine and at times executing the same code gives you error. If you have two radio buttons on the screen for example Gender selection options the code below might give problem. Browser("").Page("").WebRadioGroup("").Select "M" Where "M" can be the id or the value of the radio button (Spy the object). So what is the problem in the code above? As there are two radio buttons QTP identifies them as same, so we need to differentiate between them. Index is a property by which you can differentiate, it tells the position of the object. So if we write the same code below with an additional index property it will work fine. Browser("").Page("").WebRadioGroup("html id:=" ,"index:= 1").Select "M". This code is intended to select a radio button among two, what
Read more