CODEWITHSUNDEEP
c#vbaexcel
Inneshfree
Inneshfree

Reputation: 67

How do you find and replace a word/phrase in excel using C#

I am currently creating a program for staff members to auto populate and print out a check sheet with information based on user input etc. The check sheet is created as an excel document and I want to search the document for a particular word or phrase and replace that with the variable input the user generates.

Example: User inputs room number RM:XXX and the program will the search the document for phrase {Room Number} and replace it with RM:XXX. I found some solutions that did not work with Visual Basic 2017.

The closest solution I came to building a resolution from was:

 Microsoft.Office.Interop.Excel.Application replaceWord = new Microsoft.Office.Interop.Excel.Application();

 Microsoft.Office.Interop.Excel.Workbook wb = default(Microsoft.Office.Interop.Excel.Workbook);

 wb = replaceWord.Workbooks.Open("C:\\test.xlsx");

 wb.Worksheets("Sheet1").Cells.Replace(What: "{Room Number}", Replacement: "RM:XXX", LookAt: XlLookAt.xlWhole, SearchOrder: XlSearchOrder.xlByRows, MatchCase: false, SearchFormat: true, ReplaceFormat: false);

The issue was on the last line wb.Worksheets came up with an error: {"non-invocable member WorkBook.Worksheets cannot be used like a member."}

Can anyone point me in the right direction to where I can find out why wb.Worksheets is not working or throwing an error?

Upvotes: 2

Views: 545

Answers (1)

rene
rene

Reputation: 42434

Worksheets is an property, not an method. That is what the error message is trying to tell you.

The property gives you an Sheets instance that implements an indexer and in C# those are accessed by using []. Your last line should look like this:

wb.Worksheets["Sheet1"]
  .Cells
  .Replace(What: "{Room Number}", 
           Replacement: "RM:XXX", 
           LookAt: XlLookAt.xlWhole, 
           SearchOrder: XlSearchOrder.xlByRows, 
           MatchCase: false,  
           SearchFormat: true, 
           ReplaceFormat: false);

Notice the square brackets at Worksheets["Sheet1"]. Those make a difference.

Upvotes: 2

Related Questions