Visual Basic 2008 / 2010 Tutorials
www.visual-basic-tutorials.com

 

Index

 Update 2007 Excel file record

This tutorial will show you how to update a record in excel in visual basic program.

Please refer to Write to Excel 2007 File to add the declarations.

Lets say we want to update a file that contains products prices. In this example we would like to update the banana price to 0.99 instead of .69

We will use the following form to enter the new price

code:

Imports Excel = Microsoft.Office.Interop.Excel
 

Public Class Form1
 

Dim APP As Excel.Application

Dim worksheet As Excel.Worksheet

Dim workbook As Excel.Workbook

Dim LastRow As Integer

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

App = New Excel.ApplicationClass

workbook = App.Workbooks.Open("Path\file.xlsx")

worksheet = workbook.Worksheets("sheet1")

LastUsedCell()

End Sub

We will use the following code to get the last used row in the excel file.
 

  Public Sub LastUsedCell()

        With worksheet

            LastRow = .Cells(.Rows.Count, 3).End(Excel.XlDirection.xlUp).Row

        End With

    End Sub

Update Button Click Code:
 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        For Each item In worksheet.Range("A1", "A" & LastRow)

            If item.value = TextBox1.Text Then

                Main.items.Cells(item.row, 3).value = TextBox2.Text

            End If

        Next

        workbook.Save()

    End Sub