Hi Team,
I am reading value from excel Using Recordset , giving search Criteria via userform.
Now I am looking for extracted value to pasted in listbox1. where user can select single value.
Attempted code ----
s = TextBox1.Value
SQLQuery = "SELECT * FROM [Film$] where Title like '" & s & "%'" ' filtering via wildcard value entered in textbox
ListBox1.RowSource = SQLQuery ............something like this or copy from recordset.
How to put recordset value into listbox1.
Private Sub TextBox1_Enter()
Dim s As String
Dim SQLQuery As String
s = TextBox1.Value
SQLQuery = "SELECT * FROM [Film$] where Title like '" & s & "%'"
'Run the query with the SQL string
GetQueryResults (SQLQuery)
End Sub
Sub GetQueryResults(SQLQuery As String)
Dim MovieFilePath As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws As Worksheet
Dim i As Integer
Dim RowCount As Long, ColCount As Long
'Exit the procedure if no query was passed in
If SQLQuery = "" Then
MsgBox _
Prompt:="You didn't enter a query", _
Buttons:=vbCritical, _
Title:="Query string missing"
Exit Sub
End If
'Check that the Movies workbook exists in the same folder as this workbook
MovieFilePath = ThisWorkbook.Path & "\Movies.xlsx"
'Create and open a connection to the Movies workbook
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & MovieFilePath & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
'Try to open the connection, exit the subroutine if this fails
On Error GoTo EndPoint
cn.Open
'If anything fails after this point, close the connection before exiting
On Error GoTo CloseConnection
'Create and populate the recordset using the SQLQuery
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.CursorType = adOpenStatic
rs.Source = SQLQuery 'Use the query string that we passed into the procedure
' ListBox1.RowSource = SQLQuery
'Try to open the recordset to return the results of the query
rs.Open
'If anything fails after this point, close the recordset and connection before exiting
On Error GoTo CloseRecordset
'Get count of rows returned by the query
RowCount = rs.RecordCount
Debug.Print RowCount & " row(s)", SQLQuery
'Exit the procedure if no rows returned
If RowCount = 0 Then
MsgBox _
Prompt:="The query returned no results", _
Buttons:=vbExclamation, _
Title:="No Results"
Exit Sub
End If
'Get the count of columns returned by the query
ColCount = rs.Fields.Count
'Create a new worksheet
Set ws = ThisWorkbook.Worksheets.Add
'Select the worksheet to avoid the formatting bug with CopyFromRecordset
ThisWorkbook.Activate
ws.Select
'Copy values from the recordset into the worksheet
ws.Range("A2").CopyFromRecordset rs
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Exit Sub
End Sub[/CODE]
Thanks
mg
I am reading value from excel Using Recordset , giving search Criteria via userform.
Now I am looking for extracted value to pasted in listbox1. where user can select single value.
Attempted code ----
s = TextBox1.Value
SQLQuery = "SELECT * FROM [Film$] where Title like '" & s & "%'" ' filtering via wildcard value entered in textbox
ListBox1.RowSource = SQLQuery ............something like this or copy from recordset.
How to put recordset value into listbox1.
Private Sub TextBox1_Enter()
Dim s As String
Dim SQLQuery As String
s = TextBox1.Value
SQLQuery = "SELECT * FROM [Film$] where Title like '" & s & "%'"
'Run the query with the SQL string
GetQueryResults (SQLQuery)
End Sub
Sub GetQueryResults(SQLQuery As String)
Dim MovieFilePath As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws As Worksheet
Dim i As Integer
Dim RowCount As Long, ColCount As Long
'Exit the procedure if no query was passed in
If SQLQuery = "" Then
MsgBox _
Prompt:="You didn't enter a query", _
Buttons:=vbCritical, _
Title:="Query string missing"
Exit Sub
End If
'Check that the Movies workbook exists in the same folder as this workbook
MovieFilePath = ThisWorkbook.Path & "\Movies.xlsx"
'Create and open a connection to the Movies workbook
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & MovieFilePath & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
'Try to open the connection, exit the subroutine if this fails
On Error GoTo EndPoint
cn.Open
'If anything fails after this point, close the connection before exiting
On Error GoTo CloseConnection
'Create and populate the recordset using the SQLQuery
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.CursorType = adOpenStatic
rs.Source = SQLQuery 'Use the query string that we passed into the procedure
' ListBox1.RowSource = SQLQuery
'Try to open the recordset to return the results of the query
rs.Open
'If anything fails after this point, close the recordset and connection before exiting
On Error GoTo CloseRecordset
'Get count of rows returned by the query
RowCount = rs.RecordCount
Debug.Print RowCount & " row(s)", SQLQuery
'Exit the procedure if no rows returned
If RowCount = 0 Then
MsgBox _
Prompt:="The query returned no results", _
Buttons:=vbExclamation, _
Title:="No Results"
Exit Sub
End If
'Get the count of columns returned by the query
ColCount = rs.Fields.Count
'Create a new worksheet
Set ws = ThisWorkbook.Worksheets.Add
'Select the worksheet to avoid the formatting bug with CopyFromRecordset
ThisWorkbook.Activate
ws.Select
'Copy values from the recordset into the worksheet
ws.Range("A2").CopyFromRecordset rs
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Exit Sub
End Sub[/CODE]
Thanks
mg