Create a new Excel Workbook with VBA EN

Hi Everybody, today I'm going to show you how to create a new workbook with VBA; You can create a workbook from Excel or PowerPoint or another program of Office, you just need to have the reference.

First of all If we are working on Excel we don't need any reference because the references are set automatically but if you are not in Excel you need to add the references as following:

• Click on Tools->References

• Find ans Select the library: Microsoft Excel 14.0 Object Library

• Click on Ok

Now let's create the new workbook, I'm going to create it from Word and well before I start I should have my programming environment, If you don´t know how to get to that just see the previous posts, this could help you. Well now let's start:

• Right click on This Document->Insert->Module

• Now Write the following code:

Sub createNewWorkbook()

    Dim newBook As Workbook

    Dim newSheet As Worksheet

    Dim name As String

    name = InputBox("Enter the Name of the new Workbook") & ".xls"

    

    Set newBook = Workbooks.Add

    With newBook

        .Title = name

        .SaveAs FileName:=ActiveDocument.Path & "\" & name

    End With

    

    Set newSheet = newBook.Sheets.Add

    

    newSheet.Cells(1, 1).Value = "You have created a new Workbook"

    

    newBook.Close (True)

End Sub

Well now We just need to test it, just run the macro: Click on Run on your Programming environment or on Your Active Document Click on Developer->Macros->Choose the Macro and Click on Run.

As you can see in the folder where my Active document is I have this:

When I run the Macro, the Macro will immediately show a window asking me for the name of the file, just enter the name and Click on Ok:

And now you have your new File, When you try to open the file if a warning message like this appear, just click on Yes, and the result is the following

Well that's it for now, I hope this could help you and If you have doubts or comments please leave them on the respective area, thanks and see you dudes ^_^/

Here is the link of the project on Github