rola theo 0 Newbie Poster

Hello !!

I use with excel

I have a buttom "get the name of the column from worksheet" with a combobox

thi is the code of the button:

 Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim Excols As New Dictionary(Of Integer, String)

        Form2.xlWorkSheet = CType(Form2.xlWorkBook.Sheets(Form2.ComboBox1.Text), Excel.Worksheet)

        With Form2.xlWorkSheet
            Dim LastCol As Integer = Form2.xlWorkSheet.Cells(1, Form2.xlWorkSheet.Columns.Count).End(XlDirection.xlToLeft).Column

            For x As Integer = 2 To LastCol

                Excols.Add(x, Form2.xlWorkSheet.Cells(1, x).value.ToString)
            ComboBox2.DataSource = New BindingSource(Excols, Nothing)
            ComboBox2.ValueMember = "Key"
            ComboBox2.DisplayMember = "Value"

            AddHandler ComboBox2.SelectedIndexChanged, AddressOf ComboBox2_SelectedIndexChanged

        End With

    End Sub

this is the code of the combobox:

Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Form2.xlWorkSheet = CType(Form2.xlWorkBook.Sheets(Form2.ComboBox1.Text), Excel.Worksheet)
        Form2.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
        Dim DoesSheetExists As Boolean = False

        For Each xs In Form2.xlApp.Sheets
            If xs.Name = value Then
                DoesSheetExists = True
            End If

        If DoesSheetExists = True Then
            MsgBox("Sheet already exists", CType(MessageBoxIcon.Error, MsgBoxStyle))

            With Form2.xlWorkSheet

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

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

                Form2.xlWorkSheet.Range("A1:A" & lastrow.ToString).Copy(exWS2.Range("A1"))

                Form2.xlWorkSheet.Range(colletter & "1:" & colletter & lastrow.ToString).Copy(exWS2.Range("B1"))
                exWS2.Range("A1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
                exWS2.Range("B1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
                exWS2.Range("A1").Interior.ColorIndex = 8
                exWS2.Range("B1").Interior.ColorIndex = 8
                exWS2.Range("A2:A" & lastrow.ToString).Interior.ColorIndex = 20
                exWS2.Range("A1:A" & lastrow.ToString).HorizontalAlignment = -4108
                exWS2.Range("B1:B" & lastrow.ToString).HorizontalAlignment = -4108
                exWS2.Range("A1").Font.Name = "Times New Roman"
                exWS2.Range("B1").Font.Name = "Times New Roman"
                exWS2.Range("B1").Font.FontStyle = "Gras"
                exWS2.Range("A1").Font.FontStyle = "Gras"

            End With
        End If
    End Sub
    Public Function ColumnIndexToColumnLetter(ByVal colIndex As Integer) As String
        Dim div As Integer = colIndex
        Dim colLetter As String = String.Empty
        Dim modnum As Integer = 0

        While div > 0
            modnum = (div - 1) Mod 26
            colLetter = Chr(65 + modnum) & colLetter
            div = CInt((div - modnum) \ 26)
        End While

        Return colLetter
    End Function

What i want is to add every time when i select a column , it will be added to a datagridview

this is my column that i want to add to the datagridview
Click Here
help me please