Adding items to listbox from excel

Question

I have a column in an Excel file that contain 100 rows. I'm trying to import this column into a ListBox using a button.

The problem is that only 48 rows are importing from the Excel column.

Why aren't all the rows inside the column imported?

Here is my code (vb.net form):

Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
    Dim oExcel As Object = CreateObject("Excel.Application")
    Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\User\Desktop\1.xlsx")
    Dim oSheet As Object = oBook.Worksheets(1)
    Dim i As Integer
    Dim cell As String
    For i = 0 To AscW(ListBox1.Items.Count.ToString()(i = i + 1)) - 1
        'set cell name, e.g. A1, A2, etc
        cell = "B" & Convert.ToString(i + 1)
        ' get cell data from Excel
        cell = oSheet.Range(cell).Value
        If cell = "" Then
            Exit For
        Else
            ListBox5.Items.Add(cell)
        End If
    Next
    oExcel.Quit()
End Sub

The issue is most probably coming from AscW(ListBox1.Items.Count.ToString()(i = i + 1)) - 1 or the Exit For, why are you taking the Asc value of your listbox's count? — R3uK
– R3uK, Commented Dec 16, 2015 at 9:58
there is no empty cell, I'm using Asc to take all the items not a specific size — bod
– bod, Commented Dec 16, 2015 at 10:00

R3uK · Accepted Answer · 2015-12-16 10:21:25Z

2

I changed your AscW(... to oSheet.Range("B" & oSheet.Rows.Count).End(xlUp).Row,
so that you'll add all the column B to your ListBox

(still, be careful because of your Exit For you canNOT have an empty cell in the middle!)

Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
Dim oExcel As Object = CreateObject("Excel.Application")
    Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\User\Desktop\1.xlsx")
    Dim oSheet As Object = oBook.Worksheets(1)
    Dim i As Integer
    Dim cell As String
For i = 0 To oSheet.Range("B" & oSheet.Rows.Count).End(xlUp).Row
    'set cell name, e.g. A1, A2, etc
    cell = "B" & Convert.ToString(i + 1)
    ' get cell data from Excel
    cell = oSheet.Range(cell).Value
    If cell = "" Then
        Exit For
    Else
        ListBox5.Items.Add (cell)
    End If
Next i
oExcel.Quit()
End Sub

answered Dec 16, 2015 at 10:21

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

bod Over a year ago

Very much appreciate your answer . But a small error occur ( Error 'xlup' is not declared. It may be inaccessible due to its protection level)

R3uK Over a year ago

Ok, so you can replace it by its actual value : Const xlUp = -4162 (&HFFFFEFBE), let me know if it is enough! ;)

bod Over a year ago

Can you be more specific where exactly to insert the actual value?

R3uK Over a year ago

You could either define a variable with that name and set it to the good value. Or you can just replace it directly in the line For i = ... , change ).End(xlUp). to ).End(-4162).

bod Over a year ago

Ofcourse i would love to help you .. but i can't downvote now until the answer is edited

|

Collectives™ on Stack Overflow

Adding items to listbox from excel

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related