Solved

how to allow user to choose date for query to run with datetimepicker

Posted on 2014-12-02
7
123 Views
Last Modified: 2014-12-03
Currently, my code hard code the date to run a query to generate an output.  I would like to create a way to allow the user to choose a dates to run the query to give the user more options.  
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 CreateWorksheet(ByVal sheetName As String, ByVal colorTab As Excel.XlColorIndex, ByVal parmDate As Date, ByVal parmterm 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", parmDate)
                    mycmd.Parameters.AddWithValue("@TERM_TYPE", parmterm)
                    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 Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

        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

        Dim Test As String
        Dim Date1 As String
        Dim Date2 As String
        Dim Date3 As String
        Dim Date4 As String
        Dim Date5 As String

        Test = "9-2-2014 S"
        Date1 = "9-2-2014 Q"
        Date2 = "10-13-2014 Q"
        Date3 = "10-27-2014 S"
        Date4 = "12-01-2014 Q"
        Date5 = "01-05-2015 S"

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

        xlWorkBook.Sheets.Add(, , 4)


        'Create and run each worksheet'

        xlSheet6 = CType(xlWorkBook.Worksheets("Sheet6"), Excel.Worksheet)
        CreateWorksheet(Test, CType(3, Excel.XlColorIndex), #9/2/2014#, "S", "TableStyleMedium2", xlSheet6)

        ''NAME EXCEL SHEET 2''
        xlSheet5 = CType(xlWorkBook.Worksheets("Sheet5"), Excel.Worksheet)
        CreateWorksheet(Date1, CType(6, Excel.XlColorIndex), #9/2/2014#, "Q", "TableStyleMedium4", xlSheet5)

        ''NAME EXCEL SHEET 3''
        xlSheet4 = CType(xlWorkBook.Worksheets("Sheet4"), Excel.Worksheet)
        CreateWorksheet(Date2, CType(9, Excel.XlColorIndex), #10/13/2014#, "Q", "TableStyleMedium1", xlSheet4)

        ''NAME EXCEL SHEET 4''
        xlSheet1 = CType(xlWorkBook.Worksheets("Sheet1"), Excel.Worksheet)
        CreateWorksheet(Date3, CType(29, Excel.XlColorIndex), #10/27/2014#, "S", "TableStyleMedium3", xlSheet1)

        ''NAME EXCEL SHEET 5''
        xlSheet2 = CType(xlWorkBook.Worksheets("Sheet2"), Excel.Worksheet)
        CreateWorksheet(Date4, CType(12, Excel.XlColorIndex), #12/1/2014#, "Q", "TableStyleMedium7", xlSheet2)

        ''NAME EXCEL SHEET 6''
        xlSheet3 = CType(xlWorkBook.Worksheets("Sheet3"), Excel.Worksheet)
        CreateWorksheet(Date5, CType(22, Excel.XlColorIndex), #1/5/2015#, "S", "TableStyleMedium9", xlSheet3)

        ''NAME EXCEL SHEET 7''
        xlSheet7 = CType(xlWorkBook.Worksheets("Sheet7"), Excel.Worksheet)
        xlSheet7.Activate()
        xlSheet7.Name = "Summary"
        xlSheet7.Tab.ColorIndex = CType(14, Excel.XlColorIndex)

        xlSheet7.Range("A1").Value = "Summary of ISIRs Received for Admitted Students"
        xlSheet7.Range("A1").Font.Size = 14

        With xlSheet7

            .Range("B3").Value = "9/2/2014 S"
            .Range("F3").Value = "9/2/2014 Q"
            .Range("J3").Value = "10/13/2014 Q"
            .Range("B22").Value = "10/27/2014 S"
            .Range("F22").Value = "12/1/2014 Q"
            .Range("J22").Value = "01/05/2015 S"
            .Range("B4, F4, J4, B23, F23, J23").Value = "Status"
            .Range("B5, F5, J5, B24, F24, J24").Value = "Incomplete"
            .Range("B6, F6, J6, B25, F25, J25").Value = "Ready To Package - Special Processing"
            .Range("B7, F7, J7, B26, F26, J26").Value = "Ready To Package"
            .Range("B8, F8, J8, B27, F27, J27").Value = "Awarded"
            .Range("B9, F9, J9, B28, F28, J28").Value = "Declined Aid"
            .Range("B10, F10, J10, B29, F29, J29").Value = "Not Eligible"
            .Range("B11, F11, J11, B30, F30, J30").Value = "Inactive Awarded"
            .Range("B12, F12, J12, B31, F31, J31").Value = "Inactive Not Awarded"
            .Range("B13, F13, J13, B32, F32, J32, B20, F20, J20, B39, F39, J39").Value = "Total"
            .Range("B15, F15, J15, B34, F34, J34").Value = "Active Students Days RP"
            .Range("B16, F16, J16, B35, F35, J35").Value = "0 - 7"
            .Range("B17, F17, J17, B36, F36, J36").Value = "'8 - 14"
            .Range("B18, F18, J18, B37, F37, J37").Value = "15 - 21"
            .Range("B19, F19, J19, B38, F38, J38").Value = "22+"
            .Range("C4, G4, K4, C23, G23, K23, C15, G15, K15, C34, G34, K34").Value = "Students"
            .Range("D4, H4, L4, D23, H23, L23, D15, H15, L15, D34, H34, L34").Value = "Percent"

            .Range("C5").Formula = "=COUNTIFS('" & Test & "'!$D:$D,""IP"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")+(COUNTIFS('" & Test & "'!$D:$D,""ID"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS""))"
            .Range("C6").Formula = "=COUNTIFS('" & Test & "'!$D:$D,""DM"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")+COUNTIFS('" & Test & "'!$D:$D,""AW"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")"
            .Range("C7").Formula = "=COUNTIFS('" & Test & "'!$D:$D,""RP"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")"
            .Range("C8").Formula = "=COUNTIFS('" & Test & "'!$E:$E,""Y"",'" & Test & "'!$H:$H,""<>IS"")"
            .Range("C9").Formula = "=COUNTIFS('" & Test & "'!$D:$D,""DA"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")"
            .Range("C10").Formula = "=COUNTIFS('" & Test & "'!$D:$D,""DS"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")+COUNTIFS('" & Test & "'!$D:$D,""HL"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")+COUNTIFS('" & Test & "'!$D:$D,""NA"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")+COUNTIFS('" & Test & "'!$D:$D,""RD"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")+COUNTIFS('" & Test & "'!$D:$D,""SC"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")+COUNTIFS('" & Test & "'!$D:$D,""AR"",'" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""<>IS"")"
            .Range("C11").Formula = "=COUNTIFS('" & Test & "'!$E:$E,""Y"",'" & Test & "'!$H:$H,""IS"")"
            .Range("C12").Formula = "=COUNTIFS('" & Test & "'!$E:$E,""N"",'" & Test & "'!$H:$H,""IS"")"
            .Range("C13").Formula = "=SUM(C5:C12)"
            .Range("C16").Formula = "=COUNTIFS('" & Test & "'!$F:$F,"">=0"",'" & Test & "'!$F:$F,""<8"",'" & Test & "'!$H:$H,""<>IS"")"
            .Range("C17").Formula = "=COUNTIFS('" & Test & "'!$F:$F,"">=8"",'" & Test & "'!$F:$F,""<15"",'" & Test & "'!$H:$H,""<>IS"")"
            .Range("C18").Formula = "=COUNTIFS('" & Test & "'!$F:$F,"">=15"",'" & Test & "'!$F:$F,""<22"",'" & Test & "'!$H:$H,""<>IS"")"
            .Range("C19").Formula = "=COUNTIFS('" & Test & "'!$F:$F,"">=22"",'" & Test & "'!$H:$H,""<>IS"")"
            .Range("C20").Formula = "=SUM(C16:C19)"

            .Range("G5").Formula = "=COUNTIFS('" & Date1 & "'!$D:$D,""IP"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & Date1 & "'!$D:$D,""ID"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS""))"
            .Range("G6").Formula = "=COUNTIFS('" & Date1 & "'!$D:$D,""DM"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date1 & "'!$D:$D,""AW"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")"
            .Range("G7").Formula = "=COUNTIFS('" & Date1 & "'!$D:$D,""RP"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")"
            .Range("G8").Formula = "=COUNTIFS('" & Date1 & "'!$E:$E,""Y"",'" & Date1 & "'!$H:$H,""<>IS"")"
            .Range("G9").Formula = "=COUNTIFS('" & Date1 & "'!$D:$D,""DA"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")"
            .Range("G10").Formula = "=COUNTIFS('" & Date1 & "'!$D:$D,""DS"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date1 & "'!$D:$D,""HL"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date1 & "'!$D:$D,""NA"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date1 & "'!$D:$D,""RD"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date1 & "'!$D:$D,""SC"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date1 & "'!$D:$D,""AR"",'" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""<>IS"")"
            .Range("G11").Formula = "=COUNTIFS('" & Date1 & "'!$E:$E,""Y"",'" & Date1 & "'!$H:$H,""IS"")"
            .Range("G12").Formula = "=COUNTIFS('" & Date1 & "'!$E:$E,""N"",'" & Date1 & "'!$H:$H,""IS"")"
            .Range("G13").Formula = "=SUM(G5:G12)"
            .Range("G16").Formula = "=COUNTIFS('" & Date1 & "'!$F:$F,"">=0"",'" & Date1 & "'!$F:$F,""<8"",'" & Date1 & "'!$H:$H,""<>IS"")"
            .Range("G17").Formula = "=COUNTIFS('" & Date1 & "'!$F:$F,"">=8"",'" & Date1 & "'!$F:$F,""<15"",'" & Date1 & "'!$H:$H,""<>IS"")"
            .Range("G18").Formula = "=COUNTIFS('" & Date1 & "'!$F:$F,"">=15"",'" & Date1 & "'!$F:$F,""<22"",'" & Date1 & "'!$H:$H,""<>IS"")"
            .Range("G19").Formula = "=COUNTIFS('" & Date1 & "'!$F:$F,"">=22"",'" & Date1 & "'!$H:$H,""<>IS"")"
            .Range("G20").Formula = "=SUM(G16:G19)"

            .Range("K5").Formula = "=COUNTIFS('" & Date2 & "'!$D:$D,""IP"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & Date2 & "'!$D:$D,""ID"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS""))"
            .Range("K6").Formula = "=COUNTIFS('" & Date2 & "'!$D:$D,""DM"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date2 & "'!$D:$D,""AW"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")"
            .Range("K7").Formula = "=COUNTIFS('" & Date2 & "'!$D:$D,""RP"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")"
            .Range("K8").Formula = "=COUNTIFS('" & Date2 & "'!$E:$E,""Y"",'" & Date2 & "'!$H:$H,""<>IS"")"
            .Range("K9").Formula = "=COUNTIFS('" & Date2 & "'!$D:$D,""DA"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")"
            .Range("K10").Formula = "=COUNTIFS('" & Date2 & "'!$D:$D,""DS"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date2 & "'!$D:$D,""HL"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date2 & "'!$D:$D,""NA"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date2 & "'!$D:$D,""RD"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date2 & "'!$D:$D,""SC"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date2 & "'!$D:$D,""AR"",'" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""<>IS"")"
            .Range("K11").Formula = "=COUNTIFS('" & Date2 & "'!$E:$E,""Y"",'" & Date2 & "'!$H:$H,""IS"")"
            .Range("K12").Formula = "=COUNTIFS('" & Date2 & "'!$E:$E,""N"",'" & Date2 & "'!$H:$H,""IS"")"
            .Range("K13").Formula = "=SUM(K5:K12)"
            .Range("K16").Formula = "=COUNTIFS('" & Date2 & "'!$F:$F,"">=0"",'" & Date2 & "'!$F:$F,""<8"",'" & Date2 & "'!$H:$H,""<>IS"")"
            .Range("K17").Formula = "=COUNTIFS('" & Date2 & "'!$F:$F,"">=8"",'" & Date2 & "'!$F:$F,""<15"",'" & Date2 & "'!$H:$H,""<>IS"")"
            .Range("K18").Formula = "=COUNTIFS('" & Date2 & "'!$F:$F,"">=15"",'" & Date2 & "'!$F:$F,""<22"",'" & Date2 & "'!$H:$H,""<>IS"")"
            .Range("K19").Formula = "=COUNTIFS('" & Date2 & "'!$F:$F,"">=22"",'" & Date2 & "'!$H:$H,""<>IS"")"
            .Range("K20").Formula = "=SUM(K16:K19)"

            .Range("C24").Formula = "=COUNTIFS('" & Date3 & "'!$D:$D,""IP"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & Date3 & "'!$D:$D,""ID"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS""))"
            .Range("C25").Formula = "=COUNTIFS('" & Date3 & "'!$D:$D,""DM"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date3 & "'!$D:$D,""AW"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")"
            .Range("C26").Formula = "=COUNTIFS('" & Date3 & "'!$D:$D,""RP"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")"
            .Range("C27").Formula = "=COUNTIFS('" & Date3 & "'!$E:$E,""Y"",'" & Date3 & "'!$H:$H,""<>IS"")"
            .Range("C28").Formula = "=COUNTIFS('" & Date3 & "'!$D:$D,""DA"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")"
            .Range("C29").Formula = "=COUNTIFS('" & Date3 & "'!$D:$D,""DS"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date3 & "'!$D:$D,""HL"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date3 & "'!$D:$D,""NA"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date3 & "'!$D:$D,""RD"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date3 & "'!$D:$D,""SC"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date3 & "'!$D:$D,""AR"",'" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""<>IS"")"
            .Range("C30").Formula = "=COUNTIFS('" & Date3 & "'!$E:$E,""Y"",'" & Date3 & "'!$H:$H,""IS"")"
            .Range("C31").Formula = "=COUNTIFS('" & Date3 & "'!$E:$E,""N"",'" & Date3 & "'!$H:$H,""IS"")"
            .Range("C32").Formula = "=SUM(C24:C31)"
            .Range("C35").Formula = "=COUNTIFS('" & Date3 & "'!$F:$F,"">=0"",'" & Date3 & "'!$F:$F,""<8"",'" & Date3 & "'!$H:$H,""<>IS"")"
            .Range("C36").Formula = "=COUNTIFS('" & Date3 & "'!$F:$F,"">=8"",'" & Date3 & "'!$F:$F,""<15"",'" & Date3 & "'!$H:$H,""<>IS"")"
            .Range("C37").Formula = "=COUNTIFS('" & Date3 & "'!$F:$F,"">=15"",'" & Date3 & "'!$F:$F,""<22"",'" & Date3 & "'!$H:$H,""<>IS"")"
            .Range("C38").Formula = "=COUNTIFS('" & Date3 & "'!$F:$F,"">=22"",'" & Date3 & "'!$H:$H,""<>IS"")"
            .Range("C39").Formula = "=SUM(C35:C38)"

            .Range("G24").Formula = "=COUNTIFS('" & Date4 & "'!$D:$D,""IP"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & Date4 & "'!$D:$D,""ID"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS""))"
            .Range("G25").Formula = "=COUNTIFS('" & Date4 & "'!$D:$D,""DM"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date4 & "'!$D:$D,""AW"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")"
            .Range("G26").Formula = "=COUNTIFS('" & Date4 & "'!$D:$D,""RP"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")"
            .Range("G27").Formula = "=COUNTIFS('" & Date4 & "'!$E:$E,""Y"",'" & Date4 & "'!$H:$H,""<>IS"")"
            .Range("G28").Formula = "=COUNTIFS('" & Date4 & "'!$D:$D,""DA"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")"
            .Range("G29").Formula = "=COUNTIFS('" & Date4 & "'!$D:$D,""DS"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date4 & "'!$D:$D,""HL"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date4 & "'!$D:$D,""NA"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date4 & "'!$D:$D,""RD"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date4 & "'!$D:$D,""SC"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date4 & "'!$D:$D,""AR"",'" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""<>IS"")"
            .Range("G30").Formula = "=COUNTIFS('" & Date4 & "'!$E:$E,""Y"",'" & Date4 & "'!$H:$H,""IS"")"
            .Range("G31").Formula = "=COUNTIFS('" & Date4 & "'!$E:$E,""N"",'" & Date4 & "'!$H:$H,""IS"")"
            .Range("G32").Formula = "=SUM(G24:G31)"
            .Range("G35").Formula = "=COUNTIFS('" & Date4 & "'!$F:$F,"">=0"",'" & Date4 & "'!$F:$F,""<8"",'" & Date4 & "'!$H:$H,""<>IS"")"
            .Range("G36").Formula = "=COUNTIFS('" & Date4 & "'!$F:$F,"">=8"",'" & Date4 & "'!$F:$F,""<15"",'" & Date4 & "'!$H:$H,""<>IS"")"
            .Range("G37").Formula = "=COUNTIFS('" & Date4 & "'!$F:$F,"">=15"",'" & Date4 & "'!$F:$F,""<22"",'" & Date4 & "'!$H:$H,""<>IS"")"
            .Range("G38").Formula = "=COUNTIFS('" & Date4 & "'!$F:$F,"">=22"",'" & Date4 & "'!$H:$H,""<>IS"")"
            .Range("G39").Formula = "=SUM(G35:G38)"

            .Range("K24").Formula = "=COUNTIFS('" & Date5 & "'!$D:$D,""IP"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & Date5 & "'!$D:$D,""ID"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS""))"
            .Range("K25").Formula = "=COUNTIFS('" & Date5 & "'!$D:$D,""DM"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date5 & "'!$D:$D,""AW"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")"
            .Range("K26").Formula = "=COUNTIFS('" & Date5 & "'!$D:$D,""RP"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")"
            .Range("K27").Formula = "=COUNTIFS('" & Date5 & "'!$E:$E,""Y"",'" & Date5 & "'!$H:$H,""<>IS"")"
            .Range("K28").Formula = "=COUNTIFS('" & Date5 & "'!$D:$D,""DA"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")"
            .Range("K29").Formula = "=COUNTIFS('" & Date5 & "'!$D:$D,""DS"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date5 & "'!$D:$D,""HL"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date5 & "'!$D:$D,""NA"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date5 & "'!$D:$D,""RD"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date5 & "'!$D:$D,""SC"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & Date5 & "'!$D:$D,""AR"",'" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""<>IS"")"
            .Range("K30").Formula = "=COUNTIFS('" & Date5 & "'!$E:$E,""Y"",'" & Date5 & "'!$H:$H,""IS"")"
            .Range("K31").Formula = "=COUNTIFS('" & Date5 & "'!$E:$E,""N"",'" & Date5 & "'!$H:$H,""IS"")"
            .Range("K32").Formula = "=SUM(K24:K31)"
            .Range("K35").Formula = "=COUNTIFS('" & Date5 & "'!$F:$F,"">=0"",'" & Date5 & "'!$F:$F,""<8"",'" & Date5 & "'!$H:$H,""<>IS"")"
            .Range("K36").Formula = "=COUNTIFS('" & Date5 & "'!$F:$F,"">=8"",'" & Date5 & "'!$F:$F,""<15"",'" & Date5 & "'!$H:$H,""<>IS"")"
            .Range("K37").Formula = "=COUNTIFS('" & Date5 & "'!$F:$F,"">=15"",'" & Date5 & "'!$F:$F,""<22"",'" & Date5 & "'!$H:$H,""<>IS"")"
            .Range("K38").Formula = "=COUNTIFS('" & Date5 & "'!$F:$F,"">=22"",'" & Date5 & "'!$H:$H,""<>IS"")"
            .Range("K39").Formula = "=SUM(K35:K38)"

            .Range("D5").Formula = "=C5/C$13"
            .Range("D6").Formula = "=C6/C$13"
            .Range("D7").Formula = "=C7/C$13"
            .Range("D8").Formula = "=C8/C$13"
            .Range("D9").Formula = "=C9/C$13"
            .Range("D10").Formula = "=C10/C$13"
            .Range("D11").Formula = "=C11/C$13"
            .Range("D12").Formula = "=C12/C$13"
            .Range("D16").Formula = "=C16/C$20"
            .Range("D17").Formula = "=C17/C$20"
            .Range("D18").Formula = "=C18/C$20"
            .Range("D19").Formula = "=C19/C$20"

            .Range("H5").Formula = "=G5/G$13"
            .Range("H6").Formula = "=G6/G$13"
            .Range("H7").Formula = "=G7/G$13"
            .Range("H8").Formula = "=G8/G$13"
            .Range("H9").Formula = "=G9/G$13"
            .Range("H10").Formula = "=G10/G$13"
            .Range("H11").Formula = "=G11/G$13"
            .Range("H12").Formula = "=G12/G$13"
            .Range("H16").Formula = "=G16/G$20"
            .Range("H17").Formula = "=G17/G$20"
            .Range("H18").Formula = "=G18/G$20"
            .Range("H19").Formula = "=G19/G$20"

            .Range("L5").Formula = "=K5/K$13"
            .Range("L6").Formula = "=K6/K$13"
            .Range("L7").Formula = "=K7/K$13"
            .Range("L8").Formula = "=K8/K$13"
            .Range("L9").Formula = "=K9/K$13"
            .Range("L10").Formula = "=K10/K$13"
            .Range("L11").Formula = "=K11/K$13"
            .Range("L12").Formula = "=K12/K$13"
            .Range("L16").Formula = "=K16/K$20"
            .Range("L17").Formula = "=K17/K$20"
            .Range("L18").Formula = "=K18/K$20"
            .Range("L19").Formula = "=K19/K$20"

            .Range("D24").Formula = "=C24/C$32"
            .Range("D25").Formula = "=C25/C$32"
            .Range("D26").Formula = "=C26/C$32"
            .Range("D27").Formula = "=C27/C$32"
            .Range("D28").Formula = "=C28/C$32"
            .Range("D29").Formula = "=C29/C$32"
            .Range("D30").Formula = "=C30/C$32"
            .Range("D31").Formula = "=C31/C$32"
            .Range("D35").Formula = "=C35/C$39"
            .Range("D36").Formula = "=C36/C$39"
            .Range("D37").Formula = "=C37/C$39"
            .Range("D38").Formula = "=C38/C$39"

            .Range("H24").Formula = "=G24/G$32"
            .Range("H25").Formula = "=G25/G$32"
            .Range("H26").Formula = "=G26/G$32"
            .Range("H27").Formula = "=G27/G$32"
            .Range("H28").Formula = "=G28/G$32"
            .Range("H29").Formula = "=G29/G$32"
            .Range("H30").Formula = "=G30/G$32"
            .Range("H31").Formula = "=G31/G$32"
            .Range("H35").Formula = "=G35/G$39"
            .Range("H36").Formula = "=G36/G$39"
            .Range("H37").Formula = "=G37/G$39"
            .Range("H38").Formula = "=G38/G$39"

            .Range("L24").Formula = "=K24/K$32"
            .Range("L25").Formula = "=K25/K$32"
            .Range("L26").Formula = "=K26/K$32"
            .Range("L27").Formula = "=K27/K$32"
            .Range("L28").Formula = "=K28/K$32"
            .Range("L29").Formula = "=K29/K$32"
            .Range("L30").Formula = "=K30/K$32"
            .Range("L31").Formula = "=K31/K$32"
            .Range("L35").Formula = "=K35/K$39"
            .Range("L36").Formula = "=K36/K$39"
            .Range("L37").Formula = "=K37/K$39"
            .Range("L38").Formula = "=K38/K$39"

            .Range("D:D, H:H, L:L").NumberFormat = "0%"

            .Range("B5:D5, F5:H5, J5:L5, B24:D24, F24:H24, J24:L24").Interior.Color = RGB(255, 255, 204)
            .Range("B6:D6, F6:H6, J6:L6, B25:D25, F25:H25, J25:L25").Interior.Color = RGB(204, 255, 102)
            .Range("B7:D7, F7:H7, J7:L7, B26:D26, F26:H26, J26:L26").Interior.Color = RGB(153, 255, 153)
            .Range("B8:D8, F8:H8, J8:L8, B27:D27, F27:H27, J27:L27").Interior.Color = RGB(51, 204, 51)
            .Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Interior.Color = RGB(0, 51, 0)
            .Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Font.Color = RGB(255, 255, 255)
            .Range("B11:D12, F11:H12, J11:L12, B30:D31, F30:H31, J30:L31").Interior.Color = RGB(191, 191, 191)
            .Range("B16:D19, F16:H19, J16:L19, B35:D38, F35:H38, J35:L38").Interior.Color = RGB(153, 255, 153)

            .Range("A1:L1, B3:D3, F3:H3, J3:L3, B22:D22, F22:H22, J22:L22").Merge()
            .Range("A1:L1, B3:D3, F3:H3, J3:L3, B22:D22, F22:H22, J22:L22, B4, F4, J4, B23, F23, J23, B15, F15, J15, B34, F34, J34").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            .Range("A1, B3, F3, J3, B22, F22, J22, B4, F4, J4, B23, F23, J23, B13, F13, J13, B32, F32, J32, B20, F20, J20, B39, F39, J39, B15, F15, J15, B34, F34, J34, C4, G4, K4, C23, G23, K23, C15, G15, K15, C34, G34, K34, C20, G20, K20, C39, G39, K39, D4, H4, L4").Font.Bold = True
            .Range("D23, H23, L23, D15, H15, L15, D34, H34, L34").Font.Bold = True

            With xlSheet7.Range("B3:D20, F3:H20, J3:L20, B22:D39, F22:H39, J22:L39")

                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                    .LineStyle = Excel.XlLineStyle.xlDouble
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlMedium
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlMedium
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlMedium
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlMedium
                End With

                With xlSheet7.Range("B3:D3, F3:H3, J3:L3, B22:D22, F22:H22, J22:L22")
                    With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = Excel.XlBorderWeight.xlThin
                    End With
                    With xlSheet7.Range("B14:D14, F14:H14, J14:L14, B33:D33, F33:H33, J33:L33")
                        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
                    End With
                End With
            End With
        End With

        xlSheet7.Columns.AutoFit()
        xlApp.Visible = True

        releaseObject(xlSheet7)
        releaseObject(xlSheet6)
        releaseObject(xlSheet5)
        releaseObject(xlSheet4)
        releaseObject(xlSheet3)
        releaseObject(xlSheet2)
        releaseObject(xlSheet1)
        releaseObject(xlWorkBook)
        releaseObject(xlApp)

        MessageBox.Show("Report is complete")

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

Open in new window

0
Comment
Question by:cmed
  • 5
  • 2
7 Comments
 

Author Comment

by:cmed
ID: 40476121
and allow a user to chose a specific term type.
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 40477809
Put a DateTimePicker control on your form. The result is in the .Value field, so when you're calling CreateWorksheet, you can replace a literal date like "#9/2/2014#" with "dtpTest.Value".

You use two different text date formats, "9-2-2014" and "9/2/2014". dtpTest.Value.ToShortDateString will return "9/2/2014" if you're using standard US English localization for the date format. You can get the other format with Format(dtpTest.Value, "M-d-yyyy") (case is significant).

So your first CreateWorksheet call would look like:
        CreateWorksheet(Format(dtpTest.Value, "M-d-yyyy") & "S", CType(3, Excel.XlColorIndex), dtpTest.Value, "S", "TableStyleMedium2", xlSheet6)

Open in new window

0
 

Author Comment

by:cmed
ID: 40478293
@ElrondCT Thanks.  I have been working on this for code, so I came up with the same approach.  The different format (9-2-2014) is just how I named each Excel Sheet, but I am using the short date format for the datetimepicker.  I am now using a combo box to chose between different terms.  The problem I am running into now is how to use a variable from the database to choose the term type.  For example, if the drop down has Quarter, but to use the variable "Q" for the parameters.  How would I code this?  This is where I am running into problems.
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 DateTimePicker_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker.ValueChanged
        Dim Dtp As String
        Dtp = CStr(DateTimePicker.Value)
    End Sub


    Private Sub COBTermType1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles COBTermType1.SelectedIndexChanged
        'Dim TermType As String
        'If COBTermType1.Text = "Quarter" Then
        '    TermType = "Q"
        'ElseIf COBTermType1.Text = "Semester" Then
        '    TermType = "S"
        'End If

        'Dim a = Me.COBTermType1
        'Dim b = a.Items
        'For Each i In b
        '    Dim c = i.ToString
        'Next

    End Sub



    Private Sub CreateWorksheet(ByVal sheetName As String, ByVal colorTab As Excel.XlColorIndex, ByVal DateTimePicker As Date, ByVal COBTermType1 As String, ByVal tableStyle As String, ByRef xlSht As Excel.Worksheet)
        xlSht.Activate()
        xlSht.Name = sheetName
        xlSht.Tab.ColorIndex = colorTab

        Dim dtp As String
        dtp = CStr(DateTimePicker.Date)

        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", DateTimePicker)
                    'AddWithValue("P2", Me.DateTimePicker1.Value)
                    mycmd.Parameters.AddWithValue("@TERM_TYPE", COBTermType1)
                    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 Sub btnCreateReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReport.Click

        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

        'For Excel Functions'

        Dim Test As String
        Dim Date1 As String
        Dim Date2 As String
        Dim Date3 As String
        Dim Date4 As String
        Dim Date5 As String

        Test = "9-2-2014 S"
        Date1 = "9-2-2014 Q"
        Date2 = "10-13-2014 Q"
        Date3 = "10-27-2014 S"
        Date4 = "12-01-2014 Q"
        Date5 = "01-05-2015 S"

        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(Test, CType(3, Excel.XlColorIndex), DateTimePicker.Value, "TermType", "TableStyleMedium2", xlSheet6)

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:cmed
ID: 40478298
as I am running the code, I only getting the columns name returned on the spreadsheet.  I know it is something wrong with my parameters and worksheet.  

 mycmd.CommandTimeout = 0
                    mycmd.Parameters.AddWithValue("@PROGRAM_START", DateTimePicker)
                    'AddWithValue("P2", Me.DateTimePicker1.Value)
                    mycmd.Parameters.AddWithValue("@TERM_TYPE", COBTermType1)
                    mysda.SelectCommand = mycmd

xlSheet6 = CType(xlWorkBook.Worksheets("Sheet6"), Excel.Worksheet)
        CreateWorksheet(Test, CType(3, Excel.XlColorIndex), DateTimePicker.Value, "TermType", "TableStyleMedium2", xlSheet6)

Open in new window

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 DateTimePicker_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker.ValueChanged
        Dim Dtp As String
        Dtp = CStr(DateTimePicker.Value)
    End Sub


    Private Sub COBTermType1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles COBTermType1.SelectedIndexChanged
        'Dim TermType As String
        'If COBTermType1.Text = "Quarter" Then
        '    TermType = "Q"
        'ElseIf COBTermType1.Text = "Semester" Then
        '    TermType = "S"
        'End If

        'Dim a = Me.COBTermType1
        'Dim b = a.Items
        'For Each i In b
        '    Dim c = i.ToString
        'Next

    End Sub



    Private Sub CreateWorksheet(ByVal sheetName As String, ByVal colorTab As Excel.XlColorIndex, ByVal DateTimePicker As Date, ByVal COBTermType1 As String, ByVal tableStyle As String, ByRef xlSht As Excel.Worksheet)
        xlSht.Activate()
        xlSht.Name = sheetName
        xlSht.Tab.ColorIndex = colorTab

        Dim dtp As String
        dtp = CStr(DateTimePicker.Date)

        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", DateTimePicker)
                    'AddWithValue("P2", Me.DateTimePicker1.Value)
                    mycmd.Parameters.AddWithValue("@TERM_TYPE", COBTermType1)
                    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 Sub btnCreateReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReport.Click

        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

        'For Excel Functions'

        Dim Test As String
        Dim Date1 As String
        Dim Date2 As String
        Dim Date3 As String
        Dim Date4 As String
        Dim Date5 As String

        Test = "9-2-2014 S"
        Date1 = "9-2-2014 Q"
        Date2 = "10-13-2014 Q"
        Date3 = "10-27-2014 S"
        Date4 = "12-01-2014 Q"
        Date5 = "01-05-2015 S"

        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(Test, CType(3, Excel.XlColorIndex), DateTimePicker.Value, "TermType", "TableStyleMedium2", xlSheet6)

Open in new window

 mycmd.CommandTimeout = 0
                    mycmd.Parameters.AddWithValue("@PROGRAM_START", DateTimePicker)
                    'AddWithValue("P2", Me.DateTimePicker1.Value)
                    mycmd.Parameters.AddWithValue("@TERM_TYPE", COBTermType1)
                    mysda.SelectCommand = mycmd

xlSheet6 = CType(xlWorkBook.Worksheets("Sheet6"), Excel.Worksheet)
        CreateWorksheet(Test, CType(3, Excel.XlColorIndex), DateTimePicker.Value, "TermType", "TableStyleMedium2", xlSheet6)

Open in new window

0
 
LVL 20

Accepted Solution

by:
ElrondCT earned 500 total points
ID: 40478595
You don't want to pass the entire control as a parameter; you want to pass its value. So you'd want
                    mycmd.Parameters.AddWithValue("@PROGRAM_START", DateTimePicker.Value)
                    mycmd.Parameters.AddWithValue("@TERM_TYPE", COBTermType1.Text)

Open in new window

Though if you only want the first character of the drop-down box with the term length, you'd change COBTermType1.Text to COBTermType1.Text.Substring(0, 1)
0
 

Author Comment

by:cmed
ID: 40478739
@ElrondCT

Thanks.  I've just finish the parameters before you sent this, but I like the substring, so I used that.  Now I am trying to name my Excel sheet using the datapickertime and combo box.  When I used  

termStart1 = DTPTerm1.Value.ToString("MM-dd-yyyy") & CStr(COBTermType1.SelectedItem)

it created 01-05-2015Quarter.  I want the sheet name to be 01-05-2015 Q.  It didn't like the substring,so I am working on this now. 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 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 Sub btnCreateReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReport.Click

        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

        'For Excel Functions'

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


        termStart1 = DTPTerm1.Value.ToString("MM-dd-yyyy") & CStr(COBTermType1.SelectedItem)
        
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(COBTermType1.SelectedItem), "TableStyleMedium2", xlSheet6)

Open in new window

Calvin-code-error.PNG
0
 

Author Comment

by:cmed
ID: 40478894
i figure it out.  I just used
 termStart1 = DTPTerm1.Value.ToString("MM-dd-yyyy") & " " & COBTermType1.SelectedItem.ToString(0)

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

8 Experts available now in Live!

Get 1:1 Help Now