Change Excel file and Update via Visual Basic application

DRGAGI 146 Reputation points
2020-12-27T20:09:10.11+00:00

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?
51513-snap1.jpg

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,663 questions
0 comments No comments
{count} votes

Accepted answer
  1. Xingyu Zhao-MSFT 5,361 Reputation points
    2020-12-28T07:01:51.097+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. DRGAGI 146 Reputation points
    2020-12-27T20:11:49.077+00:00
    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