Excel formulas and QTP

[bis]

I have an excel sheet its pretty simple 4 columns of data 10 rows of data.
Column A is just text (there is another problem i will talk about later)
Column B is just a number
Columns C and D were giving me problems i was using

Code:

=RANDOMBETWEEN(1,50)

but i changed that to

Code:

=INT(RAND()*50+1)

this works fine.

now my questions. when (if) my script fails it puts a lock on the .xls that i am loading is there a way to force the xls closed. I dont know what language QTP uses for scripting but i know that if i open a file in C# i can force it closed after i am done with it.
i am using

Code:

DataTable.ImportSheet "C:\4week\test.xls", 1, "Action1"

also does the scripting language support sending an email? or would i be able to use QTP to load up a seperate application to send me an email with the results from the test and a copy of the data?

lastly the other problem i was having is i wanted to randomize the data that is in column A to do this i'm using

Code:

=INDEX(Z$4:Z$46,RANK(Y4,Y$4:Y$46))

but when it loads into the QTP data sheet i get an error. any ideas?

[gammaflare]

now my questions. when (if) my script fails it puts a lock on the .xls that i am loading is there a way to force the xls closed. I dont know what language QTP uses for scripting but i know that if i open a file in C# i can force it closed after i am done with it.
i am using

Code:

DataTable.ImportSheet "C:\4week\test.xls", 1, "Action1"

QTP locks the Excel sheet and there is no way you would be able to close it. But if you want to change the data while the script is running there is workaround provided by Excel, invoke the sharing option of the datatable, that will enable the user to change the data while the QTP script is using the datatable but again the changed data will only be used in the next execution of the script not in the current execution.

also does the scripting language support sending an email? or would i be able to use QTP to load up a seperate application to send me an email with the results from the test and a copy of the data?

Yes, you could write a vbscript to send an email to you, we did it. If you want to see the results of the test, I would suggest to store them in a share and use the results object to access the functions.



lastly the other problem i was having is i wanted to randomize the data that is in column A to do this i'm using

Code:

=INDEX(Z$4:Z$46,RANK(Y4,Y$4:Y$46))

but when it loads into the QTP data sheet i get an error. any ideas?
[/quote]

Are you sure it is RANK not RAND??

[bis]

yeah i have 1 column "y" with 46 cells of data in those cells is =RAND()
in column "z" i have cells 1-46 with a random word in them

what =INDEX(Z$4:Z$46,RANK(Y4,Y$4:Y$46)) does is rank the "z" column of names based on the rank of random number that is generated in y

it works fine in excel but does not work at all in QTP