Excel Formula work around?

[GeeSlimmy]

I have a script that I run every two weeks on my companies patch
night.


The script is as follows:

1. 4 Excel sheets with Date values that need to be changed every two
weeks. I tried creating a =NOW() formula in excel and I think QTP does
not like formulas because I kept getting error messages about my excel
sheet when I opened my script in QTP.

2. I have a driver action at the top of this test that is connected to
all the existing test below it just like Karthik demonstrated in the
Actions, SOR..Pre-Recorded class.

3. I also have emails that need to be unique and names that need to be
unique every two weeks when I run this test on 4 Excel sheets.

This test takes a total of around 30 minutes to run on 2 Servers and
touches a lot of modules across our application.

Is there some way I can write a VBscript that will update the cells in
the excel sheet to random emails, random names, and certain dates like
tomorrow and yesterday. Basically, I'm trying to figure out a way so
I don't have to manually change all of these cells each time I run the
test. My guess is to set up some variable but I'm not sure about the
approach?

[Chandan Kochhar]

Hi

1. QTP does not like formulas but you can use your formulas in Excel
sheet and get all your data, then create another exel sheet and copy
all of the data in the new sheet, it should resolve issue of QTP not
liking the formulas.

For random dates you can use formula like
DateAdd("d", 1, Date), this will add one day to today's date.
DateAdd("d", -1, Date) will give you yesterday's date
and for different email ids, you can use concatenate some strings
with like time or random number functions
e.g RandomNumber (100000, 9999999)
e.g abc & second(now) @gmail.com

Hopefully it gives you some direction and resolve your issue.

Regards,
Chandan

[GeeSlimmy]

Hi Chandan,

1. I'm not really understanding how you mean "use your formulas in
Excel sheet and get all your data"? If I cannot use formulas? I have
the Default.xls sheet as my data resource. Are you saying that I need
to create a new sheet with the formulas in the same Default.xls book
and have my script access this copied sheet inside the same
Default.xls book? Also this sheet is used throughout 15 scripts
utilizing login names and secure passwords etc this part I would like
to leave as is.

2. (DateAdd("d",1,Now())) I will try to write this VBscript for the
dates so instead of the script accessing the sheet for this date data
I guess the VBscript can do a better job of it?

3. abc & second(now) @gmail.com I will try to write this as well in a
VBscript for the emails.

If I write the VBscript for Dates and Emails then do I still need to
access my Default.xls sheet for the dates and emails?

Here's an example of my script. Can you please guide me where I should
make the change? And show me example of how and what might work for
this script.


Example of my Date accessing GlobalSheet.



Browser("Client SSO").Page("Client
SSO").ASPAjaxCalendar("ctl00_ctl00_ContentPlaceHolder").Set
DataTable("TaskStartDate", dtGlobalSheet)
Browser("Client SSO").Page("Client
SSO").ASPAjaxCalendar("ctl00_ctl00_ContentPlaceHolder_2").Set
DataTable("TaskEndDate", dtGlobalSheet)



Example of my Email accessing GlobalSheet and Username



Browser("Client SSO").Page("Client SSO").WebEdit("regControl
$emailTxt").Set DataTable("Email", dtGlobalSheet)
Browser("Client SSO").Page("Client SSO").WebEdit("regControl
$txtUserName").Set DataTable("UserName_selfreg", dtGlobalSheet)


Can I set up something like this below?

Option Explicit
Dim vEmail, vDate

(Then I will need to replace these 4 lines of code but I'm not sure
how to write this vbscript?)

[GeeSlimmy]

Ok, I'm half way there almost. The copy and delete old sheet worked
and I'm able to use =NOW()+1 and =NOW()+2 for my date fields which is
working out great. I'm still trying to figure out the random name and
random email that I might be able to do all in excel but I'm
investigating it now. Please let me know about the VBScript for the 4
lines of code I mentioned before. Any idea as to why I need to create
a copy of the sheet with the formulas then delete the old sheet? After
I did this it works without any errors! I'm happy about this part but
still stuck with how to either VBScript code the random email and
names part or somehow do it with excel formulas?

Thanks for this so far great help!

Gary

[Chandan Kochhar]

hi mate,

I think there are two ways you can achieve this by

1. Using Excel, if you are comfortable with excel then, what you can
do is have a excel book outside qtp, create your sheet with formulas,
once you are happy with all the data, select all, copy all, paste
VALUES into the next sheet. and now this new sheet is ready with all
the values you might need for you script. Then you can just import
this new sheet into your qtp datatable and then from there on
everything should be same and straight forward i assume.
syntax for importing a sheet:
datatable.importsheet("c:\test.xls", "newsheet", "datatablesheet")

2. using vbscript, it ll be a little complex as you ll need to use
loop to fill values in those variables and then rather than setting
data from datatable, you will say set data from your variable.

e.g.

Option Explicit
Dim vEmail, vDate

datatable.importsheet("c:\test.xls", "newsheet", "datatablesheet")
vEmail = datatable.Value("Email", dtGlobalsheet)
vDate = datatable.Value("TaskStartDate", dtGlobalSheet)

Browser("Client
SSO").Page("ClientSSO").ASPAjaxCalendar("ctl00_ctl00_ContentPlaceHolder").Set
vDate
Browser("Client
SSO").Page("ClientSSO").ASPAjaxCalendar("ctl00_ctl00_ContentPlaceHolder_2").Set
vDate
'Example of my Email accessing GlobalSheet and Username

Browser("Client SSO").Page("Client SSO").WebEdit("regControl

$emailTxt").Set vEmail

Browser("Client SSO").Page("Client SSO").WebEdit("regControl
$txtUserName").Set DataTable("UserName_selfreg", dtGlobalSheet)

[GeeSlimmy]

Hi Chandan,

Thanks for the help. This help is mainly geared up towards adding
formulas to my excel sheet right? I did for =NOW()+1 and =RAND() both
of these work fine for me.

1. =NOW()+1 and =NOW()+2 'I'm using these two for my date fields.
2. =RAND() I can actually get away with using this which generates
random numbers for my username field. :-)
3. I still haven't figured out how I'm going to generate the random
emails? I was think in excel to have =RAND()&D1 (in this case D1
would be @gmail.com) but this will not work and just show as a blank
cell when I import and checked it into my script in QTP.) I guess I
cannot have a cell on the sheet that's not actually being utilized and
probably cannot add & formula but not 100% sure about it?

I would honestly like to do a VBScript loop statement but this might
be a little too advanced for me right now but would love to learn
this.

I have another question this is probably a very easy one to answer.

I'm using this line of code below adding action and insert call to
existing about 4 times during my script different parts of the script
where I need to login again after logging out.

Browser("Cornerstone Learning Talent").Page("Cornerstone Learning
Talent").WebEdit("corpBox").Set DataTable("Corp", dtGlobalSheet)


I try to do action and insert call to existing and have the Action
Call Properties set up to Run from row 2 to row 2 but this is being
ignored and just seems to run from row 1.

Here's what the code looks like below.

RunAction "Login [ILT_And_Proxy_QueueDown]", "2 - 2"

I have two different servers one in row 1 and one in row 2. Row 2 is
being ignored here when I specific say Run from row 2 to row 2.



Thanks again,

Gary

On Dec 23, 12:35 pm, Chandan Kochhar <chandan.koch...@gmail.com>

[GeeSlimmy]

I winded up adding an extra column called URL2 in my first row with my
second server URL in the cell. I also made the last name cell
@gmail.com and I just used the =RAND() which generates a decimal
random number & @gmail.com column for my random emails. This works
fine. So my formula for the random emails is referencing two cells on
my sheet. =D1&B1 (=RAND() + @gmail.com) this gives me what I
needed.

[Chandan Kochhar]

Sorry buddy,

I was on holidays and could not reply any earlier. But nice to see
that you have found your solution, it could be achieved in excel
itself as well using concatination formula. but as long as it works
for you, thats what matters.

Cheers,
Chandan