Highlighted
Absent Member.
Absent Member.
1929 views

Retrieve a column name & column number from excel

Jump to solution
I need to find the first column that = Nav1 and place that
column number into a variable so I can use it again later.

EX:

Here are some columns in my excel

A B C D E Nav1 Nav2 Nav3 Nav4

So I would need some code that would go into excel and come
back with the number 6. Since Nav1 starts on column 6.

Note: The column location for Nav1 can change.

Thanks,
Steve

I found something similar here, but it isn't exactly what I am looking for.

http://funandknowledge.blogspot.com/2009/01/to-retrieve-column-names-from-database.html
0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.
Absent Member.
Hi Steve,

If you are importing the excel sheet into the QTP datatable then you can use the below code.

Step 1: Import the excel into the QTP datatable
DataTable.ImportSheet "",1,Global

Step2: Get the total no of column present in the datatable
str_TotalColumn = DataTable.GetSheet("Global").GetParametercount

Step3: Use for loop to check the name of each columns. If match found then it will return the current index of the column
For i = 1 To str_TotalColumn
str_ColumnName = DataTable.GetSheet("Global").GetParameter(i).Name
If str_ColumnName = "Nav1" Then
str_Index = i
End If
Next

Hope this is what you are looking for.

Thanks
Arpan

View solution in original post

1 Reply
Highlighted
Absent Member.
Absent Member.
Hi Steve,

If you are importing the excel sheet into the QTP datatable then you can use the below code.

Step 1: Import the excel into the QTP datatable
DataTable.ImportSheet "",1,Global

Step2: Get the total no of column present in the datatable
str_TotalColumn = DataTable.GetSheet("Global").GetParametercount

Step3: Use for loop to check the name of each columns. If match found then it will return the current index of the column
For i = 1 To str_TotalColumn
str_ColumnName = DataTable.GetSheet("Global").GetParameter(i).Name
If str_ColumnName = "Nav1" Then
str_Index = i
End If
Next

Hope this is what you are looking for.

Thanks
Arpan

View solution in original post

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.