CODEWITHSUNDEEP
excelvba
hawbsl
hawbsl

Reputation: 16033

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.

Upvotes: 9

Views: 20581

Answers (4)

marg
marg

Reputation: 2827

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'

Upvotes: 11

Gendrian Borromeo
Gendrian Borromeo

Reputation: 11

Sub DeleteSheets()
Dim DeleteSheet As Variant
Dim ws              As Worksheet
DeleteSheet = Array("Sheet1", "Sheet2", "Sheet3")

Application.DisplayAlerts = False

For Each ws In Worksheets

   If IsInArray(ws.Name, DeleteSheet) Then ws.Delete

Next

End Sub


Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
Dim i As Integer
Dim Ret As Boolean
Ret = False
For i = LBound(arr) To UBound(arr)
    If VBA.UCase(stringToBeFound) = VBA.UCase(arr(i)) Then
       Ret = True
       Exit For
    End If
Next i
IsInArray = Ret
End Function

Upvotes: 1

RubberDuck
RubberDuck

Reputation: 12768

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)

Upvotes: 5

Macness
Macness

Reputation: 1226

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

Upvotes: 3

Related Questions