Adding items to listbox from excel

Question

0.00/5 (No votes)

See more:

I have a column in excel file contain 100 row I'm trying to import this column into listbox using a button

The problem is that only 48 row is importing from the excel column .

why not all rows inside the column are 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

Posted 15-Dec-15 23:13pm

Member 12208071

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-12-15T23:40:00

Solution 1

If you look at the edit to my solution to your previous post importing from excel to listbox[^] you will notice that I have already explained what the problem is

To reiterate, the for loop

C#

For i = 0 To AscW(ListBox1.Items.Count.ToString()(i = i + 1)) - 1

is only looking at items that are already in the ListBox

Go back to your previous post and look at the WHILE loop I provided.

Posted 15-Dec-15 23:40pm

CHill60

Comments

Member 12208071 16-Dec-15 5:47am

so much appreciate you answer .
I update my code as you suggest like this

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

While True

cell = "E" & Convert.ToString(i)
i += 1

cell = oSheet.Range(cell).Value
If Not cell Is Nothing Then
ListBox1.Items.Add(cell)
Else
Exit While
End If
ListBox1.Items.RemoveAt(ListBox1.Items.Count - 1)
End While
Next
oExcel.Quit()
End Sub

but a break error occur on this line
cell = oSheet.Range(cell).Value

CHill60 16-Dec-15 5:59am

Get rid of the For loop all together!
And there is no point saying "a break error occur" - you have to tell people what the error is

Gopi06 25-May-20 0:28am

After removing the For loop still its not working :-( please help.

below is my code...
its only importing only 48 rows from excel

Dim oExcel As Object = CreateObject("Excel.Application")
Dim oBook As Object = oExcel.Workbooks.Open(TextBox1.Text)
'Dim oBook As Object = oExcel.Workbooks.Open("C:\Temp\New.xlsx")
Dim oSheet As Object = oBook.Worksheets(1)

Dim i As Integer
Dim cell As String
For i = 0 To AscW(CheckedListBox1.Items.Count.ToString()(i = i + 1)) - 1

cell = "G" & Convert.ToString(i + 1)

cell = oSheet.Range(cell).Value

If cell = "Number" Then
CheckedListBox1.Items.Remove(cell)

Else
CheckedListBox1.Items.Add(cell)

End If

Next

CHill60 26-May-20 6:31am

What a coincidence that only 48 rows are being imported from Excel!
If you have a question then you need to use the red "Ask a Question" link at the top of the page. Very few people will come across this comment to a 5 year old post - I only saw it because I was notified that you had "replied" to me.
When posting your question remember that "not working" is not a good description of your problem - describe your problem in more detail - be specific

Gopi06 25-May-20 0:39am

Tried the below code as well...but not working :-(

Dim oExcel As Object = CreateObject("Excel.Application")

Dim oBook As Object = oExcel.Workbooks.Open("C:\Temp\Test.xlsx")
Dim oSheet As Object = oBook.Worksheets(1)

Dim i As Integer
Dim cell As String

While True

cell = "G" & Convert.ToString(i)
i += 1

cell = oSheet.Range(cell).Value
If Not cell Is Nothing Then
ListBox1.Items.Add(cell)
Else
Exit While
End If
ListBox1.Items.RemoveAt(ListBox1.Items.Count - 1)
End While

oExcel.Quit()