Work with the application each time with a different comoboxvalue

hello guys , i need your help with my application.

im trying to run the application each time with a different comobox value. but the problem that the code works for the first time i click on the button 2 and when i try to change the combobox value run it with a different comobox value it shows this erreur
"Object reference not set to an instance of an object" on the ws1.

can you please show me how to run the app multiple times without this probleme .

Code:

---

Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports System.Windows.Forms.OpenFileDialog

Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlworkbook As Excel.Workbook
Dim ws1 As Excel.Worksheet
Dim cell As Excel.Range

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged

End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

    End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        OpenFileDialog1.ShowDialog()
        TextBox1.Text = OpenFileDialog1.FileName
        xlworkbook = xlApp.Workbooks.Open(TextBox1.Text)

        For Each xlWorkSheet In xlApp.Sheets
            ComboBox1.Items.Add(xlWorkSheet.Name)
        Next
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button3.Click

        ws1 = xlApp.Workbooks.Open(TextBox1.Text).Worksheets(ComboBox1.SelectedItem) ' here where the error shows
        cell=ws1.Cells(3, 5)
        cell.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
            xlworkbook.Close()
            xlApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) : xlApp = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(work1) : work1 = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ws1) : ws1 = Nothing
end sub

---

thank you guys for helping in advanced

There is obviously no worksheet in the workbook with that name/index.

no actually there is a combobox i just pasted the part of the code that interests me. as i sais the code works the firs time perfectly it doesn't work ahen i try to change the combobox value

If there was a worksheet with that name/index then it would be working. It's not working so there isn't. You obviously think there is but you're wrong. The fact that it works the first time just means that there is a worksheet with the name/index you're using the first time. You need to actually debug your code, using a breakpoint. When it breaks on that line, you can loo to see what worksheets actually exist and you will see that the one you're trying to get doesn't.

no the worksheet does existe, i'm sure of the choices i'm making. sense the files are in my desktop so i know what are the name of the worksheets that i have .

i guess that the program considers as if i didn't chose a file and doesn't take in consideration the first time i chose one . im not sure about this

Why do you open the file again?

Code:

---

ws1 = xlApp.Workbooks.Open(TextBox1.Text).Worksheets(ComboBox1.SelectedItem) ' here where the error shows

---

Just use this reference you already created:

Code:

---

xlworkbook = xlApp.Workbooks.Open(TextBox1.Text)

---

Did you check the content of ComboBox1.SelectedItem??
Is it an index or the literal text?

Code:

---

ws1 = xlworkbook.Worksheets(ComboBox1.SelectedItem)

---

Thank you for your answer but xlworkbook is in button1 so how can i call it in button2.

cause when i use what you said they tell me that xlworkbook is used before getting a value ?

xlworkbook is a "global" variable (local in the form class) so you can call it anywhere in the class you created it.

What is "work1" because there is no such thing in your code

Quote:

---

Originally Posted by highfly884

no the worksheet does existe

---

The worksheet you are specifying does not exist in the workbook you are opening. That's a fact. Either you are opening the wrong workbook, specifying the wrong worksheet or you're just wrong. The simple fact is that that exception would not be thrown otherwise. Your dogged determination that you're not wrong doesn't change the fact that you are and it's preventing you finding out why you're wrong. Debug your code properly, like you should have done already. We can't help you because we don't have the file and we don't know what your ComboBox contains. All we can tell you is that that exception would not be thrown if that worksheet existed in that workbook.

i meant xlworkbook not work1 sorry . it is a global variable but it takes its value in the button1 so when i use it in button 2 it says that it has no value .

what do you mean bye debug your code properly ? the combobox has the worksheets that are in the excel file and no i'm not opening the wrong file . sorry if i offended you but what you're suggestion is just not logical for me sorry again

arnoutdv i tried your solution but it stills doesn't not work. can you please suggest me other wise

Quote:

---

Originally Posted by highfly884

no the worksheet does existe, i'm sure of the choices i'm making. sense the files are in my desktop so i know what are the name of the worksheets that i have .

i guess that the program considers as if i didn't chose a file and doesn't take in consideration the first time i chose one . im not sure about this

---

You're also wrong. This is one of the easiest problems to solve. When you get that particular error, some object on the line that is throwing the exception is Nothing. Once you find that thing, you'll know what is causing the exception. At that point, you may not know WHY it is Nothing, but you will know that it is. For that particular exception, this is ALWAYS the first step: Look at the line at the time the exception is thrown, find the item that is Nothing.

The second step is then to find out why it is Nothing, but tells us which object is Nothing, and the line where it is Nothing.

Quote:

---

Originally Posted by highfly884

what do you mean bye debug your code properly ? the combobox has the worksheets that are in the excel file and no i'm not opening the wrong file . sorry if i offended you but what you're suggestion is just not logical for me sorry again

---

Put a breakpoint on the line that causes the exception, or a line just before that. Run until the breakpoint is reached. At that point, you can see all the values in all the objects on that line. Without breakpoints, you are just guessing. With breakpoints, you can see what the computer sees.

Once you find what is Nothing, then you can look earlier to where you thought the variable was set. Putting a breakpoint there, you will be able to see whether or not it really IS set there, and might also see why it is Nothing. You WILL see that it is being set to Nothing, or else you will find out that it is not being set where you thought it was being set.

I replied to your other thread without realizing you had a separate thread opened. This was my response. It is a bit disappointing because it looks like the advice I am giving is basically reiterating what other members already suggested.

There are a couple of things that you can do.

The first, most obvious, is to setup a breakpoint where you assign the value of ws1. Once the breakpoint hits, in your watch window, get the value of workbooks.Open, get the worksheets in one watch variable and the selected item in another. If workbooks.open fails, then you know that the file is failing to load. If worksheets returns an empty collection, then you know that there aren't any worksheets in the workbook. If selected item returns nothing (or the value you're not expecting), then something is wrong with the combo box. If the selected item is what you're expecting, but the value is not returned by the worksheets, then you know that the desired worksheet doesn't exist in the workbook.

The second would be to just break these down into separate statements with conditional statements checking for valid values along the way:

Code:

---

Dim workbook As Excel.Workbook
Try
    workbook = xlApp.Workbooks.Open(TextBox1.Text)
Catch
    MessageBox.Show("Something went wrong opening the workbook.")
    Return
End Try

If (workbook Is Nothing) Then
    MessageBox.Show("Something went wrong opening the workbook.")
    Return
End If

Dim worksheets As Excel.Sheets = workbook.Worksheets
If (worksheets.Count = 0) Then
    MessageBox.Show("There aren't any worksheets in the workbook.")
    Return
End If

Dim selectedItem As Object = ComboBox1.SelectedItem
If (selectedItem Is Nothing OrElse String.IsNullOrWhitespace(selectedItem.ToString())) Then
    MessageBox.Show("There is no selected item in the combobox")
    Return
End If

ws1 = worksheets(ComboBox1.SelectedItem.ToString())
If (ws1 Is Nothing) Then
    MessageBox.Show($"'{ComboBox1.SelectedItem}' is not a valid worksheet in the workbook.")
    Return
End If
'...

---

This essentially does the same thing that you're doing by setting up a breakpoint, only it provides the user (in this case, yourself) with friendly error messages.

thank you for your suggestion but i don't know how to insert a break point. im new to vb.net and working on a really complicated app so i didn't have the time to look on the basics of vb.net . thank you for your suggestion i will look on the internet how do we insert breakpoints

Thank you so much for your suggestion. i will try to find the problem and come back to you if i find it.

p.s i made a new thread thinking that maybe the worksheet.close() function is the probleme and sense it wasn't in this thread i made a new one . thank you so much for your efforts

There is a guide to debugging (including breakpoints etc) here:
https://docs.microsoft.com/en-gb/vis...h-the-debugger

Thank you so much for the link i will use it for my next programmes .

Solution :
for this one the problem was exactly in my saving area. when i wanted to save the changes to the file i added xlapp=nothing, so for the second value of the combobox the xlapp=nothing, this is why it wasn't working so i changed xlapp = New Microsoft.Office.Interop.Excel.Application and it's working.

i still have a problem that the file saved is read only, i don't know why i keep on getting this problem everytime i try to save a file i will look into it more and if i find a solution i will rewrite it here for other people to see .

thank you all guys for your help.