[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