How to Manipulate Excel Cell Data in Visual Basic

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Manipulate Excel Cell Data in Visual Basic
Learn how to edit Excel cell data using Visual Basic.NET. (stock image of earth in binary code image by Ruslana Stovner from

Learning how to manipulate Microsoft Office Excel data can save you time when you need to modify workbook cell values from a Visual Basic application. Excel is a spreadsheet application that's part of the Microsoft Office suite. Microsoft Visual Basic.NET is an object oriented programming language that's relatively easy to learn and use. In a few steps you can write VB.NET code to modify cell values in an Excel workbook.

Skill level:


  1. 1

    Create a new Excel spreadsheet and add the following data:

    Type "Field1" in "A1", "Field2" in "B1", and "Field3" in "C1".

    Type "1" in "A2", "2" in "B2", and "3" in "C2"

    Save it to "C:\" as "manipulateExcel.xls".

  2. 2

    Start Microsoft Visual Basic, click the "File" menu and select "New Project". Click "Windows Forms Application" and select "OK". Double-click "Button" on the "Tools" pane to add a new button to your form.

    Double-click "Button1" on your form to open the "Form1.vb" module.

  3. 3

    Copy and paste the following code under "Public Class Form1" to create a connection to your Excel workbook.

    Private con As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

               "Data Source=C:\manipulateExcel.xls;" & _
               "Extended Properties=""Excel 8.0;HDR=YES"""
  4. 4

    Copy and paste the following code under "Button1_Click" to open "manipulateExcel.xls" and edit the cell data.

  5. 5

    Dim XLConnection As New OleDbConnection(con)

        Dim XLDataAdapter As New OleDbDataAdapter("Select * From [Sheet1$]", XLConnection)
        Dim XLDataSet As DataSet = New DataSet()
        XLDataAdapter.Fill(XLDataSet, "Sheet1")
        XLDataAdapter.UpdateCommand = New OleDbCommand( _
           "UPDATE [Sheet1$] SET field1 = ?, field2=?, field3=?", XLConnection)
        XLDataAdapter.UpdateCommand.Parameters.Add("@field1", OleDbType.Numeric).SourceColumn = "field1"
        XLDataAdapter.UpdateCommand.Parameters.Add("@field2", OleDbType.Currency).SourceColumn = "field2"
        XLDataAdapter.UpdateCommand.Parameters.Add("@field3", OleDbType.Currency).SourceColumn = "field3"
        XLDataSet.Tables(0).Rows(0)("field1") = 1000
        XLDataSet.Tables(0).Rows(0)("field2") = 10.1
        XLDataSet.Tables(0).Rows(0)("field3") = 500.1
        XLDataAdapter.Update(XLDataSet, "Sheet1")
  6. 6

    Press "F5" to run your application.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.