Forum Discussion
srigudav
10 years agoOccasional Contributor
Hi All,
hank you for the updates. I was able to do by the below code.
Sub ReadExcel
QryXls_GetData("C:\driversheet.xls")
End Sub
'sFileName= xls file name with path
'Sheetname = table name
'VRstatmt = where statment for query
'Colname=Column name to fetch value from
'==============================================
Public Function QryXls_GetData( sFileName)
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = "&H0001"
sql_text="Select * FROM [driver$]where [Scenario Required]='yes'"
'1) Create an ADODB connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
msgbox(sFileName)
'2) Open connection'
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sFileName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"
'3) Create a Recordset'
Set objRecordSet = CreateObject("ADODB.Recordset")
'4) Execute SQL and store results in reocrdset'
objRecordset.Open sql_text , objConnection, adOpenStatic, adLockOptimistic, adCmdText
'5) Read all fields data and store in a array'
'For Multiple records
'=================
ReDim SQLExpectedData(objRecordset.recordcount -1)
For i=0 to objRecordset.recordcount -1 'objRecordset.fields.item(1).properties.count
SQLExpectedData(i)= objRecordset.fields("Test case")
functionname = "call "& objRecordset.fields("Test case")
msgbox (functionname)
execute functionname
objRecordset.movenext
msgbox(SQLExpectedData(i))
Next
'6) Close and Discard all variables '
objRecordset.Close
objConnection.Close
End Function
hank you for the updates. I was able to do by the below code.
Sub ReadExcel
QryXls_GetData("C:\driversheet.xls")
End Sub
'sFileName= xls file name with path
'Sheetname = table name
'VRstatmt = where statment for query
'Colname=Column name to fetch value from
'==============================================
Public Function QryXls_GetData( sFileName)
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = "&H0001"
sql_text="Select * FROM [driver$]where [Scenario Required]='yes'"
'1) Create an ADODB connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
msgbox(sFileName)
'2) Open connection'
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sFileName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"
'3) Create a Recordset'
Set objRecordSet = CreateObject("ADODB.Recordset")
'4) Execute SQL and store results in reocrdset'
objRecordset.Open sql_text , objConnection, adOpenStatic, adLockOptimistic, adCmdText
'5) Read all fields data and store in a array'
'For Multiple records
'=================
ReDim SQLExpectedData(objRecordset.recordcount -1)
For i=0 to objRecordset.recordcount -1 'objRecordset.fields.item(1).properties.count
SQLExpectedData(i)= objRecordset.fields("Test case")
functionname = "call "& objRecordset.fields("Test case")
msgbox (functionname)
execute functionname
objRecordset.movenext
msgbox(SQLExpectedData(i))
Next
'6) Close and Discard all variables '
objRecordset.Close
objConnection.Close
End Function
Related Content
- 4 years ago
- 6 years ago
Recent Discussions
- 13 hours ago
- 2 days ago