importing from excel to listbox

Question

0.00/5 (No votes)

See more:

I'm having an break error while trying to import column from excel to my listbox in vb.net form

the problem is after importing all the items inside the column successfuly to the listbox . A break error shown up and close the program. here is the break error

An unhandled exception of type 'System.ArgumentNullException' occurred
in System.Windows.Forms.dll
Additional information: Value cannot be null))

and here is my code

VB

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.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))

        cell = "E" & Convert.ToString(i + 1)
        cell = oSheet.Range(cell).Value
        ListBox1.Items.Add(cell)

    Next
    oExcel.Quit()
End Sub

[EDIT from CHill60 - OP Code from solution with further query]

Quote:
Thank you very much it's work now

If you allow me to ask you a little question about something related

when importing all column values into listbox
i dont want it to import the last row of the column in the excel file

here is my code

VB.NET

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.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

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

        cell = oSheet.Range(cell).Value
        If cell = "" Then
            Exit For
        Else
            ListBox1.Items.Add(cell)
        End If
    Next
    oExcel.Quit()
End Sub

Posted 14-Dec-15 21:45pm

Member 12208071

Updated 15-Dec-15 3:11am

CHill60

v3

Add a Solution

Comments

Richard MacCutchan 15-Dec-15 4:00am

One of your parameter values is NULL. Use your debugger to find out where and correct it.

Member 12208071 15-Dec-15 4:11am

error break refers me to this line : ListBox1.Items.Add(cell)

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 · Accepted Answer · 2015-12-15T00:00:00

If you put a breakpoint on

C#

cell = oSheet.Range(cell).Value

you will see that the value of cell is Nothing
You need to make sure you actually have some data in that cell before attempting to add it to the ListBox e.g.

VB.NET

If Not cell Is Nothing Then ListBox1.Items.Add(cell)

[EDIT after follow up query]
I've looked at the code properly now and you appear to only load data from the spreadsheet if there is already information in the ListBox - see that For statement.
As I understand it you want all of the data from the spreadsheet except for the last row.

Because you have used CreateObject for the excel application you don't have access to as many properties as you would by using (for example) interop.

I would just read everything from the sheet and exit when you hit a blank cell (as you have already in your code). Note that there is a "danger" of running off the end of the sheet ... but you shouldn't have that much data in a GUI so I'm not checking that i is greater than the maximum row number.

Something like this ...

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

End While

Once you have all of the data then just remove the last entry

C#

ListBox1.Items.RemoveAt(ListBox1.Items.Count - 1)

NB - if you are going to use a Sorted ListBox then make sure you switch of the sorting

VB.NET

ListBox1.Sorted = False"

and switch sort back on only after you have filled it and removed the last item

There are some great articles on CodeProject if you want to explore other ways of manipulating Excel with VB.Net

importing from excel to listbox

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us