locked
Error when reading an Excel sheet RRS feed

  • Question

  • Hi,
    I receive an error when i try to get data from an Excel sheet.
    Strange thing is that the code worked before I reinstall my computer.

    When I try to read from the sheet then I receive the following error:
    TextBox1.Text = WorkSheet.Cells(4, 1).Value
    Object reference not set to an instance of an object.

    I have the references set...
    What is wrong or what causes this error?

    Imports Microsoft.Office.Interop
    
    Public Class Form1
    
      Public App As Microsoft.Office.Interop.Excel.Application
      Public WorkSheet As Microsoft.Office.Interop.Excel.Worksheet
      Public WorkBook As Microsoft.Office.Interop.Excel.Workbook
    
    
      Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
    
        'clean it.
        WorkBook.Save()
        WorkBook.Close()
        App.Quit()
    
      End Sub
    
    
      Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
        'this is hard coded
        App = New Excel.Application()
        WorkBook = App.Workbooks.Open("C:\found.xlsx")
        WorkSheet = WorkBook.Worksheets("Blad1")
    
      End Sub
    
      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
        'If you have Excel running please close it, if you run this application then you receive an error.
        TextBox1.Text = WorkSheet.Cells(4, 1).Value
        TextBox2.Text = WorkSheet.Cells(4, 2).Value
        TextBox3.Text = WorkSheet.Cells(4, 3).Value
        TextBox4.Text = WorkSheet.Cells(4, 5).Value
    
      End Sub
    End Class

    Monday, July 11, 2011 3:22 PM

Answers

  • Finaly the problem is solved.
    Received several updates this evening...pfffffff 2 hours install time
    Changed this: Textbox1.Text = WorkSheet.Cells(4,1) to: Textbox1.Text = WorkSheet.Cells(4,1).Value

    No errors anymore
    Happy now and going to sleep (it's bedtime in the Netherlands) -:)

    • Marked as answer by dantehavenaar Thursday, July 14, 2011 4:47 PM
    Wednesday, July 13, 2011 7:57 PM

All replies

  • Are you sure WorkSheet is defined?  It might be nothing.  Put a breakpoint on the line of code where you set TextBox1.Text and see if Worksheet is defined.
    vb-tips.com
    Monday, July 11, 2011 4:23 PM
  • That is the problem, but how is that possible
    WorkSheet = Nothing
    WorkBook = {System.__ComObject}

    Monday, July 11, 2011 5:43 PM
  • Hi Dantehavenaar,

    Welcome to the MSDN Forum.

    Does the worksheet named "Blad1" exist?

    Please check it first, I have test your code, it works fine.

    In addition, you can also try this code

     WorkSheet = WorkBook.Worksheets(1)

    I hope this will be helpful, if you have further concerns, please feel free to let me know.

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 13, 2011 12:26 PM
    Moderator
  • Hi Mike,
    Blad1 is Dutch for Sheet1.
    Also tried your option WorkSheet = Workbook.WorkSheets(1) >> Same error.


    Strange thing is that the code worked for me either before I reinstalled my computer.
    The only difference now is that I had Office 2007 installed before the computer crash and now I have Office 2010.
    Is there a difference between the Microsoft.Office.Interop .NET and COM ?
    I have tried both but I received the same error.
    OS is Windows 7 64b

    I have added some pictures with the errors.
    As you can see is Dim WorkSheet as Excel.WorkSheet   Nothing
    I have changed the code Textbox1.text = WorkSheet.Cells(4,1)  to WorkSheet.Cells(4,1).ToString
    That because the sugestion is in picture 3: "Conversion from type 'Range' to type 'String' is not valid.
    When I run the code again the error "Object reference not set to an instance of an object" occurred.
     

    http://www.box.net/shared/5rpglboxcueipmk501hg

    http://www.box.net/shared/8sz5js0ua8r9kzlfrdf6

    http://www.box.net/shared/6cyqy94lr9fomte9phfe

    http://www.box.net/shared/sacp6ao7tzl6md07d4i1

    Regards,
    Dante


    The Netherlands

    Wednesday, July 13, 2011 5:06 PM
  • Finaly the problem is solved.
    Received several updates this evening...pfffffff 2 hours install time
    Changed this: Textbox1.Text = WorkSheet.Cells(4,1) to: Textbox1.Text = WorkSheet.Cells(4,1).Value

    No errors anymore
    Happy now and going to sleep (it's bedtime in the Netherlands) -:)

    • Marked as answer by dantehavenaar Thursday, July 14, 2011 4:47 PM
    Wednesday, July 13, 2011 7:57 PM