How to Import, Read, Change Data from EXCEL in QTP/UFT

Spreadsheets remain the most common place where testers keep their input values, expected results, and environment settings. QTP, now sold as OpenText UFT One after the product moved from HP to Micro Focus and then to OpenText, reads those spreadsheets through the DataTable object. The sections below show the exact statements used to import a workbook, count its rows, read individual cells, change values, and write the sheet back to disk.

Steps to Import, Read, Change Data from EXCEL

Consider that you want to import the following Sales.xls workbook into your test.

Once the file is imported into UFT, the top row becomes the column header, so structure your data accordingly. Anything in row 1 is treated as a parameter name rather than as test data, and every remaining row is counted as a data row.

The syntax to import an entire Excel file is shown below.

DataTable.Import(FileName)

In our case the statement becomes the following. VBScript does not need parentheses when a method is called as a statement, so the plain form is used here.

DataTable.Import "D:\Automation\Sales.xls"

After the run, the imported rows appear in the run-time Data pane exactly as they were laid out in the workbook.

The syntax to import one particular sheet is shown next. SheetSource is the sheet inside the workbook and SheetDest is the destination sheet in the run-time data table.

DataTable.ImportSheet FileName, SheetSource, SheetDest

Use the GetRowCount method to get the number of data rows in a sheet. Each statement must sit on its own line.

DataTable.Import "D:\Automation\Sales.xls"
row = DataTable.GetSheet(1).GetRowCount
MsgBox row

The message box that appears reports the row total for the imported sheet.

To move the pointer to a specific row, use the SetCurrentRow method. The second argument of DataTable.Value is the sheet number, not the row number, which is why the row pointer has to be moved separately.

DataTable.Import "D:\Automation\Sales.xls"

' In the code below, 1 is the sheet number
DataTable.SetCurrentRow 1
Row1 = DataTable.Value("Year", 1)

DataTable.SetCurrentRow 2
Row2 = DataTable.Value("Year", 1)

MsgBox "Year Row 1 = " & Row1 & "    Year Row 2 = " & Row2

Both values are read from the same column but from two different rows, and the message box prints them side by side.

Use the Value method on the left of an assignment to change data in the imported sheet. The change lives in memory until you call Export, which writes the run-time data table back to an Excel file.

DataTable.SetCurrentRow 1
DataTable.Value("Year", 1) = 2026
DataTable.Export "D:\Automation\Sales_Updated.xls"

The edited cell is visible in the run-time Data pane straight after the assignment runs.

Opening the exported workbook confirms that the modified values were saved to disk rather than kept only for the duration of the run.

How to Read, Change and Export Excel Data: A Complete Example

The snippets above each demonstrate one method. In a real data-driven test they are combined into a single loop that walks every row, calculates something, stores the result, and exports the sheet. Assume the workbook Sales.xls holds the following four columns and three data rows.

The script below adds a Revenue column, fills it row by row, keeps a running total, and writes the finished sheet to a new file. AddParameter is used because the column does not exist in the source workbook; calling it for a column that already exists raises an error.

' Read, calculate and export Excel data in UFT One
Dim sh, rowCount, i, units, price, revenue, total

DataTable.Import "D:\Automation\Sales.xls"

Set sh = DataTable.GetSheet(1)
sh.AddParameter "Revenue", ""

rowCount = sh.GetRowCount
Print "Total rows imported: " & rowCount

total = 0
For i = 1 To rowCount
    DataTable.SetCurrentRow i

    units   = DataTable.Value("Units", 1)
    price   = DataTable.Value("Price", 1)
    revenue = CInt(units) * CInt(price)

    DataTable.Value("Revenue", 1) = revenue
    total = total + revenue

    Print "Row " & i & " | Year: " & DataTable.Value("Year", 1) & _
          " | Region: " & DataTable.Value("Region", 1) & _
          " | Revenue: " & revenue
Next

Print "Grand Total Revenue: " & total

DataTable.Export "D:\Automation\Sales_Revenue.xls"
Set sh = Nothing

Output:

Total rows imported: 3
Row 1 | Year: 2023 | Region: East | Revenue: 3000
Row 2 | Year: 2024 | Region: East | Revenue: 3750
Row 3 | Year: 2025 | Region: West | Revenue: 6000
Grand Total Revenue: 12750

Three points in this script matter more than the rest. First, GetRowCount returns 3 rather than 4 because the header row is consumed as column names. Second, SetCurrentRow is called at the top of every iteration; without it, all three iterations would read the same row. Third, values coming out of the data table are strings, so CInt is applied before the multiplication to avoid string concatenation instead of arithmetic. Skipping DataTable.Export would leave the calculated revenue visible in the Data pane but absent from any file on disk.

DataTable Object vs Excel COM Object in UFT

The DataTable object is not the only way to reach a spreadsheet. UFT scripts can also create an Excel.Application COM object and drive Excel directly. The two approaches solve different problems.

The practical rule is simple. Use the DataTable object for straightforward parameterisation, where each spreadsheet row becomes one iteration of an automation testing run. Switch to the COM object only when you need something the data table cannot express, such as reading a formula result, writing a formatted report, or comparing two workbooks in the same script. The example below reads one cell, writes a status back, and shuts Excel down properly.

Dim xlApp, xlBook, xlSheet, cellValue

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = False

Set xlBook  = xlApp.Workbooks.Open("D:\Automation\Sales.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")

cellValue = xlSheet.Cells(2, 1).Value
Print "Cell A2 contains: " & cellValue

xlSheet.Cells(2, 5).Value = "Reviewed"
xlBook.Save

' Release everything, in this order
xlBook.Close False
xlApp.Quit
Set xlSheet = Nothing
Set xlBook  = Nothing
Set xlApp   = Nothing

Output:

Cell A2 contains: 2023

Cell A2 holds 2023 because row 1 of the worksheet is the header row and column A is Year. Notice that COM addressing is one-based and counts the header, whereas DataTable.SetCurrentRow 1 points at the first data row.

Common Errors When Using Excel with UFT and How to Fix Them

Most Excel problems in UFT come from a small set of repeatable mistakes. Recognising them saves a great deal of debugging time.

• Excel stays in memory. Close the workbook, call xlApp.Quit, then release the sheet, workbook, and application objects in that order. Releasing the application first leaves a live reference and keeps the process alive.
• Changes disappear after the run. DataTable.Value writes to the run-time data table only. Without DataTable.Export, nothing reaches the file system.
• “The column does not exist” or an invalid parameter error. The column name passed to DataTable.Value must match the header cell exactly, including trailing spaces. Use AddParameter to create a column before writing to it.
• Every iteration reads the same values. SetCurrentRow was not called inside the loop, so the row pointer never moved.
• Off-by-one row counts. GetRowCount excludes the header row. A workbook with a header and ten records returns 10, not 11.
• Path and permission failures. Hard-coded paths such as D:\Automation\Sales.xls break on other machines. Build the path from an environment variable so the same script runs on every agent.
• Merged cells and multiple header rows. The import expects exactly one header row of unique names. Merged cells produce blank or duplicated parameter names.
• Sharing violation on export. The destination file cannot be open in Excel while Export runs. Close it first, or export to a timestamped file name.

A useful habit is to wrap COM cleanup in an On Error Resume Next block placed in a dedicated recovery function, so a mid-script failure still shuts Excel down. When Excel data feeds a formal test case repository, the same discipline applies to results: export once, verify the file exists, then attach it to the run.

To go further with the topics touched on here, work through the full QTP/UFT tutorial for the wider framework, learn how to push results into ALM from UFT, review the fundamentals in software testing, and compare the approach with data-driven scripting in Selenium.

FAQs