wayneray
asked on
Want to Disply Multiple Rows in Text Box from Excel Workbook using VB.Net
I am writing a desktop application using VB.Net and an Excel Workbook as the database. I can display one row easily using the code below. How do I display all rows from the workbook that have the same date? Thanks in advance.
Here is the code I am using. I have attached the Excel File.
Private m_sConn1 As String = "Provider=Microsoft.ACE.OL EDB.12.0;" & _
"Data Source=c:\computers\vb project\Sweetheart\mydates .xlsx;" & _
"Extended Properties=""Excel 12.0;HDR=YES"""
Private Sub cmdFind_Click(sender As Object, e As EventArgs) Handles cmdFind.Click
Dim cn As New System.Data.OleDb.OleDbCon nection(m_ sConn1)
Dim appt, apdt, amessage As String
Dim format = "MM,dd,yyyy"
Dim begindt As Date = dtpBegin.Value.ToString(fo rmat)
Dim enddt As Date = dtpEndDt.Value.ToString(fo rmat)
Dim cm As New OleDbDataAdapter("Select * From [Sheet1$] where Date = '" & begindt & "'", cn)
Dim ds As DataSet = New DataSet()
cn.Open()
cm.Fill(ds)
If TextBox1.Text = "" Then
Debug.WriteLine(vbCrLf & "Bills:" & vbCrLf & "=============")
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows 'Show results in output window
'Debug.WriteLine(System.St ring.Forma t("{0,-15} {1, -6}{2}", _
' dr("Event"), dr("Date"), dr("Message")))
Next
appt = dr("Event")
apdt = dr("Date")
amessage = dr("Message")
TextBox1.Text = "Category " & "Date " & "Info " & vbCrLf & vbCrLf & appt & " " & apdt & " " & amessage
End If
End Sub
mydates.xlsx
Here is the code I am using. I have attached the Excel File.
Private m_sConn1 As String = "Provider=Microsoft.ACE.OL
"Data Source=c:\computers\vb project\Sweetheart\mydates
"Extended Properties=""Excel 12.0;HDR=YES"""
Private Sub cmdFind_Click(sender As Object, e As EventArgs) Handles cmdFind.Click
Dim cn As New System.Data.OleDb.OleDbCon
Dim appt, apdt, amessage As String
Dim format = "MM,dd,yyyy"
Dim begindt As Date = dtpBegin.Value.ToString(fo
Dim enddt As Date = dtpEndDt.Value.ToString(fo
Dim cm As New OleDbDataAdapter("Select * From [Sheet1$] where Date = '" & begindt & "'", cn)
Dim ds As DataSet = New DataSet()
cn.Open()
cm.Fill(ds)
If TextBox1.Text = "" Then
Debug.WriteLine(vbCrLf & "Bills:" & vbCrLf & "=============")
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows 'Show results in output window
'Debug.WriteLine(System.St
' dr("Event"), dr("Date"), dr("Message")))
Next
appt = dr("Event")
apdt = dr("Date")
amessage = dr("Message")
TextBox1.Text = "Category " & "Date " & "Info " & vbCrLf & vbCrLf & appt & " " & apdt & " " & amessage
End If
End Sub
mydates.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Scott's solution helped me overcome a block in the road. His response offered me more than I asked for and I appreciate his answer.
ASKER