cmed
asked on
how to change date to boolean
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.T oString(0) , "TableStyleMedium2", xlSheet6)
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
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
RichTextBox1.Clear()
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.Activate()
xlSht.Name = sheetName
xlSht.Tab.ColorIndex = colorTab
Using cnn As New SqlConnection(connectionString)
cnn.Open()
'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
mysda.Fill(ds)
'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()
Next
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) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next
'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
xlSht.Columns.AutoFit()
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.")
COBTermType.Focus()
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
Me.Close()
End If
End Sub
End Class
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
(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?
Tom