Excel Replace all found chars

Question

I've been trying to replace all "#" in a Excel worksheet using a winform application...

I tried with something like this

Excel.WorksheetClass excelWorksheet = (Excel.WorksheetClass)application.Worksheets[1];
        excelWorksheet.Cells.Replace(@"\", "", Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, false, Type.Missing, false, false);

But it just replace those "#" that are alone in a cell, if the cell value is "#ABC" then it does not replaces, advanced option in excel allow to do this, but how can I do that using Iterop classes.

Regards

Answer 1

The third parameter XlLookAt is the problem. You need to use xlPart for a search which will match only part of the cell.

Answer 2

Hi ..Can u please provide completee code here to replace text in excel.. i need code right from initiating the excel.. and replacing text in each of sheets..

Thanks in advnace :)

Answer 3

darsahan.

Here's the code I used.

class ExcelOpener
{
    public static void OpenTxtInExcel(string name, string filename)
    {
        Excel.Application application = new Excel.Application();
        object missing = System.Reflection.Missing.Value;
        application.Visible = true;
        application.Workbooks.OpenText
        (
            filename,
            missing,
            1,
            Excel.XlTextParsingType.xlDelimited,
            Excel.XlTextQualifier.xlTextQualifierNone,
            missing,
            missing,
            missing,
            missing, 
            missing,
            true, //True, using a custom delimiter
            @"#", //This is the custom delimiter
            missing,
            missing,
            missing,
            missing,
            missing,
            missing
        );

        Excel.Worksheet excelWorksheet = (Excel.Worksheet)application.Worksheets[1];
        excelWorksheet.Cells.Replace(@"\", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns, false, Type.Missing, false, false); 


    }
}

Regards