Excel

[lit_73]

This is with respect to Excel sheet.I wish to know how i can connect Excel sheet with QTP and automate.Present situation i am using MSAcess.There are five tables.Is it possible to get them in one excel sheet with different sheets.
How can we write queries in QTP with excel sheet.

Kindly help me as its been long i am asking the same question.

Thanks and Regards'
Thushar

[Ankur]

Check the post on Excel Automation Model

[manojith1984]

Hi,
This would help you to query from the Excel Sheet

Code:

'Set oCnnExcel = CreateObject("ADODB.Connection")
'Set oRsExcel = CreateObject("ADODB.Recordset")

'DbExcelPath = "D:\Manojith_Others\All_others\Database.xls"
'oCnnExcel.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & Trim(DbExcelPath) & ";Readonly=1;" 'DSN
'oRsExcel.Open "SELECT * FROM [TEST_01_UC01$] WHERE IdNo IN ('123','124') ORDER BY IdNo, CompanySal, CompanyName Asc", oCnnExcel, 2, 3

'oRsExcel.MoveFirst
'Do While oRsExcel.EOF = False
'    MsgBox oRsExcel.Fields("CompanyName").Value
'    MsgBox oRsExcel.Fields("CompanySal").Value
'    oRsExcel.MoveNext
'Loop

[lit_73]

Hi Manojith,

Thanks for this piece of code.But again i have some doubts so i thought i will clear with you.

1.The syntax. "SELECT * FROM [TEST_01_UC01$] "but i am getting error on this.I am not sure if the name given is correct.
2.Actually the excel sheet name is check.xls and the database is also the same.Can you please advise me on this.

Rgds
Thushar

[lit_73]

Hi,
I have a few doubts with this code like
Database.xls points to what.In other words imagine i have n number of excel sheets so each time i should establish the same code or how is it.
Example if i have an Excel sheet named Test.xls then instead of Database i should put that name if i am not wrong.Kindly advise me if i am wrong.
DbExcelPath = "D:\Manojith_Others\All_others\Database.xls"

Regards
Thushar

[manojith1984]

Hi,
'Database.xls' is the excel files name.
You can parameterize this variable

For Example: if you have created a file 'test.xls' in path 'D:' then you can give
DbExcelPath = "D:\test.xls"

Thanks and Regards,
Manojith Ramachandran

[savita.kale]

Hi Tushar,

I hope this will help you.

Code:

Dim sFileLocation

On Error Resume Next
SystemUtil.CloseProcessByName "Excel.Exe"

sFileLocation = "C:\Input.xls"
sWorksheet = "InputData"


Set xlObj = CreateObject("Excel.Application")
xlObj.Visible = True

Set xlWorkBookObj = xlObj.Workbooks.Open(sFileLocation)

Set xlWorkSheetObj = xlWorkBookObj.Worksheets(sWorksheet)

For  i  =2 To 3
    
        sRollNo = xlWorkSheetObj.Cells(i,1)
        Browser("ConfirmationHome").Page("ConfirmationHome").WebEdit("trfrBpId").Set sRollNo
        Browser("ConfirmationHome").Page("ConfirmationHome").WebButton("Search").Click

        If Browser("ConfirmationHome").Page("ConfirmationHome").Link("DRN").Exist Then
            xlWorkSheetObj.Cells(i,2).Value = "Pass"
        Else
          xlWorkSheetObj.Cells(i,2).Value = "Fail"
        End If
    
Next

[linhke]

Hi all,
I have a excel sheet with structure:

KEY USER_NAME PASS_WORD
LOG2 niss niss

After i set randomize for USER_NAME value, i want to store the new variable value (ex: niss1234) to that cell by UPDATE query with input parameters (Sheet name, KEY value, USER_NAME field), without row, column index. Can any body tell me the solution?

Thanks and BRs,

---

Hi,

There is a run error if one of those field value is empty. Could you show me how to cover it?

Thanks and BRs,