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.

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.

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.