Create New Workbook in Excel VBA

Description:

Sometimes we may want to open or create new workbook using VBA.You can set the newly created workbook to an object, so that it is easy to refer to your workbook to do further tasks.

Solution(s):

The following Macros will allow to open or create new workbook using Add method.

Create New Workbook in Excel VBA – Example Cases:

• Create new workbook
• Create an Object for newly created workbook

Create new workbook

You can use the following code to create new Workbook and save in the C drive using Add method.

Code:

Sub AddNewWorkbook1()

	'Adding New Workbook
	Workbooks.Add

	'Saving the Workbook
	ActiveWorkbook.SaveAs "C:WorkbookName.xls"

	'OR
	ActiveWorkbook.SaveAs Filename:="C:WorkbookName1.xls"

End Sub

Explanation:

1. Workbooks.Add method will add a new workbook
2. ActiveWorkbook.SaveAs method will save the active workbook to a specific location with specified File name

Output:

You should see newly opened workbook along with existing workbook.

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Insert a new module from Insert menu
4. Copy the above code and Paste in the code window
5. Press F5 to see the output
6. You should see newly opened workbook as shown above
7. Save the file as macro enabled workbook

Create an object for newly created workbook

You can set the newly created workbook to an object, so that it is easy to refer to your workbook to do further tasks.

Code:

sub AddNewWorkbook2()

	Dim wkb as Workbook

	'Adding New Workbook
	Set wkb = Workbooks.Add

	'Saving the Workbook
	wkb.SaveAs "C:WorkbookName.xls"
	'OR

	wkb.SaveAs Filename:="C:WorkbookName1.xls"</span></code>

End Sub

Output:

You should see newly opened workbook along with existing workbook.

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Insert a new module from Insert menu
4. Copy the above code and Paste in the code window
5. Press F5 to see the output
6. You should see newly opened workbook as shown above
7. Save the file as macro enabled workbook