Custom Search

Friday, May 6, 2011

Retrive multiple column and Rows values from Excel sheet with Query

myarray=GetDataFromExcel("C:\QTPExcelTest\Excel.xls","WCH","ColName1,ColName2","VisitGroup='Prenatal Visit'")
RowCount=UBound(myarray,2)
colVal=0
For c=0 to colVal
   For r=0 to RowCount
       MsgBox myArray(r,c)
   Next
Next

Function GetDataFromExcel(strPath, strSheet, strRqfields,strWhereClause)
Dim cn
''Create connection to Excel 2003 sheet
Set cn = CreateObject("ADODB.Connection")
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString = "Data Source="&strPath&";" &"Extended Properties=Excel 4.0;"
cn.Open
If err.number <> 0 Then
   Print Err.number
End if
''' Execting Query
Query = "Select "& strRqfields &" FROM [" & StrSheet & "$] where "& strWhereClause
Set rs = CreateObject("ADODB.recordset")
Set rs = cn.Execute(Query)
If Not Rs.EOF Then
   myArray = Rs.GetRows()
End If
GetDataFromExcel=myArray
rs.close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Function

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.