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.
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
- 3) “sample” by “some” in the text
Now let’s create a basic Form with the necessary controls. Please see the below 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.
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 code above, I am using two different subs. Please see below for more details.
1) Find and Replace Text in Values/Formulas
The code that I used:
'~~> 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:
Let’s Break it up:
- 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
'~~> 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 code above.