none
Refresh data in existing open Excel worksheet in Visual Basic RRS feed

  • Question

  • Hello 

    I want to refresh the data of an already open excel worksheet. I have a siple code with a text box and all i want is, every time i click on a button the text box value go and replace the previous excell cell value. Her is my code

    Imports System.Data.OleDb
    Imports Excel = Microsoft.Office.Interop.Excel
    
    
    
    Public Class Form1
        Dim objApp As Excel.Application
        Dim objBook As Excel._Workbook
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ' Create a new instance of Excel and start a new workbook.
            objApp = New Excel.Application()
            objBooks = objApp.Workbooks
            objBook = objBooks.Add
            objSheets = objBook.Worksheets
            objSheet = objSheets(1)
    
            objSheet.Range("A1").Value = textbox1.text
    
            'Return control of Excel to the user.
            objApp.Visible = True
            objApp.UserControl = True
    
            'Clean up a little.
            range = Nothing
            objSheet = Nothing
            objSheets = Nothing
            objBooks = Nothing
        End Sub
    
       
    End Class
    but what happens here is every time i click the buton i get the text box value but in a new Workbook. 


    • Edited by Anastasia24 Sunday, February 12, 2017 4:31 PM
    Sunday, February 12, 2017 4:28 PM

All replies

  • Hi Anastasia24,

    I can see that on button click event you are creating a new excel object and adding a new excel file.

    you can create excel file on form load and use that same excel file after that on button click event to add value in cell.

    also you not need to clear the objects at the end of button click event.

    below is your code modified by me.

    Imports System.Data.OleDb
    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class Form1
    
    
        Dim objApp As Excel.Application
        Dim objBook As Excel._Workbook
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ' Create a new instance of Excel and start a new workbook.
    
            objSheet.Range("A1").Value = TextBox1.Text
    
            'Return control of Excel to the user.
            objApp.Visible = True
            objApp.UserControl = True
    
            'Clean up a little.
            'range = Nothing
            'objSheet = Nothing
            'objSheets = Nothing
            'objBooks = Nothing
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            objApp = New Excel.Application()
            objBooks = objApp.Workbooks
            objBook = objBooks.Add
            objSheets = objBook.Worksheets
            objSheet = objSheets(1)
    
        End Sub
    End Class
    

    Output:

    I just try to solve the issue to make it work but you need to properly write your code and save the file.

    Regards

    Deepak


    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.

    Monday, February 13, 2017 2:21 AM
    Moderator