none
how to extract column names and table names to listbox or combobox in vba? RRS feed

  • Question

  • Hello developers,

    I'm working on a database application where you need to select a table names and column names from listbox or combobox in vba.

    The question is how can i get the table names and column names from an excel or database file and put it in listbox or combobox?

    Thanks in advance

    Saturday, August 23, 2014 2:19 PM

Answers

  • Is it the listcolumn object?

    Nearly. Inside an Excel workbook we have sheets, every sheet has columns and rows and each cell can contain anything.

    Coming up with Excel 2003 cells could be formatted as Table, means a sheet can contain one (or more) tables. This tables can be found inside the ListObjects collection which is a part of the WorkSheet object.

    Every ListObject has also columns and rows, but to distinguish them from the sheet rows/columns they are called ListColumns and ListRows.

    The ListColumn object has a Name property, add this to your list box.

    Andreas.

    Sub Test()
      Dim Ws As Worksheet
      Dim Lo As ListObject
      Dim Lc As ListColumn
      
      Set Ws = ActiveSheet
      Set Lo = Ws.ListObjects(1)
      For Each Lc In Lo.ListColumns
        Debug.Print Lc.Name
      Next
    End Sub
    

    Sunday, August 24, 2014 11:54 AM

All replies

  • What version of Excel?

    Have you looked at the developer reference for the object model?

    http://msdn.microsoft.com/en-us/library/bb149081(v=office.12).aspx


    Kind Regards, Rich ... http://greatcirclelearning.com

    Sunday, August 24, 2014 2:35 AM
  • I'm currently using 2013 and i also want the app to be compatible with other versions if possible

    I have looked into the link you gave me but still have no idea what to do.

    I'm sorry i don't know anything about sql stuff. I only know vba stuff.

    Sunday, August 24, 2014 3:25 AM
  • Is it the listcolumn object?
    Sunday, August 24, 2014 3:28 AM
  • I'm currently using 2013 and i also want the app to be compatible with other versions if possible

    I have looked into the link you gave me but still have no idea what to do.

    I'm sorry i don't know anything about sql stuff. I only know vba stuff.

    I don't know SQL either. I thought you were looking at Excel.

    Sorry...


    Kind Regards, Rich ... http://greatcirclelearning.com

    Sunday, August 24, 2014 10:53 AM
  • It's okay though i need both Excel and Sql codes.

    Sunday, August 24, 2014 10:57 AM
  • Is it the listcolumn object?

    Nearly. Inside an Excel workbook we have sheets, every sheet has columns and rows and each cell can contain anything.

    Coming up with Excel 2003 cells could be formatted as Table, means a sheet can contain one (or more) tables. This tables can be found inside the ListObjects collection which is a part of the WorkSheet object.

    Every ListObject has also columns and rows, but to distinguish them from the sheet rows/columns they are called ListColumns and ListRows.

    The ListColumn object has a Name property, add this to your list box.

    Andreas.

    Sub Test()
      Dim Ws As Worksheet
      Dim Lo As ListObject
      Dim Lc As ListColumn
      
      Set Ws = ActiveSheet
      Set Lo = Ws.ListObjects(1)
      For Each Lc In Lo.ListColumns
        Debug.Print Lc.Name
      Next
    End Sub
    

    Sunday, August 24, 2014 11:54 AM
  • Thanks for replying but i seem to be having a problem with adding Reference object in vba

    i already added "Microsoft.Office.Interop.Excel" reference and even the "Imports Microsoft.Office.Interop.Excel" in the first line but it says

    'Worksheet' is ambiguous in the namespace 'Microsoft.Office.Interop.Excel'.

    the same goes to "ListObject" and "ListColumn"

    do i have to add more reference in vba

    Sunday, August 24, 2014 12:58 PM
  • nevermind i found a way thanks anyway. i'll try your code
    Sunday, August 24, 2014 1:24 PM