Create New Workbook in Excel VBA

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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

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.
Create new 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
LIMITED TIME OFFER
By |January 13th, 2013|Excel VBA|2 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

2 Comments

  1. ow.ly March 8, 2014 at 1:10 AM - Reply

    Thank you for sharing your info. I really appreciate your efforts and I am waiting for your next write ups thank you once again.

  2. Ed March 3, 2016 at 8:51 AM - Reply

    Can you show how to use the ‘template’ option when you .Add

Leave A Comment


Related pages


excel vba internet explorervba create new worksheetgenpact interview questions for freshersdcl sqlshortcut key for insert rowopen recordset vbaunprotect excel forgot passwordexcel copy hyperlinkexcel vba removedelete unwanted rows in excelunhide pivot tabledelete both duplicates in exceltutorial vlookup excelconcatenation excelexcel tutorial for beginnershow to write a if statement in excelexcel vba print formexcel duplicate entriesexcel how to insert a rowexcel vba with rangeexcel vba arrayscreating a stock chart in excelvba excel recordsettimevalue vbavba tutorial for beginnersdelete columns excelupdate sql from excelsql workbooknested if in vbalessons learned template powerpointmerge and center on excelexcel highlight duplicates in columnmicrosoft excel unprotect sheetproject budget excel templateddl dml commandsvba excel select worksheetexcel 2007 vbaexcel function offsetexcel vba to close workbookhow to convert column to row in exceldefining an array in vbaremove duplicate entries from excelms excel macro programmingproject management spreadsheet templatestick mark in excel 2007excel vba pivotusing multiple if statements in excelvba excel xmlif iserror functionvlookup multiple conditionsshortcut key in excelrange cell vbawrite sql query in excelvba tutorial pdfwhat is the use of vlookup function in excelexcel len functionunhide column a in excel 2013excel vba do while loopvlookup explainedcut and paste worksheetpassword protect workbookhow to merge to columns in excelexcel vba codecase function in vbaexcel macro to open files in a folderdeveloper tab in excelvba hourexcel formula to calculate rangeuse of concatenate in excelexcel formulas to find duplicatesexcel saveas fileformatconsolidate multiple worksheetsvlookup example in hindimerge two columns in excelvba excel isemptyformula match excelvba declare variablewhy we use hlookup in excelexcel vba iserror