troubleshooting Question

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

Avatar of cmed
cmed asked on
Visual Basic.NET
7 Comments1 Solution289 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
ElrondCT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros