QTP/UFT tutorial for beginners – Importing excel sheet data into QTP/UFT global sheet

Full Code Below:


 

dataTable.AddSheet "dtTestWorksheet1"
dataTable.AddSheet "dtTestWorksheet2"
dataTable.ImportSheet Environment("TestDir")&"\PracticeSheet.xlsx", "TestWorksheet1", "dtTestWorksheet1"
dataTable.ImportSheet Environment("TestDir")&"\PracticeSheet.xlsx", "TestWorksheet2", "dtTestWorksheet2"

MsgBox dataTable.value("TestColumn", "dtTestWorksheet1")
MsgBox dataTable.value("TestColumn", "dtTestWorksheet2")


 

Hi. Today, I am going to show you how to import data from an existing excel sheet, into our QTP/UFT global sheet. The global sheet is a built in table within QTP/UFT, which we can use to store and reuse data.

1. I created a practice sheet for us which we can use for this example. Look at the image below. When we import the sheet, the 1st cell in column A always becomes the column name in our QTP/UFT global sheet. The second cell will contain the value that we are going to be pulling from the sheet. And the worksheet name can be called upon to import the data from that specific sheet.

Download the practice sheet: PracticeSheet

2. Create a test in QTP/UFT and call it PracticeScript. Save the script in QTP/UFT. After you save the script, find the directory that this script is located in, and save your PracticeSheet you downloaded above, into this directory.

3. Now, we can access the data in this excel sheet through our driver script. Since our excel sheet has two worksheets, we need to add two worksheets in our global sheet as well. We can do that by:

dataTable.AddSheet "dtTestWorksheet1"
dataTable.AddSheet "dtTestWorksheet2"

the dtTestWorksheet1 and dtTestWorksheet2 are what I named the sheets. The name can be anything, but I like to relate the names to the actual sheets we are going to import from excel.

4. Now that we created two sheets within our global sheet, let's import the data from our practice excel sheet.

dataTable.ImportSheet Environment("TestDir")&"\PracticeSheet.xlsx", "TestWorksheet1", "dtTestWorksheet1"
dataTable.ImportSheet Environment("TestDir")&"\PracticeSheet.xlsx", "TestWorksheet2", "dtTestWorksheet2"

These are important to know:

  • PracticeSheet.xlsx    - This is the actual name of our excel sheet from where we want to import the data
  • TestWorksheet1        - This is the actual name of our worksheet in excel, which we will be importing
  • dtTestWorksheet1    - This is the name of the global sheet we created from step 3, where we will store our imported data

5. If you have excel open, close the application. Now run the QTP/UFT script. When the results report displays, click on the Data tab in the bottom hand corner.

You should see our newly created global sheets along with the imported data.

6. Lastly, let's read the data from our global sheet.

MsgBox dataTable.value("TestColumn", "dtTestWorksheet1")
MsgBox dataTable.value("TestColumn", "dtTestWorksheet2")

  • The TestColumn is the column name within the global sheet.
  • The dtTestWorksheet1 is the global sheet name

Congratulations! If you did this correctly, you should see two messages with values "Value from sheet 1" and "Value from sheet 2". This is how simple it is to create and use data from excel! Please post your comments and questions below!

  • varshali

    when we import the .xlsx datasheet in UFT we get till columns IV, whereas the rest of the columns are not imported and we have data after col VI also int he excel.please help.

    • QTPtutorialnet

      Hey Varshali. Send me the code you’re using to import the excel sheet?

Pin It on Pinterest

Clef two-factor authentication