cmed
asked on
using richboxtext how can i add the second date to the sheet
I am trying to take the selected items from the rich text box and inserted them into my worksheet. I was able to get the first date 12/1/2014 semester to work, but not the second date 1/5/15 quarter. Any suggestions?
ASKER
@ Rgonzo1971
for the richtextbox no.
for the richtextbox no.
without knowing where the data is and how it is called
My.Resources.SQL_new_stude
I cannot help further
My.Resources.SQL_new_stude
I cannot help further
ASKER
i can call the first line in my rich text box, but I am unable to call my second. Any suggestions?
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.Text
'termStart2 = DTPTerm1.Value.ToString("MM-dd-yyyy") & " " & COBTermType.SelectedItem.ToString(0)
'termStart2 = RichTextBox1.Text + 1
'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, CStr(COBTermType.SelectedItem), "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), DTPTerm1.Value, CStr(COBTermType.SelectedItem), "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 can call the first line in my rich text box,"
Can you show just a few important lines of code, so that I can find what you mean by calling the "first line"?
Can you show just a few important lines of code, so that I can find what you mean by calling the "first line"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
is your ds DataSet filled with both rows?
Regards