Solved

name excel sheet

Posted on 2014-12-15
3
68 Views
Last Modified: 2014-12-16
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

Open in new window

0
Comment
Question by:cmed
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
edwardq earned 500 total points
Comment Utility
You are not setting the Name.

After adding a Worksheet.  
xlSheet.Name = TermDate + TermType
0
 

Author Comment

by:cmed
Comment Utility
this does not work

I need termDate + TermType to work in the worksheet, but nothing is working.
0
 

Author Closing Comment

by:cmed
Comment Utility
@ edwardq

It worked. I didn't add the correctly.  I needed to do a replace. Thank you
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 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

11 Experts available now in Live!

Get 1:1 Help Now