Modify (Edit) an Excel File using Vb.Net

You can edit or modify data in an Excel file without even opening the file, using Vb.Net. With few steps, you can do this. In my previous article, I have shown with an example on how to create Excel file using Vb.Net with Auto formatting technique. In this article, I’ll show you how to modify data in an existing Excel file with the click of a button.

Modify or Edit Excel using Vb.Net

Check this article if you are C# developer.

First, we need to add a COM library in our project. In the .Net IDE, click Project from the top menu list and select Add Reference… In the Add Reference window, select COM tabs and find Microsoft Excel 12.0 Object Library from the list. Select it and click OK.

Also Read: How to Read Data from a Closed Excel File or Workbook without Opening it using VBA

Form Design (Form1.vb)

Add four controls on the form. A ComboBox to select employee names, a Textbox for the mobile number, an OpenFileDialog control to open and select the Excel file, whose contents we wish to modify, and finally a Button.

The controls that I have added in the form are all standard controls, which you can easily drag and drop on the form.

Code (Form1.vb)
Option Explicit On
Imports Excel = Microsoft.Office.Interop.Excel          ' EXCEL APPLICATION.

Public Class Form1
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    ' THE CLICK EVENT OF BUTTON WILL OPEN THE DIALOG BOX TO SELECT A FILE. 
    Private Sub cmdSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles cmdSelect.Click

        If Trim(tbMobile.Text) <> "" And Trim(cmbEmp.Text) <> "" Then
            With OpenFileDialog1
                .Title = "Excel File to Edit"           ' DIALOG BOX TITLE.
                .FileName = ""
                .Filter = "Excel File|*.xlsx;*.xls"     ' FILTER ONLY EXCEL FILES IN FILE TYPE.

                Dim sFileName As String = ""

                If .ShowDialog() = DialogResult.OK Then
                    sFileName = .FileName

                    If Trim(sFileName) <> "" Then
                        EditEmpDetails(sFileName)       ' PROCEDURE TO EDIT EMPLOYEE DETAILS.
                    End If
                End If
            End With
        End If
    End Sub

    ' EDIT DETAILS IN THE EXCEL FILE.
    Private Sub EditEmpDetails(ByVal sFile As String)
        ' THE EXCEL NAMESPACE ALLOWS US TO USE THE EXCEL APPLICATION CLASS
        
        xlApp = New Excel.ApplicationClass      
        xlWorkBook = xlApp.Workbooks.Open(sFile)           ' WORKBOOK TO OPEN THE EXCEL FILE.
        xlWorkSheet = xlWorkBook.Worksheets("Employee")    ' THE NAME OF THE WORK SHEET. 

        Dim iRow As Integer = 0
        Dim iCol As Integer = 0

        For iRow = 2 To xlWorkSheet.Rows.Count
            If Trim(xlWorkSheet.Cells(iRow, 1).value) = "" Then
                Exit For        ' BAIL OUT IF REACHED THE LAST ROW.
            End If

            For iCol = 1 To xlWorkSheet.Columns.Count
                If Trim(xlWorkSheet.Cells(1, iCol).value) = "" Then
                    Exit For    ' BAIL OUT IF REACHED THE LAST COLUMN.
                End If

                ' CHECK IF THE SELECTED EMPLOYEE EXISTS AND CHANGE THE MOBILE NO.
                If Trim(xlWorkSheet.Cells(iRow, iCol).value) = Trim(cmbEmp.Text) Then
                    xlWorkSheet.Cells(iRow, iCol + 1) = Trim(tbMobile.Text)
                    Exit For    ' DONE. GET OUT OF THE LOOP.
                End If
            Next
        Next

        xlWorkBook.Close() : xlApp.Quit()

        ' CLEAN UP. (CLOSE INSTANCES OF EXCEL OBJECTS.)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) : xlApp = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) : xlWorkBook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet) : xlWorkSheet = Nothing
    End Sub
End Class
Conclusion

This process is useful if you wish to edit the contents of an Excel file without actually opening it. However, you have to select the file before you do any changes it. In-addition, you must know the location of the columns you wish to modify.

That's it. Thanks for reading.

Related Posts:

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner