How can I search for text in whole workbook?

Question

I need to search an Excel file for a particular text.

I found this article at MS. https://learn.microsoft.com/en-us/visualstudio/vsto/how-to-programmatically-search-for-text-in-worksheet-ranges?view=vs-2019 I modified it for searching the whole workbook. It works fine till the time there is only one sheet with search value. If any other sheet also have the value, then Excel freezes with hourglass pointer. Ultimately I need to kill the process.

Here is my code:

    public int searchcount(string srchtrm)
    {
        Excel.Range currentFind = null;
        Excel.Range firstFind = null;
        int stcount = 0;

        foreach (Excel.Worksheet w in Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets)
        {
            w.Select();   
            Excel.Range Fruits = w.UsedRange;

            currentFind = Fruits.Find(srchtrm, Type.Missing,
                Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
                Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
                Type.Missing, Type.Missing);

            while (currentFind != null)
            {
                if (firstFind == null)
                {
                    firstFind = currentFind;
                }

                else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1)
                      == firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
                {
                    break;
                }

                currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                currentFind.Font.Bold = true;
                currentFind = Fruits.FindNext(currentFind);
                stcount = stcount + 1;
            }
        }
        return stcount;
    }

Answer 1

You are not resetting the currentFind and the firstFind variables. This is causing the endless loop, since you are using the currentFind and the firstFind values from the previous sheet once you have more that 1 sheet in the workbook.

The easiest solution would be to declare these variables in the inner loop:

int stcount = 0;

foreach (Excel.Worksheet w in Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets)
{
    Excel.Range currentFind = null;
    Excel.Range firstFind = null;

    w.Select();   
    Excel.Range Fruits = w.UsedRange;

    // REST of the code....
}