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.
123456789101112Private Sub VBA_Create_New_Workbook()'Add Reference to Microsoft Excel Object Library before this code'Create New WorkbookWorkbooks.Add'Save newly created workbookActiveWorkbook.SaveAs "D:\New_Workbook_From_VBA.xlsx"'Workbook Close vbaActiveWorkbook.CloseEnd Sub
- 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.
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.1234567891011121314151617Private Sub Create_New_Workbook_with_Object()'Add Reference to Microsoft Excel Object Library before this codeDim objNewExcel As Workbook'Create New Workbook with ObjectSet objNewExcel = Workbooks.Add'VBA Create New Workbook and Copy DataobjNewExcel.Sheets(1).Cells(1, 1) = "Copy To New Workbook"'Save newly created workbook using ObjectobjNewExcel.SaveAs "D:\New_Workbook_From_VBA_Obj.xlsx"'Workbook Close VBAobjNewExcel.SaveobjNewExcel.CloseEnd 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.
Excel VBA Create New Workbook – With Name – Copy Save Close