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.
- Press Alt+F11 to open VB Editor.
- Copy paste the below code.
- Ensure file with same name does not exists in path.
- 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.