Live update data in Excel sheet from VB.NET Form

Question

I have a form in VB.NET with .NET 4.5. I have an EXCEL file opened side by side of the form.

I want to see the updated data from code, LIVE, in the EXCEL sheet. But cant see the data.

Below is the code

Imports Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    'Me.OpenFileDialog1.ShowDialog()
    Dim xlApp As Application
    Dim xlWorkBook As Workbook
    Dim xlWorkSheet As Worksheet

    xlApp = New ApplicationClass
    'xlApp.ScreenUpdating = False
    xlWorkBook = xlApp.Workbooks.Open("E:\BACKUP\TRY.xls")
    xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
    'display the cells value B2
    MsgBox(xlWorkSheet.Cells(8, 1).value)    'GETTING EXISTING VALUE OK
    'edit the cell with new value
    xlWorkSheet.Cells(2, 2) = "HI"    'WANT TO SEE THIS DATA BEING LIVE UPDATED
    'xlWorkBook.Close()    'DONT WANT TO CLOSE THE OPENED SHEET/WORKBOOK
    'xlApp.ScreenUpdating = True
    xlApp.Quit()

    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)

End Sub

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub
End Class

Answer 1

The following example the main excel work is done in a code module, the Worksheet object if the sheet was found is returned to the caller, in this case a form button click.

Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports ST = System.Runtime.InteropServices
Module OpenWorkSheets2
    ''' <summary>
    ''' Open file, activate sheet while keeping
    ''' the excel file open
    ''' </summary>
    ''' <param name="FileName">Path and file name to open</param>
    ''' <param name="SheetName">Worksheet to work with</param>
    ''' <param name="FoundSheet">True indicates we are good to use the returned sheet object</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function OpenExcel1(
        ByVal FileName As String,
        ByVal SheetName As String,
        ByRef FoundSheet As Boolean) As Excel.Worksheet

        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)
        xlApp.Visible = True
        xlWorkSheets = xlWorkBook.Sheets

        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

            If xlWorkSheet.Name = SheetName Then
                xlWorkSheet.Activate()
                FoundSheet = True
                Exit For
            End If

            ST.Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        Next

        Return xlWorkSheet

    End Function
End Module

Place the following at the top of the form

Imports Excel = Microsoft.Office.Interop.Excel

Add to form level variables

Private xlWorkSheet As Excel.Worksheet = Nothing
Private Success As Boolean = False

In a button click event call the function above with as the first argument the file name and path followed by the sheet name then pass in our variable Success above.

xlWorkSheet = OpenExcel1(
    IO.Path.Combine(Application.StartupPath, "Customers.xlsx"),
    "Orders",
    Success)

Now to change a cell value and see it live. Add a TextBox, place something in it and call it as follows. Note the cell address can be what you want.

If Not String.IsNullOrWhiteSpace(TextBox1.Text) Then
    If Me.Success Then
        xlWorkSheet.Cells(2, 2) = TextBox1.Text
    Else
        MessageBox.Show("Failed")
    End If
Else
    MessageBox.Show("Please enter a value")
End If