how to change date to boolean

Posted on 2014-12-10
Last Modified: 2015-01-16
I need to change my worksheet to pull data from the richtextbox (see picture) instead of datetimepicker and combo box.  Currently, my worksheet is pulling data from from datatimepicker = DTPTerm1 and combobox = COBTermType
I want to use the rich text box to pull the data.  In the richtextbox, i need the date and the term type letter Q or S in the box

created sheet

 CreateWorksheet(termStart1, CType(3, Excel.XlColorIndex), DTPTerm1.Value, COBTermType.SelectedItem.ToString(0), "TableStyleMedium2", xlSheet6)
picture of design and richtexbox
Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DTPTerm1.Format = DateTimePickerFormat.Custom
        DTPTerm1.CustomFormat = " "
        ' Setting the minimum date
    End Sub

    Private Sub DTPTerm1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DTPTerm1.ValueChanged
        DTPTerm1.CustomFormat = "MM/dd/yyy"
    End Sub

    Private Sub btnAddTermStart_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAddTermStart.Click
        RichTextBox1.SelectionStart = RichTextBox1.TextLength
        RichTextBox1.SelectedText = DTPTerm1.Value.ToString("MM-dd-yyyy") & " " & COBTermType.SelectedItem.ToString(0) & vbCrLf
    End Sub

    Private Sub btnClearTermStarts_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnClearTermStarts.Click
    End Sub

    Private Sub RichTextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RichTextBox1.TextChanged
    End Sub

    Private Sub CreateWorksheet(ByVal sheetName As String, ByVal colorTab As Excel.XlColorIndex, ByVal termStartDate As Date, ByVal termTypeFull As String, ByVal tableStyle As String, ByRef xlSht As Excel.Worksheet)

        xlSht.Name = sheetName
        xlSht.Tab.ColorIndex = colorTab

        Using cnn As New SqlConnection(connectionString)

            'Run SQL'
            Using mysda As New SqlDataAdapter()

                Using mycmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

                    mycmd.CommandTimeout = 0
                    mycmd.Parameters.AddWithValue("@PROGRAM_START", termStartDate)
                    mycmd.Parameters.AddWithValue("@TERM_TYPE", termTypeFull.Substring(0, 1))
                    mysda.SelectCommand = mycmd

                    Using ds As New DataSet


                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSht.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
                        For i = 0 To ds.Tables(0).Rows.Count - 1
                            For j = 0 To ds.Tables(0).Columns.Count - 1
                                xlSht.Cells(i + 2, j + 1) = _

                        'Format Table'

                        xlSht.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSht.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                        xlSht.ListObjects("Table1").TableStyle = tableStyle

                        '~~> Create Borders
                        With xlSht.UsedRange

                            With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                                .LineStyle = Excel.XlLineStyle.xlDouble
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With

                            With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                            With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                            With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                            With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                            With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                        End With


                    End Using
                End Using

            End Using

        End Using
    End Sub

    Private Function ValidateCheck() As Boolean
        If DTPTerm1.CustomFormat = " " Then
            MessageBox.Show("Please select a date.")
            Return False
        ElseIf COBTermType.SelectedIndex = -1 Then
            MessageBox.Show("Please select a term type.")
            Return False

        End If
        Return True
    End Function

    Private Sub btnCreateReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReport.Click

        If ValidateCheck() = False Then
            Exit Sub
        End If

        'naming excel sheets'

        Dim termStart1 As String
        Dim termStart2 As String
        'Dim termStart3 As String
        'Dim termStart4 As String
        'Dim termStart5 As String
        'Dim termStart6 As String

        'Name each Excel Sheet'
        'termStart1 = DTPTerm1.Value.ToString("MM-dd-yyyy") & " " & COBTermType.SelectedItem.ToString(0)
        termStart1 = RichTextBox1.Lines(0)
        termStart2 = RichTextBox1.Lines(1)
        'termStart2 = RichTextBox1.text
        'termStart3 = DTPTerm3.Value.ToString("MM-dd-yyyy") & " " & COBTermType3.SelectedItem.ToString(0)
        'termStart4 = DTPTerm4.Value.ToString("MM-dd-yyyy") & " " & COBTermType4.SelectedItem.ToString(0)
        'termStart5 = DTPTerm5.Value.ToString("MM-dd-yyyy") & " " & COBTermType5.SelectedItem.ToString(0)
        'termStart6 = DTPTerm6.Value.ToString("MM-dd-yyyy") & " " & COBTermType6.SelectedItem.ToString(0)

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlSheet6 As Excel.Worksheet
        Dim xlSheet5 As Excel.Worksheet
        'Dim xlSheet4 As Excel.Worksheet
        'Dim xlSheet1 As Excel.Worksheet
        'Dim xlSheet2 As Excel.Worksheet
        'Dim xlSheet3 As Excel.Worksheet
        'Dim xlSheet7 As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)

        xlWorkBook.Sheets.Add(, , 4)
        xlApp.Visible = True

        'Create and run each worksheet'

        xlSheet6 = CType(xlWorkBook.Worksheets("Sheet6"), Excel.Worksheet)
        CreateWorksheet(termStart1, CType(3, Excel.XlColorIndex), DTPTerm1.Value, COBTermType.SelectedItem.ToString(0), "TableStyleMedium2", xlSheet6)
        With xlSheet6
            .Range("O:O").Cells.NumberFormat = "$  ###,###.00"
        End With

        'xlSheet5 = CType(xlWorkBook.Worksheets("Sheet5"), Excel.Worksheet)
        'CreateWorksheet(termStart1, CType(3, Excel.XlColorIndex), (RichTextBox1.Lines(0) = DTPTerm1.Value.ToString("MM-dd-yyyy")), (RichTextBox1.Lines(1) = COBTermType.SelectedItem.ToString(1)), "TableStyleMedium2", xlSheet6)
        'With xlSheet5
        '    .Range("O:O").Cells.NumberFormat = "$  ###,###.00"
        'End With

    End Sub

    Private Sub btnExitApplication_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExitApplication.Click
        Dim x As Integer = MsgBox("Are you sure you want to quit?", MsgBoxStyle.YesNo)
        If x = DialogResult.Yes Then
        End If
    End Sub

End Class

Open in new window

Question by:cmed
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 40492357
i am lost... what is the issue here? which line? error message? output from current code? whats is wrong here? what do you want? show the output that you want...
LVL 48

Accepted Solution

PortletPaul earned 500 total points
ID: 40492881
I also don't see how the title "how to change date to boolean" relates to the question body

IF you do want a boolean from date, then you must define the rules, e.g.

if date > today then 1 else 0

Author Comment

ID: 40493783
Sorry for the confusion (that was the error message that I was getting. My issue is this.  I am creating a report that I am using a worksheet to populate data from the database into excel.  I am making modifications to the program.  I want to application to use the date and term from the richtextbox to populate the data.
LVL 27

Expert Comment

ID: 40503944
(that was the error message that I was getting.

What was the error message you were getting? What does "that" refer to? Where did the error show up? What specifically were you doing when it showed up?


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
This video teaches viewers about errors in exception handling.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question