Results 1 to 19 of 19

Thread: Work with the application each time with a different comoboxvalue

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Unhappy 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.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Work with the application each time with a different comoboxvalue

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

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    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

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  5. #5

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    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

  6. #6
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    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)

  7. #7

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    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.

  8. #8
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    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)

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Work with the application each time with a different comoboxvalue

    Quote Originally Posted by highfly884 View Post
    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.

  10. #10

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    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 .

  11. #11

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    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

  12. #12

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    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

  13. #13
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Work with the application each time with a different comoboxvalue

    Quote Originally Posted by highfly884 View Post
    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

  14. #14
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Work with the application each time with a different comoboxvalue

    Quote Originally Posted by highfly884 View Post
    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

  15. #15
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    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.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  16. #16

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    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

  17. #17

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    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

  18. #18
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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

  19. #19

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    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
  •  



Click Here to Expand Forum to Full Width