CODEWITHSUNDEEP
c#excelvbaexcel-interopexcel.application
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10297

Why am I getting a superfluous sheet even though I'm explicitly setting sheet count to 1?

I want to avoid generated spreadsheets having empty/superfluous sheets named "Sheet1" and such. I thought I could do that by specifying how many sheets a workbook should have this way:

_xlApp = new Excel.Application { SheetsInNewWorkbook = 1 };

...But I'm still getting an unwanted "Sheet1" in addition to the sheet I create. Here is the pertinent code:

using Excel = Microsoft.Office.Interop.Excel;
. . .
private Excel.Application _xlApp;
private Excel.Workbook _xlBook;
private Excel.Sheets _xlSheets;
private Excel.Worksheet _xlSheet;
. . .
private void InitializeSheet()
{
    _xlApp = new Excel.Application { SheetsInNewWorkbook = 1 };
    _xlBook = _xlApp.Workbooks.Add(Type.Missing);
    _xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    _xlSheets = _xlBook.Worksheets;
    _xlSheet = (Excel.Worksheet)_xlSheets.Item[1];
    _xlSheet.Name = String.Format("Price Compliance {0} {1}", _month, _year);
}

So since setting SheetsInNewWorkbook to 1 in the Excel.Application instance doesn't do the trick, what do I need to do to prevent these tramp sheets from showing up?

Upvotes: 1

Views: 284

Answers (1)

test
test

Reputation: 2639

The answer to your question can be found in the documentation of the Template parameter on the Workbook.Add method.

[...] If this argument is omitted, Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property).

Your code is omitting it, therefore it is creating a single Worksheet for you (since you've set SheetsInNewWorkbook to 1.

That property is also constrained to be between 1 and 255, so you aren't able to add a work book without a sheet (unless you use a file template).

Also from the Template parameter documentation:

If this argument is a constant, the new workbook contains a single sheet of the specified type. Can be one of the following Microsoft.Office.Interop.Excel.XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorksheet.

So an alternative way to do this is:

_xlApp = new Excel.Application();
_xlBook = _xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
_xlSheets = _xlBook.Worksheets;
_xlSheet = (Excel.Worksheet)_xlSheets.Item[1];
_xlSheet.Name = String.Format("Price Compliance {0} {1}", _month, _year);

Which simply renames the single created sheet.

Upvotes: 3

Related Questions