Adding a new workbook

Adding a new workbook to Excel via Visual Basic can be done with one simple command:

Workbooks.Add

According to Excel’s Visual Basic Help file, the best method for doing so is to assign the workbook to an object variable. Doing so allows you to manipulate various properties of the workbook. Their example looks like this:

Sub AddNew()
  Set  NewBook = Workbooks.Add
    With NewBook
      .Title = "All  Sales"
      .Subject = "Sales"
      .SaveAs  Filename:="Allsales.xls"
    End With
End Sub

What this code does in English is the following:

Sub AddNew()

Creates a new subroutine called “AddNew”

Set NewBook =  Workbooks.Add

Creates a variable named NewBook and makes it equal to the VBA code for adding a workbook.  Until the the variable is changed, any time reference is made to it, it will add a new workbook.

With NewBook
  .Title = "All Sales"
  .Subject = "Sales"
  .SaveAs  Filename:="Allsales.xls"
End With

This chunk of code actually creates the new workbook and tacks on some attributes to it in the process with the ‘With’ statement.  The ‘Title=’ portion adds a title to it which you can see if in Excel you go to File –> Properties.  Excel does not normally put anything here by default and it can be different than the filename of the spreadsheet. The ‘Subject=” portion adds a subject to it in the same way that the Title did above and can be found the same way via File –> Properties. I’ve never used these properties before in my daily use of Excel and at this point I’m not sure what purpose they serve, but that may be a later topic of discussion.

The ‘SaveAs Filename:=” line actually saves the file with the filename you specify within the quotation marks.  As this code is written (with just a filename) it will save the spreadsheet to your My Documents folder. If you wanted to save it to another location you would include the entire path in the quotation marks such as “C:\Docuements and Settings\username\My Documents\Sales\Allsales.xls”

And then of course the last line is

End Sub

which stops the subroutine.

Well…there you go. You’ve just added a new workbook.