Find and replace in Excel using Regex

[mrtrend]

Hi all...

I am a Jr. C# programmer and I got this working last night, pulled it back down from my repository, and I've been sitting here for hours and can't figure out how I got this to work:

Problem:
Match a specific number format in all sheets in the current Excel workbook using Regex and replace all values with a pre-defined number.

Here's what I have so far.. I swear it worked last night... but I'm sitting here trying to figure out how I got workSheet as a string into regex... I can't remember...

I would REALLY appreciate any assistance... like most coders, I can't do anything else until I fix this... lol


public void ExcelOpenSpreadsheets()

{

try

{

// Initialize an instance of Excel.

Excel.Application excelApp = new Excel.ApplicationClass();

// Setup so user can see Excel

excelApp.Visible = true;

// Retrieve the path from our ofn variable (ShowDialog)

string workbookPath = ofn;

// Open the workbook from the workbookPath variable

Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

// Activate excelSheets with ALL sheets in workbook

Excel.Sheets excelSheets = excelWorkbook.Worksheets;

// Get the sheets we're looking for - TESTING - 1 SHEET

string currentSheet = "Sheet1";

Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);


// Define our regex expression

Regex nExpression = new Regex(@"\b\d{2}\d{2}\d{4}\b", RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace);


// Now that we have our sheet, let's scan the entire sheet

Regex matchN = Regex.IsMatch(excelWorksheet, nExpression);


// Replace the Regex matches with a predefined number

excelWorksheet.Cells.Replace(matchN, "00120098", Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, false, Type.Missing, false, false);

}

catch(Exception ee)

{

MessageBox.Show("Exception: " + ee);

}

}