Solved

using richboxtext how can i add the second date to the sheet

Posted on 2014-12-09
9
164 Views
Last Modified: 2015-01-13
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?











Calvin-code-error.PNG
0
Comment
Question by:cmed
9 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40490763
Hi,

is your ds DataSet filled with both rows?

Regards
0
 

Author Comment

by:cmed
ID: 40491098
@ Rgonzo1971

for the richtextbox no.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40491145
without knowing where the data is and how it is called

My.Resources.SQL_new_student_metric

I cannot help further
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:cmed
ID: 40491824
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

Open in new window

naming of sheetdesign
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40497322
"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"?
0
 

Accepted Solution

by:
cmed earned 0 total points
ID: 40500981
i figure it out thank you
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40546254
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now