cmed
asked on
name excel sheet
I am trying to name my sheet by using the TermDate and TermType, but it is just naming the sheet sheet3 and so forth. Any suggestion?
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;"
Dim increment As Integer = 0
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DTPTerm1.Format = DateTimePickerFormat.Custom
DTPTerm1.CustomFormat = " "
'Setting up Progress Bar
If increment > ProgressBar1.Maximum Then
increment = ProgressBar1.Maximum
End If
ProgressBar1.Value = increment
End Sub
Private Sub DTPTerm1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DTPTerm1.ValueChanged
DTPTerm1.CustomFormat = "MM/dd/yyyy"
End Sub
Private Sub btnAddTermStart_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAddTermStart.Click
RichTextBox1.SelectionStart = RichTextBox1.TextLength
RichTextBox1.SelectedText = DTPTerm1.Value.ToString("MM/dd/yyyy") & " " & COBTermType.SelectedItem & vbCrLf
End Sub
Private Sub btnClearTermStarts_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnClearTermStarts.Click
RichTextBox1.Clear()
ProgressBar1.Value = increment
End Sub
Private Sub RichTextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RichTextBox1.TextChanged
End Sub
Private Sub CreateWorksheet(ByRef xlSheet As Excel.Worksheet, ByVal termStartDate As Date, ByVal termTypeFull As String, ByVal tableStyle As String)
xlSheet.Activate()
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))
mycmd.Parameters.AddWithValue("@PROGRAM_START", termStartDate)
mycmd.Parameters.AddWithValue("@TERM_TYPE", termTypeFull)
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
xlSheet.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
xlSheet.Cells(i + 2, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next
'Format Table'
xlSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
xlSheet.ListObjects("Table1").TableStyle = tableStyle
'~~> Create Borders
With xlSheet.UsedRange
.Range("O:O").Cells.NumberFormat = "$ ###,###.00"
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
xlSheet.Columns.AutoFit()
End Using
End Using
End Using
End Using
End Sub
Private Function ValidateCheck() As Boolean
If DTPTerm1.CustomFormat = " " Then
MessageBox.Show("Please select a date.")
Return False
ElseIf COBTermType.SelectedIndex = -1 Then
MessageBox.Show("Please select a term type.")
COBTermType.Focus()
Return False
End If
Return True
End Function
Private Sub btnCreateReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReport.Click
If ValidateCheck() = False Then
Exit Sub
End If
Dim xlApp As New Excel.Application
Dim xlWorkBook = xlApp.Workbooks
Dim xlBook = xlWorkBook.Add
Dim xlSheets = xlBook.Worksheets
Dim misValue As Object = System.Reflection.Missing.Value
Do While xlSheets.Count > 1
Dim delSht = CType(xlSheets.Item(xlSheets.Count - 1), Excel.Worksheet)
delSht.Delete()
Loop
Dim shtCount = 1
For Each line In RichTextBox1.Lines
If line.Length > 0 Then
Dim whiteSpace = line.IndexOf(" "c)
Dim termDate = line.Substring(0, whiteSpace).Replace("/", "-")
Dim termType = line.Substring(whiteSpace + 1, 1)
If shtCount > 1 Then
xlSheets.Add(, xlSheets.Item(shtCount - 1))
End If
CreateWorksheet(xlSheets.Item(shtCount), termDate, termType, "TableStyleMedium1")
shtCount += 1
End If
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@ edwardq
It worked. I didn't add the correctly. I needed to do a replace. Thank you
It worked. I didn't add the correctly. I needed to do a replace. Thank you
ASKER
I need termDate + TermType to work in the worksheet, but nothing is working.