Sometimes some automation scenario looks very simple to automate, but it shows up its intricacy when you actually start working on it. Such things usually happens at the time when you do the POC (proof of concept) of an application. The objects and the high level scenarios looks ok to automate and you estimate accordingly, but at the time of the actual scripting you get hard time to achieve the required.

In this post I am going to explain one of these kind of challenges which I experienced in one of the project. To start with, I will first explain the scenario. Saving already Opened Excel Sheet –  Lets say you have an application, which shows a large number of tabular data for a particular query on UI. These data need to be retrieved into an excel file. You have an ‘Export to Excel’ functionality available on the UI, which exports all the data into an excel file and the file opens up at your screen. The requirement here is the opened excel file needs to be saved at specific location for further references.

The first part that is to click on ‘export to excel’ button can be easily done by capturing the object and performing action on that object. But the actual problem is with saving the opened excel. To tackle this problem,I tried various methods, and end up with few ways to do the same. Here I am explaining the two best and easy approaches.

In first approach, I captured the excel object and stored it in the object repository.I captured the object because I need to set the focus on excel.This will help me to perform the set of keystrokes for saving Excel.The sequence of keystroke for saving the excel is as follows :

a) Press "F10"

b) Press "ALT+F"

c) Press "F"

d) Press "X"(to save excel in 2007 format) or "9" (to save excel in 2003 format).

As soon as I press “X” or “9” ,Save As dialog box appears, where we need to set the path and click on Save button.

For stimulating the sequence of keystrokes in QTP, I used the “Sendkey” method. For using this method we need to create an object of “WScript.Shell” first.

Set Wshell=CreateObject(“WScript.Shell”)

Now I will use the SendKey method to perform the keystroke in a sequential order

Wshell.SendKey “{F10}”

Wshell.SendKey “%F”

Wshell.SendKey “F”

Wshell.SendKey “X”

After use of the Wshell object we should set the value of it as nothing.

Set Wshell=nothing

The second approach is one of the best approach I had came across to encounter this excel problem.In this we used “GetObject” function returns a reference to an object provided by a COM component.In our   case “GetObject” check whether any Excel object is present or not , and if present then save it as per the path mention by us.

See also  6 helpful Tips on QTP

Syntax

object.GetObject(Pathname [,ProgID], [Prefix])

where

object: WScript object.
Pathname: The fully qualified path name of the file that contains the object persisted to disk.
ProgID: (Optional) The object’s program identifier (ProgID)
Prefix: (Optional) Used when you want to sync the object’s events. If you supply the Prefix argument, WSH connects the object’s outgoing interface to the script file after creating the object.

Function SaveExcel(ExcelPath)

Set ExcelApp=CreateObject(,"Excel.Application")

ExcelApp.SaveAs ExcelPath

End Function

[/sociallocker]

If you also have such situation, where you find difficulties to automate or need help on any other similar issues, please feel to reach as by this contact form,we will get back to you as soon as possible.

Till then

Happy Learning!!!

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.