August 22, 2008

Adding a new workbook

Filed under: VBA — fatrunner @ 2:15 am
Tags: ,

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


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.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: