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
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.