[FAQ's: OD] How do I create a new workbook?

[RobDog888]

If you need to programmatically create a new blank workbook from within Excel itself you would use the Application.Workbooks.Add method. Now if you are automating Excel from another program language like VB 6 or a .NET language then you would create the application instance and add a new blank workbook.

From within the Excel VBA IDE environment you can place your code to create a new workbook within your Excel Application instance like shown below.

Excel 2003 VBA Code Example:

Option Explicit
'Behind "ThisWorkbook" class
Public Sub AddNewWorkbook()
    'Define a workbook variable in case you want to perform any modifications on the workbook when it is created
    Dim oWB As Excel.Workbook
    'Add it to the Workbooks collection and set our object variable equal to the newly added workbook
    'By referencing the "Application" object instance we are adding the workbook to the current 
    'runnng Excel instance (since we are in VBA).
    Set oWB = Application.Workbooks.Add
    'Perform and mods to the new workbook.
    '...
    'Close the new workbook, Save it and clean up resources
    oWB.Close SaveChanges:=True, FileName:="C:\NewWorkbook.xls"
    Set oWB = Nothing
End Sub

Then to call the procedure from any sheet or module ....

Option Explicit
'Behind Sheet1
Sub Button1_Click()
    AddNewWorkbook
End Sub

[RobDog888]

VB 6 and Excel 2003 Early Binding Code Example:

Option Explicit
'Add a reference to MS Excel xx.0 Object Library
Private Sub Command1_Click()
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    
    'Create an Excel instance.
    Set oApp = New Excel.Application
 
    'Create a new workbook
    Set oWB = oApp.Workbooks.Add
 
    'Perform and mods to the new workbook.
    '...
 
    'Close the new workbook, Save it and clean up resources
    oWB.Close SaveChanges:=True, FileName:="C:\NewWorkbook.xls"
    Set oWB = Nothing
    'Quit/Close the Excel application
    oApp.Quit
    Set oApp = Nothing
End Sub

VB 6 and Excel 2003 Late Binding Code Example:

Option Explicit
 
Private Sub Command1_Click()
    Dim oApp As Object
    Dim oWB As Object
    
    'Create an Excel instance.
    Set oApp = CreateObject("Excel.Application")
 
    'Create a new workbook
    Set oWB = oApp.Workbooks.Add
 
    'Perform and mods to the new workbook.
    '...
 
    'Close the new workbook, Save it and clean up resources
    oWB.Close SaveChanges:=True, FileName:="C:\NewWorkbook.xls"
    Set oWB = Nothing
    'Quit/Close the Excel application
    oApp.Quit
    Set oApp = Nothing
End Sub

[RobDog888]

VB.NET 2003 And Excel 2003 Early Binding Code Example:

Option Explicit On 
Option Strict On
'Copyright © 2006 by RobDog888 (VB/Office Guru™). All Rights reserved.
'
'              You may not reproduce or publish this 
'              code on any web site, online service, 
'              or distribute as source on any media 
'              without express permission.
 
'Add a reference to Microsoft Excel xx.0 Object Library
Imports Microsoft.Office.Interop
 
Public Class Form1
 
    Inherits System.Windows.Forms.Form
 
    "Windows Form Designer generated code"
 
    Private oApp As Excel.Application
    Private oWB As Excel.Workbook
 
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub
 
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) _
    Handles MyBase.Closing
        'CHECK IF THE WORKBOOK WAS SAVED FROM THE USER IN EXCEL
        If TypeName(oWB) <> "Nothing" Then
            If oWB.Saved = False Then
                Dim dlgSaveFile As New SaveFileDialog
                With dlgSaveFile
                    .CheckFileExists = True
                    .CheckPathExists = True
                    .Filter = "Excel Files Only (*.xls)|*.xls"
                    .FilterIndex = 0
                    .InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal)
                    .CreatePrompt = True
                    .FileName = "Book1.xls"
                    .OverwritePrompt = True
                    .ValidateNames = True
                    .Title = "Select an Excel Workbook File"
                    If .ShowDialog = DialogResult.OK Then
                        oWB.SaveAs(FileName:=.FileName)
                        oWB.Saved = True
                    Else
                        'CANCECL WAS PRESSED SO PROMPT TO CANCEL THE CLOSE TOO
                        If MessageBox.Show("Do you want to Quit?", "Excel New Workbook", _
                        MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = DialogResult.OK Then
                            'FALL THROUGH AND QUIT SILENTLY
                        Else
                            'CANCEL THE QUIT
                            e.Cancel = True
                            Exit Sub
                        End If
                    End If
                End With
            End If
            'CLEAR OUR RESOURCES
            oWB.Close(SaveChanges:=False)
            oWB = Nothing
        End If
        oApp.Quit()
        oApp = Nothing
        Application.Exit()
    End Sub
 
    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        'MAKE IT VISIBLE
        oApp.Visible = True
        'CREATE A NEW WORKBOOK BY ADDING IT TO THIS EXCEL APPLICATION INSTANCE
        oWB = oApp.Workbooks.Add()
        'NEEDED TO FOOL EXCEL IN OUR CLOSING PROCEDURE
        oWB.Saved = False
    End Sub
 
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'CREATE THE NEW EXCEL APPLICATION INSTANCE
        oApp = New Excel.Application
        'MAKE IT INVISIBLE
        oApp.Visible = False
    End Sub
 
End Class