Using data from an Excel spreadsheet in QuickTest Pro
I have been trying to figure out how to do this for a little while.. Here is my situation:
I have an Excel spreadsheet (lets call this "workbook.xls" and I have two sheets that I need to use for my script. One sheet has a column of data "columnA" and I am trying to put cell A2 (A1 is a header) into my QTP script to use.
I want to be able to use this specific value and then process some data and then use the next cell, A3 to process another procedure/process, and so on.. Is this possible?
In other languages, I know that I could use a for loop to iterate through the data, but I am getting stuck on trying to process the data and then going back through the loop to get the data.
Excel may have A2 as "1" and A3 as "2". I want to be able to put the value of A1 into an input field on the browser.. Then I want to be able to do some actions (clicking, writing to another file, etc) with this value.. Then I want to move onto the value A3 and put the value of A3 into that same input field as before and then do some other actions (that may not be what I did with A2). I am wondering how I could achieve this scenario.
Thanks in advance.
Yes, it's possible. Assuming I understood your question correctly you could do something like this:
Set xl = CreateObject("Excel.Application") xl.Visible = True 'set to False for production Set wb = xl.Workbooks.Open("C:\path\to\workbook.xls") Set ws = wb.Sheets(1) firstRow = ws.UsedRange.Rows(1).Row lastRow = firstRow - 1 + ws.UsedRange.Rows.Count For i = firstRow+1 To lastRow val = ws.Cells(i, 1).Value 'get value from cell 'do stuff with val Next wb.Close xl.Quit
A test's DataTable is an Excel spreadsheet. You could import your data into your test's DataTable in QTP - in the default sheet(s) or add your own - and then access them in code:
Set sheet = DataTable.GetSheet "Sheet Name" numRows = sheet.GetRowCount For index = 0 To numRows value = sheet.GetParameter(index) 'Do something Next