Modify/Edit an Excel File using Vb

You can edit or modify data in an Excel file without even opening the file in Vb. In my previous article, I have shown with an example on how to create Excel file using VB (and C#) with Auto format technique. In this article, I’ll show you how to modify data in an Excel file with the click of a button.

Check this article if you are C# developer.

Modify/Edit Excel using VB

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.

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 namespance allows us to use the excel application class.
        
        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open(sFile)           ' Open the workbook.
        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 is useful when you want to edit the contents of an Excel file without actually opening it. However, you have to select the file before you make any changes in it. In-addition, you must know the location of the columns you wish to modify.

← PreviousNext →