-
Sep 24th, 2021, 07:58 AM
#1
Thread Starter
Member
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
Last edited by highfly884; Sep 27th, 2021 at 06:36 AM.
-
Sep 24th, 2021, 08:15 AM
#2
Re: Work with the application each time with a different comoboxvalue
There is obviously no worksheet in the workbook with that name/index.
-
Sep 24th, 2021, 08:30 AM
#3
Thread Starter
Member
Re: Work with the application each time with a different comoboxvalue
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
-
Sep 24th, 2021, 08:40 AM
#4
Re: Work with the application each time with a different comoboxvalue
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.
-
Sep 24th, 2021, 08:48 AM
#5
Thread Starter
Member
Re: Work with the application each time with a different comoboxvalue
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
-
Sep 24th, 2021, 09:04 AM
#6
Re: Work with the application each time with a different comoboxvalue
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)
-
Sep 24th, 2021, 09:18 AM
#7
Thread Starter
Member
Re: Work with the application each time with a different comoboxvalue
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 ?
Last edited by highfly884; Sep 24th, 2021 at 09:44 AM.
-
Sep 24th, 2021, 09:36 AM
#8
Re: Work with the application each time with a different comoboxvalue
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
The best friend of any programmer is a search engine
"Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
“They did not know it was impossible so they did it” (Mark Twain)
-
Sep 24th, 2021, 09:39 AM
#9
Re: Work with the application each time with a different comoboxvalue
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.
-
Sep 24th, 2021, 09:40 AM
#10
Thread Starter
Member
Re: Work with the application each time with a different comoboxvalue
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 .
-
Sep 24th, 2021, 09:43 AM
#11
Thread Starter
Member
Re: Work with the application each time with a different comoboxvalue
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
-
Sep 24th, 2021, 11:22 AM
#12
Thread Starter
Member
Re: Work with the application each time with a different comoboxvalue
arnoutdv i tried your solution but it stills doesn't not work. can you please suggest me other wise
-
Sep 24th, 2021, 02:04 PM
#13
Re: Work with the application each time with a different comoboxvalue
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.
My usual boring signature: Nothing
-
Sep 24th, 2021, 02:08 PM
#14
Re: Work with the application each time with a different comoboxvalue
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.
My usual boring signature: Nothing
-
Sep 24th, 2021, 02:10 PM
#15
Re: Work with the application each time with a different comoboxvalue
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.
-
Sep 26th, 2021, 10:15 AM
#16
Thread Starter
Member
Re: Work with the application each time with a different comoboxvalue
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
-
Sep 26th, 2021, 10:18 AM
#17
Thread Starter
Member
Re: Work with the application each time with a different comoboxvalue
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
-
Sep 27th, 2021, 04:26 AM
#18
Re: Work with the application each time with a different comoboxvalue
There is a guide to debugging (including breakpoints etc) here:
https://docs.microsoft.com/en-gb/vis...h-the-debugger
-
Sep 27th, 2021, 06:35 AM
#19
Thread Starter
Member
Re: Work with the application each time with a different comoboxvalue
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|