lundi 11 mai 2015

fill the combobox with the column names

i have an application on vb.net with file Excel

First, i have a button "browse" which open a workbook Excel.

Second ,i have a button "get the names of the sheets" which give me the name of the sheet in a combobox1 ,then i choose the sheet. Then i have an other button " get the column names " which give me the name of the columns of the table in a combobox2 . When i select the name of the column a new sheet add named the name of the column and which has the data of this column

what i want is :

when i choose another sheet from combobox1, the combobox2 will be refresh with the new columns names of the new sheet

           Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    Chargement_de_données.xlWorkSheet = CType(Chargement_de_données.xlWorkBook.Sheets(Chargement_de_données.ComboBox1.Text), Excel.Worksheet)
    Chargement_de_données.xlWorkSheet.Activate()
    Chargement_de_données.xlApp.Visible = True

    Dim key As String = CStr(DirectCast(ComboBox2.SelectedItem, KeyValuePair(Of Integer, String)).Key)
    Dim value As String = DirectCast(ComboBox2.SelectedItem, KeyValuePair(Of Integer, String)).Value

    DataGridView1.Visible = True

    Dim DoesSheetExists As Boolean = False

    For Each xs In Chargement_de_données.xlApp.Sheets
        If xs.Name = value Then
            DoesSheetExists = True
        End If
    Next
    If DoesSheetExists = True Then
        MsgBox("L'indicateur est déja ouvert ", CType(MessageBoxIcon.Error, MsgBoxStyle))
    Else
        With Chargement_de_données.xlWorkSheet

            Dim lastrow As Integer = Chargement_de_données.xlWorkSheet.Cells.Rows.End(XlDirection.xlDown).Row
            Dim colletter As String = ColumnIndexToColumnLetter(CInt(key))

            exWS2 = DirectCast(Chargement_de_données.xlWorkBook.Sheets.Add, Microsoft.Office.Interop.Excel.Worksheet)
            exWS2.Name = value

            Chargement_de_données.xlWorkSheet.Range("C1:C" & lastrow.ToString).Copy(exWS2.Range("A1"))

            Chargement_de_données.xlWorkSheet.Range(colletter & "1:" & colletter & lastrow.ToString).Copy(exWS2.Range("B1"))

Aucun commentaire:

Enregistrer un commentaire