Author Topic: How to pass variable from external xls to function  (Read 1311 times)

Offline Anil shrestha

  • Jr. Member
  • **
  • Posts: 88
  • QA Analyst
How to pass variable from external xls to function
« on: October 01, 2009, 09:19:53 PM »
Hello
I need little help  to use function and  .xls sheet to Import Login URL, ORG, UserID and Pword.

Things were easier when I  was just using single URL(SystemUtil.Run "..." method ) and fixed UserID and Pword to login but now my test environment can change  4 different url and varying  userID and password.

This is how far I could go. I have created bellow function and supplied the required data to the script from DataTable(global) which works fine.
But my goal is to get it from single external xls(LoginIdPwURL.xls) file so that I can use this for all the tests.

Function:
-------------------------------------------------------------------------------------------------------------------------------
Public Function LoginPadmin(ByVal URL, ByVal ORG, ByVal UserId, ByVal Pword)

SystemUtil.Run DataTable("URL" dtGlobal)
Browser("Login").Page("Login").WebEdit("html id:=txtOrganizationId_txtText").Set DataTable ("ORG", dtGlobal)
Browser("Login").Page("Login").WebEdit("html id:=txtUserId_txtText").Set DataTable("UserID", dtGlobal))
Browser("Login").Page("Login").WebEdit("html id:=txtPassPhrase_txtText").Set DataTable("Pword", dtGlobal)
Browser("Login").Page("Login").WebButton("html id:=btnGo").Click

End Function

-------------------------------------------------------------------------------------------------------------------------------

FileName= "C:\Documents and Settings\r218483\Desktop\GSA\Excel\LoginIdPwURL.xls"
Dim ExcelSheetName
Dim My_Sheet
Dim TestmyData
Set ExcelSheetName = CreateObject("Excel.Application")
ExcelSheetName.Application.Visible = False
Set ObjActiveWkb = ExcelSheetName.Workbooks.Open(FileName)
 
For i  = 1 to 1
     msgbox i+1
     URL = objActiveWkb.Worksheets("Sheet1").Cells(i+1,1).value
     msgbox URL
                 ORG = objActiveWkb.Worksheets("Sheet1").Cells(i+1,2).value
                 msgbox ORG
                 UserID= objActiveWkb.Worksheets("Sheet1").Cells(i+1,3).value
                 MsgBox UserID
                 Pword= objActiveWkb.Worksheets("Sheet1").Cells(i+1,4).value
                 MsgBox Pword
 
Next

 Call LoginPadmin ("URL", "ORG", "UserId", "Pword")


I have already created the similar column names in the external xls sheet and I have tested the field through msgBox it all works fine. But I do not know how to pass this to the script. This code still takes value from Global sheet. I tried to remove dtGlobal with FileName but did not work.

Your help is really appreciated. Thank you in advance.

'ExcelSheetName.Workbooks("LoginIdPwURL.xls").Save
ExcelSheetName.Workbooks("LoginIdPwURL.xls").Close
ExcelSheetName.Application.Quit
Set Exl_Obj = Nothing
 

Offline bharatbkakkar

  • Newbie
  • *
  • Posts: 28
Re: How to pass variable from external xls to function
« Reply #1 on: October 02, 2009, 01:04:46 PM »
Hi dear just import the xls as following

Step 1 : datatable.importsheet "C:\Documents and Settings\r218483\Desktop\GSA\Excel\LoginIdPwURL.xls",1,1

Step 2 : remove the following code (it's not required)

FileName= "C:\Documents and Settings\r218483\Desktop\GSA\Excel\LoginIdPwURL.xls"
Dim ExcelSheetName
Dim My_Sheet
Dim TestmyData
Set ExcelSheetName = CreateObject("Excel.Application")
ExcelSheetName.Application.Visible = False
Set ObjActiveWkb = ExcelSheetName.Workbooks.Open(FileName)
 
For i  = 1 to 1
     msgbox i+1
     URL = objActiveWkb.Worksheets("Sheet1").Cells(i+1,1).value
     msgbox URL
                 ORG = objActiveWkb.Worksheets("Sheet1").Cells(i+1,2).value
                 msgbox ORG
                 UserID= objActiveWkb.Worksheets("Sheet1").Cells(i+1,3).value
                 MsgBox UserID
                 Pword= objActiveWkb.Worksheets("Sheet1").Cells(i+1,4).value
                 MsgBox Pword
 
Next


Step 3 : call the function as follows

URL=Datatable.value("URL",dtGlobalSheet)
ORG=Datatable.value("ORG",dtGlobalSheet)
UserId=Datatable.value("UserId",dtGlobalSheet)
Pword=Datatable.value("Pword",dtGlobalSheet)

Call LoginPadmin (URL, ORG, UserId, Pword)

Step 4: Changes in the function

Public Function LoginPadmin(URL, ORG, UserId, Pword)

'SystemUtil.Run DataTable("URL" dtGlobal)
Browser("Login").Page("Login").WebEdit("html id:=txtOrganizationId_txtText").Set URL
Browser("Login").Page("Login").WebEdit("html id:=txtUserId_txtText").Set UserId
Browser("Login").Page("Login").WebEdit("html id:=txtPassPhrase_txtText").Set Pword
Browser("Login").Page("Login").WebButton("html id:=btnGo").Click

End Function


Offline Anil shrestha

  • Jr. Member
  • **
  • Posts: 88
  • QA Analyst
Re: How to pass variable from external xls to function
« Reply #2 on: October 07, 2009, 08:14:30 PM »
Hello bharatbkakkar,
Thank you so much for the replay.
I followed your suggestion. It work but there was one issue. There are other code using Global DataSheet( For example "ActualResult" and "ExpectedResult" in the example bellow) are also looking for the value in this excel sheet that was imported, which is not expected to be there. It should have taken from the Global DataSheet of QTP table.



Datatable.ImportSheet "C:\Documents and Settings\r218483\Desktop\GSA\Excel\LoginIdPwURL.xls",1,1

URL=Datatable.value("URL",dtGlobalSheet)
ORG=Datatable.value("ORG",dtGlobalSheet)
UserId=Datatable.value("UserId",dtGlobalSheet)
Pword=Datatable.value("Pword",dtGlobalSheet)


Public Function LoginPadmin(URL, ORG, UserId, Pword)
SystemUtil.Run URL
Browser("Login").Page("Login").WebEdit("html id:=txtOrganizationId_txtText").Set ORG
Browser("Login").Page("Login").WebEdit("html id:=txtUserId_txtText").Set UserId
Browser("Login").Page("Login").WebEdit("html id:=txtPassPhrase_txtText").Set Pword
Browser("Login").Page("Login").WebButton("html id:=btnGo").Click
End Function


Call LoginPadmin(URL, ORG, UserId, Pword) { Successfully logs in with imported value from the data dable}

Browser("name:=Welcome").Page("title:=Welcome").Sync
Dim a,b,CurrentResult,ExpectedResult
Set a = Description.Create()
a("micclass").Value = "WebElement"
a("class").Value = "text expandTop"
 
Set b =Browser("name:=Welcome").Page("title:=Welcome").ChildObjects(a)
c= b.Count
'MsgBox c
 
For i = 0 to b.Count -1
 CurrentResult = b(i).GetROProperty("innertext")
 'MsgBox CurrentResult
DataTable.SetCurrentRow(i + 1)
DataTable("ActualResult", dtGlobalSheet)= CurrentResult    {It Fails here; Error says dataTable does not exist. It exist in Global Data Sheet of QTP table.}
ExpectedResult = DataTable("ExpectedResult", dtGlobalSheet)

If CurrentResult  = ExpectedResult Then
 Reporter.ReportEvent micPass,"Menu","Actual Program Administrator Menu is equal to expected result:->    " &ExpectedResult
Else
Reporter.ReportEvent micFail,"Menu","Actual Program Administrator Menu is : " &CurrentResult& " and is not equal to expected result:-> " &ExpectedResult
End If
 
Next

However if I use the following it Works without any issue:

FileName= "C:\Documents and Settings\r218483\Desktop\GSA\Excel\LoginIdPwURL.xls"
Dim ExcelSheetName
Dim My_Sheet
Dim TestmyData
Set ExcelSheetName = CreateObject("Excel.Application")
ExcelSheetName.Application.Visible = False
Set ObjActiveWkb = ExcelSheetName.Workbooks.Open(FileName)
 
For i  = 1 to 1
'     msgbox i+1
     URL = objActiveWkb.Worksheets("Sheet1").Cells(i+1,1).value
'     Msgbox URL
     ORG = objActiveWkb.Worksheets("Sheet1").Cells(i+1,2).value
'     Msgbox ORG
     UserID= objActiveWkb.Worksheets("Sheet1").Cells(i+1,3).value
'      MsgBox UserID
     Pword= objActiveWkb.Worksheets("Sheet1").Cells(i+1,4).value
'      MsgBox Pword
 
Next
Call LoginPadmin (URL, ORG, UserID, Pword)
Wait (2)
Browser("name:=Welcome").Page("title:=Welcome").Sync
Dim a,b,CurrentResult,ExpectedResult
Set a = Description.Create()
a("micclass").Value = "WebElement"
a("class").Value = "text expandTop"
 
Set b =Browser("name:=Welcome").Page("title:=Welcome").ChildObjects(a)
c= b.Count
'MsgBox c
 
For i = 0 to b.Count -1
 CurrentResult = b(i).GetROProperty("innertext")
 'MsgBox CurrentResult
DataTable.SetCurrentRow(i + 1)
DataTable("ActualResult", dtGlobalSheet)= CurrentResult  {Successfully Picks up the value from QTP globalSheet}
ExpectedResult = DataTable("ExpectedResult", dtGlobalSheet)
 
If CurrentResult  = ExpectedResult Then
 
Reporter.ReportEvent micPass,"Menu","Actual Program Administrator Menu is equal to expected result:->    " &ExpectedResult
Else
Reporter.ReportEvent micFail,"Menu","Actual Program Administrator Menu is : " &CurrentResult& " and is not equal to expected result:-> " &ExpectedResult
End If
 
Next

 ???
Does this thing make any senses or I am just mistaken?
When is the good time to Import verses Create Excel object???