excelvba

Start empty Excel workbook without any worksheets

Creating a new Excel workbook as in:

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Set xl = New Excel.Application
xl.Visible = False
Set wb = xl.Workbooks.Add

Is there an easy way to stop Excel automatically creating Sheet1, Sheet2, Sheet3?

I can always delete these unwanted sheets afterwards but that feels like a clunky solution.

Solution 
  • xl.SheetsInNewWorkbook = 1

    More Information on MSDN (Scroll down to Add method as it applies to the Workbooks object.)

    Full Code:

    Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim restoreSheetsInNewWorkbook As Long

Set xl =  New Excel.Application
restoreSheetsInNewWorkbook = xl.SheetsInNewWorkbook
xl.SheetsInNewWorkbook = 1

Set wb = xl.Workbooks.Add
xl.SheetsInNewWorkbook = restoreSheetsInNewWorkbook 'or just set it to 3'