none
Use Class in VB.NET to ReadWrite a cell in Excel RRS feed

  • Question

  •  First, another code opens a Excel file. Next, the following code  Read/Write a cell in the already-open Excel. But one exception occurs. How to fix it? Thanks!

    System.NullReferenceException: 'Object reference not set to an instance of an object.'

    at refloc.wks = xlWorkBook.ActiveSheet

       Dim refloc As Miscreflocation08142019

      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

            'xlWorkBook = xlApp.ActiveWorkbook
            xlWorkBook.ActiveSheet.Range("C1").Value = "TEXT"
            refloc.wks = xlWorkBook.ActiveSheet
            TextBox2.Text = refloc.Firstdataloc
        End Sub

    Class Module--

    Imports Microsoft.Office.Interop
    Public Class Miscreflocation08142019
        Dim xlApp As Excel.Application
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Public wks As Excel.Worksheet = Nothing
        Dim data As String
        Public Property Firstdataloc() As Integer
            Get
                data = wks.Range("C1").Value
                Return data
            End Get
            Set(ByVal value As Integer)
                wks.Range("C1").Value = value
            End Set
        End Property

    End Class


    • Edited by JayWangTPE Thursday, August 15, 2019 3:53 AM
    Thursday, August 15, 2019 3:52 AM

Answers

  • Hi,

    fix your code:

    Dim refloc As new Miscreflocation08142019

    try my full code:

    Imports Microsoft.Office.Interop
    
    Public Class Form1
        Dim refloc As New Miscreflocation08142019
        Dim xlApp As Excel.Application
        Dim xlWorkBooks As Excel.Workbooks
        Dim xlWorkBook As Excel.Workbook
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Open("D:\test.xlsx")
            xlApp.Visible = True
            xlApp.DisplayAlerts = False
    
        End Sub
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            refloc = New Miscreflocation08142019
            xlWorkBook.ActiveSheet.Range("C1").Value = 1
            refloc.wks = xlWorkBook.ActiveSheet
            TextBox2.Text = refloc.Firstdataloc
            xlWorkBook.Save()
    
        End Sub
        Public Class Miscreflocation08142019
            Dim xlApp As Excel.Application
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Public wks As Excel.Worksheet = Nothing
            Dim data As String
            Public Property Firstdataloc() As Integer
                Get
                    data = wks.Range("C1").Value
                    Return data
                End Get
                Set(ByVal value As Integer)
                    wks.Range("C1").Value = value
                End Set
            End Property
    
        End Class
    
    
    End Class
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JayWangTPE Thursday, August 15, 2019 7:30 AM
    Thursday, August 15, 2019 6:08 AM
  • Hi,

    I didn't have your problem, you close all excels in the process first.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JayWangTPE Friday, August 16, 2019 2:28 AM
    Thursday, August 15, 2019 9:30 AM

All replies

  • Hi,

    fix your code:

    Dim refloc As new Miscreflocation08142019

    try my full code:

    Imports Microsoft.Office.Interop
    
    Public Class Form1
        Dim refloc As New Miscreflocation08142019
        Dim xlApp As Excel.Application
        Dim xlWorkBooks As Excel.Workbooks
        Dim xlWorkBook As Excel.Workbook
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Open("D:\test.xlsx")
            xlApp.Visible = True
            xlApp.DisplayAlerts = False
    
        End Sub
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            refloc = New Miscreflocation08142019
            xlWorkBook.ActiveSheet.Range("C1").Value = 1
            refloc.wks = xlWorkBook.ActiveSheet
            TextBox2.Text = refloc.Firstdataloc
            xlWorkBook.Save()
    
        End Sub
        Public Class Miscreflocation08142019
            Dim xlApp As Excel.Application
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Public wks As Excel.Worksheet = Nothing
            Dim data As String
            Public Property Firstdataloc() As Integer
                Get
                    data = wks.Range("C1").Value
                    Return data
                End Get
                Set(ByVal value As Integer)
                    wks.Range("C1").Value = value
                End Set
            End Property
    
        End Class
    
    
    End Class
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JayWangTPE Thursday, August 15, 2019 7:30 AM
    Thursday, August 15, 2019 6:08 AM
  • Hi Alex,

    Thanks!

    Jay

    Thursday, August 15, 2019 7:30 AM
  • I found the accessed excel file become "Read Only" after running the code.

    It shows xxx.xls is locked for editing by Jay Wang (myself).

    It is kinda troublesome to edit the file. How can I fix it? Thanks.

    Thursday, August 15, 2019 9:20 AM
  • Hi,

    I didn't have your problem, you close all excels in the process first.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JayWangTPE Friday, August 16, 2019 2:28 AM
    Thursday, August 15, 2019 9:30 AM
  • I corrected my error. Now, it works!

    Thanks!

    Friday, August 16, 2019 2:28 AM