CODEWITHSUNDEEP
c#excelexcel-interop
Jan
Jan

Reputation: 137

How to preserve a formula's reference to a worksheet when the worksheet is deleted and replaced using C#?

How to keep formulas working after replacing a sheet? I mean How to preserve a formula's reference to a worksheet when the worksheet is deleted and replaced? but in C#

using Excel = Microsoft.Office.Interop.Excel;

 Excel.Application excelApp = new Excel.ApplicationClass();
 Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(connection.DataSource, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
    foreach (Excel.Worksheet sh in excelWorkbook.Sheets)
    {
        if (sh.Name == SheetName || sh.Name == "_"+SheetName)
        {
            sh.Application.DisplayAlerts = false;
            sh.Delete();
        }
    }
    excelWorkbook.Save();
    excelWorkbook.Close(true, excelWorkbook.FullName, null);
    excelApp.Quit();

Later I'm invoking SQL command "Create Table" with the same name. All formulas are not working

Upvotes: 2

Views: 1232

Answers (1)

John Bustos
John Bustos

Reputation: 19574

What you will have to do is, in the sheet that contains the formulas, firstly convert them away from formulas (this will stop Excel from creating errors in your formulas when you delete the sheet that thse formulas are referencing).

So, for example, do a Find/Replace and replace "=" with, for example "#" (or any other character or set of characters you're sure you're not using in that sheet). Thsi will stop the formula from creating an errored reference.

Then delete the sheet, add in the new sheet

Then re-replace the values in your formula sheet back from "#" to "=".

So long as your new sheet has the same name as the old sheet, you should be good to go.

Hope this helps.

Upvotes: 1

Related Questions