Excel VBA Create New Workbook – With Name – Copy Save Close

Create a New Workbook with VBA

Quick trick with Outlook, Word,Excel VBA create new workbook while run-time on the go.

Note: To create a Excel file from Outlook or Word VBA, include reference “Microsoft Excel Object Library’ from VB Editor Menu Tools->References

Once the above reference is made, then the below code will do the rest.

Excel VBA Create New Workbook with Name

From Outlook, Excel or Word follow these steps.

1. Press Alt+F11 to open VB Editor.
2. Copy paste the below code.
3. Ensure file with same name does not exists in path.
4. Press F5 to create new Excel workbook.

Private Sub VBA_Create_New_Workbook()

    'Add Reference to Microsoft Excel Object Library before this code
    'Create New Workbook
    Workbooks.Add
    
    'Save newly created workbook
    ActiveWorkbook.SaveAs "D:\New_Workbook_From_VBA.xlsx"
    
    'Workbook Close vba
    ActiveWorkbook.Close
End Sub

If the workbook is not closed, then It will be left opened by VBA. So, if you try to open the newly created workbook, then system will notify that workbook is already opened.

Also, if the above code is execute from with in Excel VB Editor, then reference to ‘Microsoft Excel Object Library’ is not required.

VBA to Create New Workbook With Object Reference

Above code just creates a Excel file & save it to a location. If the created workbook has to be referenced with the code, then use the below code.

Private Sub Create_New_Workbook_with_Object()
    'Add Reference to Microsoft Excel Object Library before this code
    Dim objNewExcel As Workbook
    
    'Create New Workbook with Object
    Set objNewExcel = Workbooks.Add
    
    'VBA Create New Workbook and Copy Data
    objNewExcel.Sheets(1).Cells(1, 1) = "Copy To New Workbook"
    
    'Save newly created workbook using Object
    objNewExcel.SaveAs "D:\New_Workbook_From_VBA_Obj.xlsx"
    
    'Workbook Close VBA
    objNewExcel.Save
    objNewExcel.Close
End Sub

This code will assign the newly created workbook to a object. So, this object can be used to write, copy or read content to & from the newly created Excel file.

Refer this page from Microsoft for a more info.

This ends this tutorial. With this tutorial we learned (1). How to Create New Workbook from VBA (2). How to Save newly created Workbook (3). How to copy data to new Excel file using VBA Code.

Always remember to use workbook close vba code, before exiting from any of these macro code.