Unsupported Sheets in Excel Addin C#

Unsupported Sheets in Excel Addin C#

Unsupported Sheets:

Sometimes developer may in need to eliminate unsupported sheets to perform safe operations on a workbook using VSTO C#. As we know, VSTO C# architecture is object specific environment and if you are iterating all the worksheets in a workbook and if it encounters any other type of sheet, it throws exception. However, with the help of Type property you can determine sheet type.

Excel has following type of sheets:

  1. Chart Sheet
  2. Dialog Sheet (Old VBA technology to design front end)
  3. Excel 4 Int Macro Sheet (Old macro writing technology)
  4. Standard WorkSheet (this is default)

You can see available sheets in below video:

https://youtu.be/4zNqetxOd90

Prerequisites:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Create a ribbon designer and put button

Code example:

public static GetListOfUnSupporetedSheets(Excel.Workbook xlWorkbook)
{
	try
	{
		foreach (Excel.Worksheet xlSheet in xlWorkbook.Sheets)
		{
			string sheetType = string.Empty;
			if (xlSheet.Type != Excel.XlSheetType.xlWorksheet)
			{
				switch (xlSheet.Type)
				{
					case Excel.XlSheetType.xlChart:
						sheetType = "xlChart";
						break;
					case Excel.XlSheetType.xlDialogSheet:
						sheetType = "xlDialogSheet";
						break;
					case Excel.XlSheetType.xlExcel4IntlMacroSheet:
						sheetType = "xlExcel4IntlMacroSheet";
						break;
					case Excel.XlSheetType.xlExcel4MacroSheet:
						sheetType = "xlExcel4IntlMacroSheet";
						break;
				}
			}
		}
	}
	catch (Exception)
	{

		throw;
	}
}

Above code helps you finding Macro sheets in a workbook, however, if you run a foreach loop for Woksheets it will auto eliminate these objects.

Next>>Validation List in Excel C# VSTO

Leave a Reply

Your email address will not be published.