刷新Visual Basic中现有的打开Excel工作表中的数据 [英] Refresh data in existing open Excel worksheet in Visual Basic

问题描述

您好 

我想刷新已打开的Excel工作表的数据。我有一个带有文本框的siple代码，我想要的是，每次我点击一个按钮时，文本框值都会被替换以前的excell单元格值。她是我的代码

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. 

推荐答案

您好
Anastasia24，

我可以看到，在按钮点击事件中，您正在创建一个新的Excel对象并添加一个新的excel文件。

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

你可以在表单加载时创建excel文件，然后在按钮点击事件后使用相同的excel文件在单元格中添加值。

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.

下面是您修改的代码。

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

输出：

我只是试试解决问题以使其正常工作，但您需要正确编写代码并保存文件。

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

问候

Deepak

这篇关于刷新Visual Basic中现有的打开Excel工作表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！