QTP: Working with Excel Spreadsheets

Example of Code:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.WorkBooks.Open("J:\My Documents\users.xls")
Set objDriverSheet = objWorkbook.Worksheets("users")

c = objDriverSheet.usedrange.rows.count

'We Start Looking at Number two since first row is column names
For i=2 to c
userId = objDriversheet.cells(i, 1)
If expectedUserId = userId Then
msgbox userId
End If
Next

Set objExcel = Nothing
Set objWorkbook = Nothing
Set objDriverSheet = Nothing

QTP: Tips and trix

Here i will gather several lessons that I have learned while working in QTP/QC. Lessons that I want to share but are not complicated enough to warrant their own post. I will continue to add new lessons here as I learn more about QTP/QC.

1) Sync Problems When Uploading With The QCUtil API
When uploading a resource to QC using QCResource.uploadResource the file is uploaded to QC, but it is not synced with the datatable. So if you try to fetch it using DataTable.import or .importSheet you will get a version of that might not match with the one you uploaded.
Workaround: Use QCResource.downloadResource instead of import. That will go straight for the file and you will not suffer any sync problem.

2) "This files is used by somebody else"
If your QTP breaks down while working with a checked out file you might get a message saying something like "This file is checked out by somebody else". And you will get a error message in QC if you try to check in the file, even though QC recognised it is you who checked out the file.
Fix: Open the QC Connection dialoge from QTP. Disconnect and reconnect. This might fix it.

3) Version management in QC from QTP
Check out files with:

Resource.VC.CheckOut ""

And check the back in with

Resource.VC.CheckIn "Comments"

Note: Resource should be a QCResource object

4) Asserts
You can use the Reporter object to report fail and pass in QC/QTP

Reporter.ReportEvent micFail, "name", "detailed comment, e.g. X is not equal Y"
Reporter.ReportEvent micFail, "name", "detailed comment, e.g. everything seems to be in order"

5) New Line/Page Break
Use vbCrLf to make a Page Break and Carrige Return in vbscript
Example:

msgbox "Row one." + vbCrLf + "Rwo two!"

VBScript: Working with Windows Environmental Variables

Option explicit

'Declare Variables
Dim WshShl, Shell, UserVar

'Set objects
Set WshShl = WScript.CreateObject("WScript.Shell")
Set Shell = WshShl.Environment("User")

'Read variable
UserVar = Shell("jonas123")

'Output value to msgbox
WScript.Echo "Your name is " & UserVar & "!"

Shell("jonas123") = "monkey"

'Cleanup Objects
Set WshShl = Nothing
Set Shell = Nothing

'Exit Script
WScript.Quit()

QTP: Read from/Write to Test Resources in QC

Finally found the answer to how to Write to Test Resources. It's possible to use the QC OTA APIs to export it to QC. I made a function for loading and saving test recourses to and from QC.

Update 1
Added code for checking in and out of QC with comment

Update 2
Changed LoadResource function so it uses DownloadResoruce method from the API. Works much better that way.

Option Explicit

Function LoadResource(ResourceName)
Dim Resource, TempFolder

' Setting Temp Folder
TempFolder = environment("SystemTempDir")

' Load Resource
Set Resource = GetResource(ResourceName)

' Download Resource To Temp, The Entire Data Table is Downloaded
Resource.DownloadResource TempFolder, True

DataTable.AddSheet(SheetName)

DataTable.Import TempFolder & "\" & Resource.Filename
End Function

Function SaveResource(ResourceName)
' Dim values
Dim Connection, TempFolder, ResourceFactory, ResourceList, Resource, ItemCount, CurrentItem

' Create a Connection To QC
Set Connection = QCUtil.QCConnection

' Setting Temp Folder
TempFolder = environment("SystemTempDir")

' Create Resource Obejects
Set ResourceFactory = Connection.QCResourceFactory
Set ResourceList = ResourceFactory.NewList("")
Set Resource = Nothing

' Loops through all items and returns the correct one
For ItemCount = 1 To ResourceList.Count
CurrentItem = ResourceList.Item(ItemCount).Name
If UCase(CurrentItem) = UCase(ResourceName) Then
Set Resource = ResourceList.Item(ItemCount)
End If
Next

Set ResourceFactory = Nothing
Set ResourceList = Nothing

Resource.VC.CheckOut ""

' Export Datatable to Temp Directory
Datatable.Export TempFolder & "\" & Resource.Filename

' Upload Datatable in Temp to Resources Folder
Resource.UploadResource TempFolder, True
Resource.VC.CheckIn "Automated check-in from QTP by SaveResource"

Set Resource = Nothing

End Function

QTP: Do things on first and last row

If DataTable.GetCurrentRow = 1 Then
Dim dttable
dttable = "[QualityCenter\Resources] Resources\TestData"
DataTable.Import(dttable)
End If

'Do Stuff

If DataTable.GetCurrentRow = DataTable.GetRowCount Then
Export Stuff
End If

QTP: Working with QTP Environment Variables

This posts describes how to Working with QTP Environment Variables in vbscript. Note that this is NOT Windows Environment Variables ( see seperate post on that issue ).

Different Types of Environmental Variables
There are 3 kinds Envrionemental Variables
Built-In ( Read only )
User defined internal ( Read and Write )
User defined external ( Saved in XML on Disc or in QC, Read Only )

Add test variable
For the sake of this post I added a environment variable called "jonas"
In QTP: In File > Settings > Environment > Variable type: User-defined add a internal user varible and give it a value. I gave mine 'apa'

Read
Reading the environment variable is now easy

'Save to local variable
UserVar = environment("jonas")
'Now UserVar contains 'apa'

or

'Show messagebox with environment variable
msgbox environment("jonas")
' "apa" is displayed

Write
Writing is even easier ( Can only be done for internal user variables )

' Save new value to environment variable
environment("jonas") = "monkey"

Note, this is not saved after the test script has finished.

List of Built-In Variables
ActionIteration
ActionName
ControllerHostName
GroupName
LocalHostName
OS
OSVersion
ProductDir
ProductName
ProductVer
ResultDir
ScenarioId
SystemTempDir
TestDir
TestIteration
TestName
UpdatingActiveScreen (true/false)
UpdatingCheckpoints (true/false)
UpdatingTODescriptions (true/false)
UserName
VuserId

Printing all Built-In Environment Variables
Here is code for showing a msgbox with all the built-in variables with names and values.

a =Array("ActionIteration", "ActionName", "ControllerHostName", "GroupName", "LocalHostName", "OS", "OSVersion", "ProductDir", "ProductName", "ProductVer", "ResultDir", "ScenarioId", "SystemTempDir", "TestDir", "TestIteration", "TestName", "UpdatingActiveScreen", "UpdatingCheckpoints", "UpdatingTODescriptions", "UserName", "VuserId")
message =""

for each x in a
message = message & x & ":" & environment(x) & Chr(13) & Chr(10)
next

msgbox(message)

QTP: How to Read or Write Windows Envrionment Variables

It is easy to read and write Windows Environment Variables from QTP using VBScript. Note that this is NOT done using what is called "Environment Variables" in QTP.

Add test variable
For the sake of this post I added a environment variable to windows called "jonas123"
In Xp: In Control Panel > System > Advanced Tab > Environment Variables add a user varible and give it a value. I gave mine 'apa'

Setup
The following code gives you the basic objects for manipulating windows Environment Variables

Option explicit

'Declare Variables
Dim wScript, Shell, UserVar
'Set objects
set wScript = CreateObject("WScript.Shell")
Set Shell = wScript.Environment("User")

Read
Reading the environment variable is now easy

'Save to local variable
UserVar = Shell("jonas123")
'Now UserVar contains 'apa'

or

'Show messagebox with environment variable
msgbox Shell("jonas123")
' "apa" is displayed

Write
Writing is even easier

' Save new value to environment variable
Shell("jonas123") = "monkey"

Adding a variable
Just write to a variable that does not exist

Shell("jonas1234") = "banana"

What about system environment variables?
In your setup code just load "System" Environment instead of "User"

Set Shell = wScript.Environment("System")