Find and Replace in Excel using VB.Net


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/?s=Find+and+Replace+in+Excel+using+VB.Net

As we all know that the Find and Replace in Excel is gives us very limited options.

There are certain limitations for example if you look at the image below, you will realize that you can only replace text in formulas. You have use this option to replace text in Values as well but not Comments.

image_thumb1

Now what if you wanted to replace text only in comments. Well you can do that easily by using this example.

First let’s create a sample Excel file for testing. Create a file as shown below and save it to a location of your choice. I am saving it as Sample.xlsx on my desktop.

If you notice that in Cell A1 we have the Sum formula. In D1 We have the Comment and C5 we have some Normal Text. In the example below we will replace

1) “Sum” by say “Max” in the formula

2) “Siddharth” by say “Sid” in the comment and

3) “sample” by “some” in the text.

image

Now let’s create a basic Form with the necessary controls. Please see the below image.

image

I am using the OpenFileDialog1 for my file browsing. Once your form is ready, simply paste the below code.

Also before you use this code, you need to set reference to Excel Object Library. Please see this link on how to do that Smile

http://siddharthrout.wordpress.com/vb-net-and-excel/

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    '~~> Define variables to store Find and Replace values
    Dim ToFind As String, ToReplace As String

    '~~> Code to browse for the Excel File
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        With OpenFileDialog1
            .DefaultExt = "xlsx"
            .DereferenceLinks = True
            .Filter = _
             "Excel files (*.xlsx)|*.xlsx"
            .Multiselect = False
            .Title = "Select an Excel file to open"
            .ValidateNames = True

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    TextBox3.Text = .FileName
                Catch fileException As Exception
                    Throw fileException
                End Try
            End If
        End With
    End Sub

    '~~> Code to do a find and replace
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Check if User has selected a file
        If TextBox3.Text = "" Then
            TextBox3.Select()
            MessageBox.Show("Please select a file first")
            Exit Sub
        End If

        '~~> Check if Find Box is filled up
        If TextBox1.Text = "" Then
            TextBox1.Select()
            MessageBox.Show("Please type the word which you want to find")
            Exit Sub
        End If

        '~~> Check if Replace box is filled up
        If TextBox2.Text = "" Then
            TextBox2.Select()
            MessageBox.Show("Please type the word with which you want to replace the above word")
            Exit Sub
        End If

        '~~> Check if user has indicated his/her choice
        If RadioButton1.Checked = False And RadioButton2.Checked = False And RadioButton3.Checked = False Then
            MessageBox.Show("Please indicate whether you want to replace text in formula, value or comment")
            Exit Sub
        End If

        xlWorkBook = xlApp.Workbooks.Open(TextBox3.Text)

        '~~> Replace Sheet1 below by the relevant sheet where you want to do a replace
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Display Excel
        xlApp.Visible = True

        ToFind = TextBox1.Text
        ToReplace = TextBox2.Text

        If RadioButton1.Checked Or RadioButton2.Checked Then
            ReplaceInValuesAndFormulas()
        ElseIf RadioButton3.Checked Then
            ReplaceInComments()
        End If

        '~~> Save the file 
        xlWorkBook.Save()

        '~~> Close the File
        xlWorkBook.Close()

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
    End Sub

    '~~> Sub to replace in values/formulas
    Sub ReplaceInValuesAndFormulas()
        '~~> Note: If the text is not found then Excel will display a popup box 
        xlWorkSheet.Cells.Replace(
    End Sub

    '~~> Sub to replace in comments
    Sub ReplaceInComments()
        Dim Rng As Excel.Range, aCell As Excel.Range

        '~~> -4144 is for xlCellTypeComments
        Rng = xlWorkSheet.UsedRange.SpecialCells(-4144)
        For Each aCell In Rng.Cells
            aCell.Comment.Text(xlApp.Substitute(aCell.Comment.Text, ToFind, ToReplace))
        Next aCell
    End Sub

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class
In the above code, I am using two different subs. Please see below for more details.

1) Find and Replace Text in Values/Formulas

image

The code that I used is

    '~~> Sub to replace in values/formulas
    Sub ReplaceInValuesAndFormulas()
        '~~> Note: If the text is not found then Excel will display a popup box 
        xlWorkSheet.Cells.Replace(ToFind, ToReplace, 2, 1, False, False, False)
    End Sub

Let’s understand how that works. The syntax of cells.Replace is as shown in the image below

image

Let’s Break it up

Parameters
What
: Required Object. The data to search for. Can be a string or any Microsoft Excel data type.

Replacement: Required Object. The data for replacing. Can be a string or any Microsoft Excel data type.

LookAt : Optional Object. Can be one of the following XlLookAt constants: xlWhole or xlPart. Value of constant  xlWhole is 1 and xlPart is 2

SearchOrder : Optional Object. Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns. Value of constant  xlByRows is 1 and xlByColumns is 2

MatchCase : Optional Object. True to make the search case sensitive. The default value is False.

MatchByte : Optional Object. Used only if you’ve selected or installed double-byte language support. True to have double-byte characters match only double-byte characters; False to have double-byte characters match their single-byte equivalents.

SearchFormat : Optional Object. The search format.

ReplaceFormat : Optional Object. The Replace format.

2) Find and Replace Text in Comments

image

    '~~> Sub to replace in comments
    Sub ReplaceInComments()
        Dim Rng As Excel.Range, aCell As Excel.Range

        '~~> -4144 is for xlCellTypeComments
        Rng = xlWorkSheet.UsedRange.SpecialCells(-4144)
        For Each aCell In Rng.Cells
            aCell.Comment.Text(xlApp.Substitute(aCell.Comment.Text, ToFind, ToReplace))
        Next aCell
    End Sub

In the above code what I am doing is looping through all the cells in the sheet which has comments and then searching for the text which needs to be replaced.

You can also see this VBA code and convert it to VB.Net for more flexibility with the above code.

http://siddharthrout.wordpress.com/2011/07/14/find-and-findnext-in-excel-vba/

Hope this helps Smile

About these ads

4 responses to “Find and Replace in Excel using VB.Net

  1. Ethan June 14, 2012 at 5:01 am

    Could you elaborate on the OpenFileDialog1 part?
    I have no idea of how to do that kind of stuff and it looks like the most basic thing in most of your tutorials…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 31 other followers

%d bloggers like this: