Change Excel file and Update via Visual Basic application

Question

Hello, i have this form to import Excel file into Datagridview, i am wondering if there is a way how to change data and save the changes in the Excel file that i loaded into Datagrid?

Answer 1

Hi @DRGAGI ,

how to change data and save the changes in the Excel file

After making a test based on your code, you can try the following code to save changes to Excel file.

Imports Excel = Microsoft.Office.Interop.Excel  

'...'  
    Dim isChanged As Boolean = False  
    Dim sheetName As String  
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click  
        sheetName = cboSheet.Text  
        If sheetName IsNot Nothing Then  
            SaveToExcel(TextBox1.Text, sheetName)  
            isChanged = False  
        End If  
    End Sub  
    Private Sub cboSheet_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboSheet.SelectedIndexChanged  
        If isChanged Then  
            Dim confirmResult = MessageBox.Show("DataGridView cell value have been changed, do you wang to save it?", "Confirm save", MessageBoxButtons.YesNo)  
  
            If confirmResult = DialogResult.Yes Then  
                SaveToExcel(TextBox1.Text, sheetName)  
                isChanged = False  
            Else  
                isChanged = False  
            End If  
        End If  
        Dim dt As DataTable = tables(cboSheet.SelectedItem.ToString())  
        DataGridView1.DataSource = dt  
    End Sub  
    Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged  
        isChanged = True  
        sheetName = cboSheet.Text  
    End Sub  
    Private Sub SaveToExcel(ByVal filePath As String, ByVal sheetName As String)  
  
        Dim xlApp As Excel.Application = New Excel.Application()  
        Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(filePath)  
        xlApp.Visible = False  
        Dim xlWorkSheets As Excel.Sheets = xlWorkBook.Worksheets  
        Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkSheets(sheetName), Excel.Worksheet)  
  
        For i = 0 To DataGridView1.RowCount - 2  
            For j = 0 To DataGridView1.ColumnCount - 1  
                xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1.Item(j, i).Value.ToString()  
            Next  
        Next  
        xlApp.DisplayAlerts = False  
        xlWorkBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)  
        xlApp.Quit()  
        releaseObject(xlWorkSheet)  
        releaseObject(xlWorkBook)  
        releaseObject(xlApp)  
    End Sub  
    Private Sub releaseObject(ByVal obj As Object)  
        Try  
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)  
            obj = Nothing  
        Catch ex As Exception  
            obj = Nothing  
            MessageBox.Show("Exception Occured while releasing object " & ex.ToString())  
        Finally  
            GC.Collect()  
        End Try  
    End Sub  

Hope it could be helpful.

Best Regards,
Xingyu Zhao
*
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Imports System.IO
Imports ExcelDataReader

Public Class Form1

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

        Dim ofd As OpenFileDialog = New OpenFileDialog() With {.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx"}
        If ofd.ShowDialog() = DialogResult.OK Then
            TextBox1.Text = ofd.FileName
            Using stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read)
                Using reader As IExcelDataReader = ExcelReaderFactory.CreateReader(stream)
                    Dim result As DataSet = reader.AsDataSet(New ExcelDataSetConfiguration() With {
                                                             .ConfigureDataTable = Function(__) New ExcelDataTableConfiguration() With {
                                                             .UseHeaderRow = True}})
                    tables = result.Tables
                    cboSheet.Items.Clear()

                    For Each table As DataTable In tables
                        cboSheet.Items.Add(table.TableName)

                    Next


                End Using
            End Using

        End If



    End Sub

    Private Sub cboSheet_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboSheet.SelectedIndexChanged

        Dim dt As DataTable = tables(cboSheet.SelectedItem.ToString())
        DataGridView1.DataSource = dt

    End Sub
End Class