Create a New Workbook in the Same Folder [Excel VBA]

Do you need to create a new workbook in the same folder where the original workbook is using automation? Find here how to do this easily using Excel VBA.

You might automate data collection, interpretation, and visualization in Excel using Excel VBA. However, two critical tasks here are to create the workbook and save it in the same directory. You might be doing this manually until today.

Wondering if you can also automate the process of creating and saving a workbook? Absolutely, yes! You can do this by writing a few lines of code in your existing VBA script.

Data analysts and scientists working at big IT companies have been doing this for many years. Today, I reveal the secrets of the Excel VBA create and save workbook processes below.

Reasons to Create a New Workbook In the Same Folder

You might wonder why you need to learn this Excel VBA trick. Nice question!

Most productive and functional Excel automation will require the following:

1. Remove all the repetitive tasks for which you don’t get paid. Creating and saving workbooks is such a task.
2. When automating Excel using third-party apps on a server, the app should be able to create a workbook on its own, enter the target data from the internet or any other source, and save the workbook in a specified location on the server.
3. In many research works, you may need to collect a specific set of data for an object over a period of time. For example, collecting weather data for a city for a few years to produce a weather pattern. Here, you can use automation to create and save new workbooks with preset specifications to save time.
4. You need to feed the Excel workbooks as a database or input file to another app where the file name should be of a specific configuration. Relying on the data entry operator to follow the naming convention could prove inefficient since human error is obvious in prolonged repetitive projects.

For the above arguments, an Excel VBA automation to create and save workbooks is the only reliable method. You also enjoy the following benefits by adapting to Excel VBA save workbook:

• All the Excel files of one project stay organized in a specific directory.
• If the location of the directory is a server, you can easily feed the address to these files into apps and software.
• It becomes easy to refer to worksheets, tables, and cells between these saved workbooks in the same folder.
• Collaborators can also easily access workbooks easily when stored in a specific folder.
• You avoid the risk of overwriting other workbooks with the same file name in other directories.

Now that you discovered the rationale behind creating and saving a new workbook in the same folder using Excel VBA, explore the codes and instructions to use those codes below:

Also read: How to Save Excel as PDF in Landscape

How to Create and Save a New Workbook in the Same Folder

This is a basic method to create a blank workbook in the same directory as the parent workbook.

I’ve also added the steps to create a clickable button so you don’t need to manually run the Excel VBA Macro each time you need to create a new workbook in the same folder. Follow these step-by-step guide:

1. Launch the Excel VBA Editor from Developer > Visual Basic.

2. On the toolbar, click Insert and choose Module.
3. Copy and paste this VBA script into the blank module you just created:

Sub CreateAndSaveWorkbookInSameFolder()
    Dim parentPath As String
    Dim newWorkbook As Workbook
    
    ' Get the path of the parent workbook
    parentPath = ThisWorkbook.Path
    
    ' Create a new workbook
    Set newWorkbook = Workbooks.Add
    
    ' Save the new workbook in the same folder as the parent workbook
    newWorkbook.SaveAs parentPath & "\" & "NewWorkbook.xlsx"
    
    ' Close the new workbook without saving changes (optional)
    newWorkbook.Close SaveChanges:=False
End Sub

4. Modify the file name for the new workbook by editing the "NewWorkbook.xlsx" code element. Don’t delete .xlsx, because that’s the file extension (file type) of the workbook you’re saving.
5. Once done, click the Save button.
6. Close the Excel VBA Editor.

7. Press Alt + F8 to launch the Macro dialog box.
8. Select the CreateAndSaveWorkbookInSameFolder macro and hit Run.
9. Excel will automatically create and close the new workbook in the same folder as the parent workbook.

You should find the new workbook in the directory of the parent workbook where you executed the above-mentioned VBA code.

You might want to add a button to run this macro on the parent workbook if you need to frequently create new workbooks from the parent workbook. Here’s how you can do this:

1. Go to the Developer tab on your workbook.
2. Click the Insert button inside the Controls commands block.
3. Choose the Button option from the Form Controls menu.

4. Draw the button anywhere on your workbook where you want to place the button.
5. As soon as you let go of the cursor after drawing the button, the Assign Macro dialog box pops up.
6. Select the CreateAndSaveWorkbookInSameFolder VBA macro from the list.
7. Click OK.

8. Now, right-click on the Button and select Edit Text on the context menu.

9. Give the Button a name that you and your colleagues could remember.

That’s it! Next time, if you need to create a new workbook in the same directory as the parent workbook, just click the button.

How to Create a New Workbook From a Template in Same Folder

Suppose, you don’t just want to create a new workbook automatically.

You also want that Excel VBA uses another workbook as a template, which could be the parent workbook as well, and create a new workbook in the same folder.

You can use the following Excel VBA script to accomplish this task:

Sub CreateWorkbookFromTemplate()
    Dim templatePath As String
    Dim templateWorkbook As Workbook
    Dim newWorkbook As Workbook
    Dim templateDirectory As String
    
    ' Prompt the user to select the template file
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select Template Workbook"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx, *.xls"
        .AllowMultiSelect = False
        
        If .Show = -1 Then
            templatePath = .SelectedItems(1)
        Else
            Exit Sub ' User canceled the file selection
        End If
    End With
    
    ' Get the directory of the template workbook
    templateDirectory = Left(templatePath, InStrRev(templatePath, "\") - 1)
    
    ' Open the template workbook
    Set templateWorkbook = Workbooks.Open(templatePath)
    
    ' Create a new workbook based on the template
    Set newWorkbook = Workbooks.Add
    templateWorkbook.Sheets.Copy Before:=newWorkbook.Sheets(1)
    
    ' Save the new workbook in the same directory as the template workbook
    newWorkbook.SaveAs templateDirectory & "\" & "NewWorkbook.xlsx"
    
    ' Close the template workbook without saving changes
    templateWorkbook.Close SaveChanges:=False
    
    ' Close the new workbook without saving changes (optional)
    newWorkbook.Close SaveChanges:=False
End Sub

Follow the steps mentioned earlier to add this code to the workbook using the Excel VBA Editor. Use the Macro dialog box or a Control Button to execute the script.

Once the code runs, it’ll show the Select Template Workbook dialog box. Use this to locate the template you’d like to use. You can also select the parent workbook.

Excel VBA will always save the new workbook in the same directory as the template workbook. If you wish to set a different workbook name, edit this code element: "NewWorkbook.xlsx".

Create a New Workbook By Replicating Original With Its Name

Suppose, you got a data entry workbook where you collect data regularly. Also, you need to create a separate workbook for each day. Not to mention, you need to keep all the workbooks in the same folder to better organize your project files.

Instead of asking the data entry operator to manually do all these, create an Excel VBA script and add it as a button to the parent workbook.

Therefore, the data entry operator can just open the parent workbook, click a button to replicate it, and save it with a suffix of the current date. Let’s explore the code below:

Sub CreateAndSaveWorkbookWithDate()
    Dim parentPath As String
    Dim parentName As String
    Dim newWorkbook As Workbook
    
    ' Get the path and name of the parent workbook
    parentPath = ThisWorkbook.Path & "\"
    parentName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1)
    
    ' Create a new workbook by copying the parent workbook
    Set newWorkbook = Workbooks.Add
    ThisWorkbook.Sheets.Copy Before:=newWorkbook.Sheets(1)
    
    ' Save the new workbook with today's date appended to the parent name
    newWorkbook.SaveAs parentPath & parentName & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
    
    ' Close the new workbook without saving changes (optional)
    newWorkbook.Close SaveChanges:=False
End Sub

Hit Alt + F11 to bring up the Excel VBA Editor and add the above script to a new Module (detailed steps explained earlier in this article). Save the code and close the Excel VBA Editor.

Run the code by pressing Alt + F8 and running the CreateAndSaveWorkbookWithDate macro.

You’ll find the replicated workbook in the same directory as the parent workbook. However, the new name will be Name of Parent Workbook_YYYY-MM-DD. Now, if you want to change the date format, edit this code element accordingly: Format(Date, "yyyy-mm-dd").

Conclusion

If you’ve read the article so far, you learned three different methods to create a new workbook in the same folder as the parent workbook using simple Excel VBA scripts. Firstly, you learned to create a new blank workbook from the parent and put that in the same directory.

Now, if you need to create a new workbook by using another workbook as the template, you can follow the second method.

Finally, when you need to replicate the parent workbook and add the current date to that as a suffix, check out the third method.

Try the method you like and write a comment below expressing your experience while reading and following the steps in this Excel tutorial.