void _ExcelApplication_WorkbookNewSheet(Microsoft.Office.Interop.Excel.Workbook Wb, object Sh)
{
DisplayInWatchWindow(WorkbookNewSheet++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
if (ExcelHlp.HasCustomTableOfContents())
{
DisplayInWatchWindow(1, System.Reflection.MethodInfo.GetCurrentMethod().Name + " - AddTableOfContents()");
Actions.Excel_TableOfContents.CreateTableOfContents();
}
}
public static void CreateTableOfContents()
{
Worksheet tableOfContents;
int row = 3; // Starting Row
int column = 1; // Starting Column
Boolean currentSheetProtectionMode;
Workbook activeWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
try
{
tableOfContents = activeWorkbook.Sheets["Table of Contents"];
}
catch (Exception)
{
tableOfContents = ExcelHlp.NewWorksheet("Table of Contents", beforeSheetName: "FIRST");
//tableOfContents = activeWorkbook.Sheets.Add();
//tableOfContents.Name = "Table of Contents";
}
tableOfContents.Columns["A:A"].ClearContents();
foreach (Worksheet sheet in activeWorkbook.Sheets)
{
switch (sheet.Name)
{
case "Table of Contents":
break;
default:
// Unprotect the sheet before adding the hyperlink
currentSheetProtectionMode = ExcelHlp.ProtectSheet(sheet, false);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Hyperlinks.Add(Anchor: sheet.Cells[1, 1], Address: "", SubAddress: "'" + tableOfContents.Name + "'!A1", TextToDisplay: tableOfContents.Name);
// Then restore the setting
ExcelHlp.ProtectSheet(sheet, currentSheetProtectionMode);
// Now update the Table of Contents Sheet
tableOfContents.Cells[row, column].Value = sheet.Name;
tableOfContents.Hyperlinks.Add(Anchor: tableOfContents.Cells[row, column], Address: "", SubAddress: "'" + sheet.Name + "'!A1", TextToDisplay: sheet.Name);
row += 1;
break;
}
}
tableOfContents.Columns["A:A"].EntireColumn.AutoFit();
if (!ExcelHlp.HasCustomTableOfContents())
{
ExcelHlp.CustomTableOfContentsExists(true);
}
}
