locked
how to get the column names of the table excel in vb.net RRS feed

  • Question

  • Hi !
    I use vb.net with excel 
    First, i have a button "browse" which open a workbook.
    Second ,i  have a button "get the names of the sheets" which give me the name of the sheet in a combobox ,then i choose the sheet.
    Then i make an other button " get the column names " which give me the name of the columns of the table.
    what i want is how to make the third button which is "get the column names" in a combobox.

    this is the code of the first button :

      Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
    
     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
    
    
    
            OpenFileDialog1.ShowDialog()
    
            TextBox1.Text = OpenFileDialog1.FileName
    
            ch = OpenFileDialog1.FileName
    
            Process.Start("Excel", TextBox1.Text)
            ListBox1.Items.Clear()
            ComboBox1.Items.Clear()
        End Sub

    this is the code of the second button :

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
            Dim x1 As String
    
            xlApp.Visible = False
            xlWorkBook = xlApp.Workbooks.Open(ch)
            xlWorkSheet = xlWorkBook.Sheets(1)
    
            ' x1 = xlWorkSheet.Name
            If ComboBox1.Items.Count = Nothing Then
                With xlWorkSheet
    
    
                    For Each Me.xlWorkSheet In xlWorkBook.Worksheets
                        x1 = xlWorkSheet.Name
    
                        ComboBox1.Items.Add(x1)
    
                    Next xlWorkSheet
    
                End With
            Else
                MsgBox("Déja Pleine!", MessageBoxIcon.Error)
    
    
            End If
        End Sub

    please help me 

    Wednesday, March 18, 2015 10:21 AM

Answers

  • Besides the far superior method of Kevin, the quick and dirty way would be

     xlWorkBook = xlApp.Workbooks.Open(ch)
     xlWorkSheet = xlWorkBook.Sheets(1)
    Dim LastCol As Integer = xlWorkSheet.Cells(1, exWS.Columns.Count).End(XlDirection.xlToLeft).Column For x As Integer = 1 To LastCol combobox1.items.add(xlWorkSheet.Cells(1, x).value) Next



    • Edited by Rbie Wednesday, March 18, 2015 11:42 AM
    • Marked as answer by tamaratheo Wednesday, March 18, 2015 11:49 AM
    Wednesday, March 18, 2015 11:41 AM

All replies

  • With tables I assume you mean query tabels? What if multiple tables are present inside the sheet, you want all the columns from all tables listed in the combobox ?

    Wednesday, March 18, 2015 10:29 AM
  • i have one table and begin always from the first row of the sheet but i have many sheets which i want to gat the name of the columns when i choose a sheet 
    Wednesday, March 18, 2015 10:44 AM
  • Hello,

    I have a short article on this found here.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by Rbie Wednesday, March 18, 2015 11:35 AM
    Wednesday, March 18, 2015 10:58 AM
  • i just want the code of how to get the name of the columns in a combobox 

    help me

    Wednesday, March 18, 2015 11:39 AM
  • Besides the far superior method of Kevin, the quick and dirty way would be

     xlWorkBook = xlApp.Workbooks.Open(ch)
     xlWorkSheet = xlWorkBook.Sheets(1)
    Dim LastCol As Integer = xlWorkSheet.Cells(1, exWS.Columns.Count).End(XlDirection.xlToLeft).Column For x As Integer = 1 To LastCol combobox1.items.add(xlWorkSheet.Cells(1, x).value) Next



    • Edited by Rbie Wednesday, March 18, 2015 11:42 AM
    • Marked as answer by tamaratheo Wednesday, March 18, 2015 11:49 AM
    Wednesday, March 18, 2015 11:41 AM
  • it's work very well

    thannnks a lot Rbie

    i love you so much 

     
    Wednesday, March 18, 2015 11:47 AM
  • hi !!

    now ,

    when i select the name of the column :

    -this column will be copie in a new sheet of the same workbook (a new sheet add and the column of the table will be there )

    help me pleasee

    Wednesday, March 18, 2015 12:23 PM
  • Could you make a new question for that, that will make it easier for other people with simular question to search for it
    Wednesday, March 18, 2015 12:27 PM
  • ok 
    Wednesday, March 18, 2015 12:28 PM
  • Hi tamaratheo,

    if you consider this solved and have an additional question, please open a new thread. There you can add a link to this one. Thank you.


    Armin

    Wednesday, March 18, 2015 12:29 PM