cmed
asked on
how to allow user to choose date for query to run with datetimepicker
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
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.ToShortDateS
So your first CreateWorksheet call would look like:
You use two different text date formats, "9-2-2014" and "9/2/2014". dtpTest.Value.ToShortDateS
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)ASKER
@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)ASKER
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)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) 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)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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("M
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?
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("M
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)ASKER
i figure it out. I just used
termStart1 = DTPTerm1.Value.ToString("MM-dd-yyyy") & " " & COBTermType1.SelectedItem.ToString(0)
ASKER