none
import multiple columns in excel to listbox

    Question

  • is it possible to import multiple columns data from excel to listbox using textbox control
    saan
    Saturday, January 21, 2012 12:07 PM

Answers

  • Set your listbox to have the number of columns that you want, and use code like

    Me.ListBox1.List = Worksheets("Sheet1").Range("A1:C10").Value  'For 3 columns of data

    Not sure how you would want to use a textbox control - maybe to set the number of rows?

    Me.ListBox1.List = Worksheets("Sheet1").Range("A1:C" & Me.TextBox1.Value).Value 


    HTH, Bernie
    Saturday, January 21, 2012 3:58 PM

All replies

  • Set your listbox to have the number of columns that you want, and use code like

    Me.ListBox1.List = Worksheets("Sheet1").Range("A1:C10").Value  'For 3 columns of data

    Not sure how you would want to use a textbox control - maybe to set the number of rows?

    Me.ListBox1.List = Worksheets("Sheet1").Range("A1:C" & Me.TextBox1.Value).Value 


    HTH, Bernie
    Saturday, January 21, 2012 3:58 PM
  • I'd like to ask a question if I may because this is relevant to my problem..

    What I understand is by using this code: Me.ListBox1.List = Worksheets("Sheet1").Range("A1:A10").Value

    I can transfer one column into a listbox in vb.

    But then HOW do I tell VB which excel file to use, etc,etc?

    I can, and have found various of links where they do it but whenever I copy their code it just gives me numerous of errors.

    And because I don't understand I don't know how to debug it/fix it...

    What I want is for example 

        Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click

    to code in here to call out to an excel file, go to sheet1 and import column A to listbox1.

    I am using VB 2010 Express so also please, don't post any links to a VB 2006 solution (Or any older one), I tried copying those numerous of times and it doesn't work...

    Thanks in advance

    Tuesday, March 13, 2012 9:52 AM
  • I don't know if the VB Listbox can accept an excel range - did you try something like

    XlApp.Workbooks.Open "C:\Folder\Name.xls"

    Me.ListBox1.List = XlApp.Workbooks("Name.xls").Worksheets("SheetName").Range("A1:A10").Value

    If that doesn't work, I think you would need to dimension an Excel application variable, workbook variable, and maybe even a worksheet variable and range variable. Then in code create an Excel instance, use the Excel open method to set the workbook variable or just open the workbook within Excel, set the worksheet variable to reference the correct worksheet, set the range variable to reference the correct range, and then possibly step through the range and read each cell's value and use the listbox add method to append the value to the listbox rather than use the .List property.


    HTH, Bernie

    Tuesday, March 13, 2012 1:26 PM