How to read the contents of external excel sheet

[qtplearner88]

Hi All,

I have one application which requires many user credentials. I am planning to save it in excel sheet and pass those at run time. Please help me in how to write a QTP script which will read username and password columns and pass it to the application.

Looking forward to hear from u all... Thanks!!


Regards
QTP learner

[sshukla12]

Hi,
The easiest way to do is to load the external sheet during run time in ur data table and use data table to insert the username and password for the same.

Let me know in case of any further help.

Regards,
Sankalp

[qtplearner88]

Hi Sankalp,
How to do this with descriptive programming?? I need to learn that too..Pls help..

Regards
QTP learner

[sshukla12]

Hi,

Data table import/export is different from Descriptive programing, if u want to implement DP u can use it in assigning the username and password but for handling external excel file DP is not the correct approach, and to learn DP u can surf through this site only and its an easy and interesting topic.

Let me know if I can help u more.

Regards,
Sankalp

[qtplearner88]

Hi,
Thanks a lot.. sure I will come bak if I am struck anywhere...Thanks Again

[hammersandnails]

Sorry its late, but if you'd prefer to do what you originally wanted this might help:

Code:

Public ref

Function LoadfromExcel(filename)
'Loads values from constantxls file into memory
    Dim objExcel
    Dim objWorkbook
    Set ref = CreateObject("Scripting.Dictionary")
    ref.CompareMode = VBTextCompare    
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open (filename)
    objExcel.Visible = True
    objExcel.Sheets("Constants").Select
    row = 2 'first data row in xls
    
    'fill dictionary with external constants
    While (objExcel.Cells(row,1).Value<>"")
        ref.Add objExcel.Cells(row,1).Value,objExcel.Cells(row,2).Value
        row = row + 1
    Wend  

    objExcel.quit
End Function

[supputuri]

Hi Guys,

I know I am late but always it's good habit to use the ADODB connection to read the values from excel files so that we can read the data from different kinds of excel files( .xls, .xlsm, .xlsx.....). Please let me know if you need any further information how to use the ADODB connection and read the data, which will save lot of time and resources.

[qtplearner88]

Hi,

Thnks a lot for ur reply.

Can u explain in detail with an example so that it would be more easy for us to understand?

Thanks
QTPLearner

[supputuri]

Hi,

I have posted a function which will provide the required info. Please click on the below link and follow the instructions.

https://www.learnqtp.com/forums/Thread-G...connection

[sshukla12]

Thnks, Its great.

Regards,
Sankalp