QTP/UFT: Excel Data Import/Export


QTP/UFT: Excel Data Import/Export at Run-time DataTable
””##########################################################

Dim WshShell
‘Dim WshSysEnv
Set WshShell = CreateObject (“WScript.Shell”)

OSType = Environment.Value(“OS”)
OsXp = “Microsoft Windows XP”
XpPath = “C:\Documents and Settings\”& Environment.Value(“UserName”) &“\My Documents\”
‘Win7Path = “C:\Users\”& Environment.Value(“UserName”) &”\Documents\”  ”User Profile
Win7Path = WshShell.ExpandEnvironmentStrings (“%USERPROFILE%”) &“\Documents\” ”Current loged on Profile

”’Import data from Local or from QC at RunTime –TableName = SheetName–
Function addQCdata(FileName,TableName)
If FilePath = “” Then ”’If Local Data path not Define
ExcelPath=(“[QualityCenter\Resources] Resources\QTP Resources\AppDAT\”& FileName)
Else ”’If Local Data path is Defined
ExcelPath=(FilePath & FileName &“.xls”)
End If
SystemUtil.CloseProcessByName(“Excel.exe”)
DataTable.AddSheet (TableName)
DataTable.ImportSheet ExcelPath, TableName, TableName
Set ExcelPath = nothing
End Function

”’Export RunTime Data to Documents Folder –TableName = SheetName–
”’Save Runtime Data in Documents Folder as a Excel format
Function SaveReport(FileName,TableName)
OSType = Environment.Value(“OS”)
UserID = Environment.Value(“UserName”)
OsXp = “Microsoft Windows XP”

If OSType = OsXp Then
DataTable.ExportSheet XpPath&FileName&“.xls”, TableName
Else
DataTable.ExportSheet Win7Path&FileName&“.xls”, TableName
End If
End Function

Rem — ############## OR ##############

”’Export RunTime Data to Documents Folder –TableName = SheetName–
Function exportData(FileName,TableName)
OSType = Environment.Value(“OS”)
UserID = Environment.Value(“UserName”)
OsXp = “Microsoft Windows XP”

If OSType = OsXp Then
DataTable.ExportSheet “C:\Documents and Settings\”& UserID &“\My Documents\”&FileName&“.xls”, TableName
Else
DataTable.ExportSheet “C:\Users\”& UserID &“\Documents\”&FileName&“.xls”, TableName
End If
Set OSType = nothing
Set UserID = nothing
Set OsXp = nothing
End Function

”’To use this Function You have to Use a SetUp.xls File  –TableName = SheetName–
”’Download the SetUp.xls File From Quality Center (ALM)
”’Define the Path in the DataPath Sheet
”’Example:
”’ScriptName : 02-UserLogin
”’LocalDataFilePath: C:\Users\userid\Documents\AppData\Automation_Data\
”’Move the ” SetUp.xls ” file to My Documents for Xp or Documents for Documents
Function AddALMData(FileName,TableName)
On Error Resume Next
SystemUtil.CloseProcessByName(“Excel.exe”)
DataTable.AddSheet (“DataPath”)
If OSType = OsXp Then
DataTable.ImportSheet XpPath&“SetUp.xls” ,“DataPath”,“DataPath”
Else
DataTable.ImportSheet Win7Path&“SetUp.xls” ,“DataPath”,“DataPath”
End If
pathRows=DataTable.GetSheet(“DataPath”).GetRowCount
For p=1 to pathRows
DataTable.GetSheet(“DataPath”).SetCurrentRow(p)
If DataTable.Value(“ScriptName”,“DataPath”)=Environment.Value(“TestName”Then
If DataTable.Value(“LocalDataFilePath”,“DataPath”) > “0” Then
FilePath= DataTable.Value(“LocalDataFilePath”,“DataPath”)
‘msgbox FilePath
else
Reporter.ReportEvent micDone, “DataFile”“App Data File is not Setup from Local”
End If
End If
Next

If FilePath = “” Then
ExcelPath=(“[QualityCenter\Resources] Resources\QTP Resources\AppDATA\”& FileName)
Reporter.ReportEvent micDone, “DataFile”“App Data File is Setup from QC”
Else
ExcelPath=(FilePath & FileName &“.xls”)
Reporter.ReportEvent micDone, “DataFile”“App Data File is Setup from Local”
End If
DataTable.AddSheet (TableName)
DataTable.ImportSheet ExcelPath, TableName, TableName
‘   DataTable.ImportSheet ExcelPath, TableName, Environment.Value(“ActionName”)     ”## You can edit script to make it Datatable Per Action
Set FilePath = nothing
Set ExcelPath = nothing
End Function

”’ How to Use It, Call it in your Script, FileName, SheetName
Call AddALMData(“AppTestDataCNN”,“UserLogon”)

rowCnt = DataTable.GetSheet(“UserLogon”).GetRowCount
For i=1 To rowCnt
DataTable.GetSheet(“UserLogon”).SetCurrentRow(i)
”’Your Condition
”’Your Condition
”’Your Condition
Next

 
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s