# 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.

For example:

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.

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