Menu
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.
567
Keyboard shortcut To quickly create a new, blank workbook, you can also press CTRL+N.
For the first scenario described above, the way to recover your never-before-saved file is to, first, open Microsoft Excel. Then go to the File tab on your Ribbon and click Open. Click Recent and then scroll to the bottom of the page and click Recover Unsaved Workbooks (see the screenshot below).
On the Home tab, in the Cells group, click the arrow next to Delete, and then click Delete Sheet. Tip: You can also right-click the sheet tab of a worksheet or a sheet tab of any selected worksheets that you want to delete, and then click Delete Sheet.
Click File > Excel Options > Advanced. Under General, in the At Startup, open all files in box, type the full path of the folder that you want to use as the alternate startup folder.
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'
Or you can:
Excel 2003 Tools>Options>General Tab and change the "Sheets in new workbook" to 1
Excel 2007 Office Button>Excel Options>Popular Section>When creating new workbooks...>Include this many sheets>1
26
Can't create one without any sheets (to the best of my knowledge), but you can create a workbook with a single sheet without messing around with the user's settings.
dim wb as Workbook
Set wb = xl.Workbooks.Add(xlWBATWorksheet)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
