Home

Workbooks

 

Workbooks Fundamentals

 

Introduction

A document in Microsoft Excel is called a workbook. When you start Microsoft Excel, it immediately creates a document, a workbook, for you. You can start working on it and, eventually, you can save it. You are then said to save the workbook. On the other hand, if you have an existing workbook somewhere in the computer from a message sent to you, you can open it as a document.

 

Practical LearningPractical Learning: Introducing Workbooks

  1. Start Microsoft Excel
  2. To close the current document, click the system close below the first one
     

Referring to a Workbook

In the VBA language, a workbook is an object that belongs to a collection called Workbooks. Each workbook of the Workbooks collection is an object of type Workbook, which is a class.

As seen in the previous lesson with regards to collections, each workbook of the Workbooks collection can be identified using the Item property. To programmatically refer to a workbook, access the Item property and pass either the index or the file name of the workbook to it.

After refering to a workbook, if you want to perform an action on it, you must get a reference to it. To do this, declare a Workbook variable and assign the calling Item() to it. This would be done as follows:

Private Sub cmdSelectWorkbook_Click()
    Dim SchoolRecords As Workbook
    
    Set SchoolRecords = Workbooks.Item(2)
End Sub

Creating Workbooks

 

Visually Creating a Workbook

As mentioned already, when it starts, Microsoft Excel creates a default blank workbook for you. You can use that workbook as you see fit. As opposed to the default blank workbook, Microsoft Excel provides many designed and ready-to-use workbooks with complete functionality you can use.

Instead of using an existing working or while you are working on another workbook, at any time, you can create a new workbook.

To visually create a new workbook based on the templates provided by Microsoft Excel, click the Office Button and click New. This would display the New Workbook dialog box. In the left frame, under Templates, you can click a category. In the middle frame, click one of the button to see a preview in the right frame:

New Workbook

If you see a template you like, click it and click Create. If none of the templates suits you and if you are connected to the Internet, in the left frame, under Microsoft Office Online, click a category and select a template in the middle frame. Then click Download. You can also check for new files on the Microsoft Office web site.

Practical LearningPractical Learning: Creating Workbooks

  1. To create a workbook based on a template, click the Office Button
  2. In the left frame of the New Workbook dialog box, click Installed Templates
  3. In the middle frame, click Blood Pressure Tracker
  4. Click Create
  5. To add another workbook based on a template, click the Office Button
  6. In the left frame of the New Workbook dialog box, click Installed Templates
  7. In the middle frame, click Time Card and click Create
  8. To add one more workbook from on a template, click the Office Button
  9. In the left frame of the New Workbook dialog box, click Installed Templates
  10. In the middle frame, click Expense Report and click Create

Programmatically Creating a Workbook

As mentioned already, a workbook is an object of type Workbook and it is part of the Workbooks collection. To support the ability to create a new workbook, the Workbooks collection is equipped with a method named Add. Its syntax is:

Workbooks.Add(Template) As Workbook

You start with the Workbooks class, a period, and the Add method. This method takes only one argument but the argument is optional. This means that you can call the method without an argument and without parentheses. Here is an example:

Private Sub cmdNewWorkbook_Click()
    Workbooks.Add
End Sub

When the method is called like this, a new workbook would be created and presented to you. After creating a workbook, you may want to change some of its characteristics. To prepare for this, notice that the Add() method returns a Workbook object. Therefore, when creating a workbook, get a reference to it. To do this, assign the called method to a Workbook variable. Here is an example:

Private Sub cmdNewWorkbook_Click()
    Dim SchoolRecords As Workbook
    
    Set SchoolRecords = Workbooks.Add
End Sub

After doing this, you can then use the new variable to change the properties of the workbook.

Saving or Opening a Workbook

 

Saving a Workbook

After creating a workbook or after working on one, you may want to save it. To visually save a workbook, you can click the Office Button and click Save You can also press Ctrl + S. If the document was saved already, it would be saved behind the scenes without your doing anything else.

To support the ability to programmatically save a workbook, the Workbook class is equipped with a method named Save. Its syntax is:

Workbook.Save()

As you can see, this method takes no argument. If you click the Office Button and click Save or if you call the Workbook.Save() method on a work that was not saved yet, you would be prompted to provide a name to the workbook.

To save a workbook to a different location, you can click the Office Button, position the mouse on Save As and select from the presented options. You can also press F12. To assist you with programmatically saving a workbook, the Workbook class is equipped with a method named SaveAs. Its syntax is:

Workbook.SaveAs(FileName,
		  FileFormat,
		  Password,
		  WriteResPassword,
		  ReadOnlyRecommended,
		  CreateBackup,
		  AccessMode,
		  ConflictResolution,
		  AddToMru,
		  TextCodepage,
		  TextVisualLayout,
		  Local)

The first argument is the only required one. It holds the name or path to the file. Therefore, you can provide only a name of the file with extension when you call it. Here is an example:

Private Sub cmdNewWorkbook_Click()
    Dim SchoolRecords As Workbook
    
    Set SchoolRecords = Workbooks.Add
    
    SchoolRecords.SaveAs "SchoolRecords.xlsx"
End Sub

If you provide only the name of a file when calling this method, the new workbook would be saved in the current directory or in My Documents. If you want, an alternative is to provide a complete path to the file.

Opening a Workbook

If you have a workbook in your computer or one that was sent to you, you can open it. To visually open a workbook, you can click the Office Button and click Open. You can also press Ctrl + O. This would display the Open dialog box where you can locate the file and click Open. If a workbook was sent to you by email or otherwise, you can double-click. This would automatically lauch Microsoft Excel and display the document.

Microsoft Excel is a multiple document interface (MDI) application. This means that you can open many workbooks at the same time and be limited only by the memory on your computer. For this reason, thhe ability to programmatically open a workbook is handled by the Workbooks collection. To support this, the Workbooks class is equipped with a method named Open. Its syntax is:

Workbooks.Open(FileName,
	       UpdateLinks,
	       ReadOnly,
	       Format,
	       Password,
	       WriteResPassword,
	       IgnoreReadOnlyRecommended,
	       Origin,
	       Delimiter,
	       Editable,
	       Notify,
	       Converter,
	       AddToMru,
	       Local,
	       CorruptLoad)

FileName is the only required argument. When calling this method, you must provide the name of the file or its path. This means that you can provide a file name with its extension. Here is an example:

Private Sub cmdOpenWorkbook_Click()
    Workbooks.Open "SchoolRecords.xlsx"
End Sub

If you provide only the name of a file, Microsoft Excel would look for for the file in the current directory or in My Documents. If Microsoft Excel cannot file the file, you would receive an error:

Error

As you can imagine, a better alternative is to provide a complete path to the file.

Managing Workbooks

 

Microsoft Excel as an MDI

As mentioned already, Microsoft Excel is a multiple document interface (MDI). This means that the application allows you to switch from one workbook to another, or be able to display all of them sharing the same screen.

If you create or open many workbooks and while you are working on them, each is represented on the taskbar by a button. You can click the button of the desired workbook on the taskbar to access it. As an alternative, on the Ribbon, you can click View. In the Window section, click Switch Windows and click the desired document. The workbook you are currently using would have a check mark on it:

When many workbooks have been opened in, to display many of them, on the Ribbon, click View. In the Window section, click Arrange All. This would display the Arrange Window dialog box. From there you can select one of the radio buttons:

Arrange Windows

  • Tiled: The workbooks would display side by side:

Tiled

  • Horizontal: Each workbook would display horizontally

Horizontally

  • Vertically: The workbooks would display side by side:

  • Cascade: The workbooks would be presented one on top of the other:

To access a workbook:

  • You can click its title bar
  • On the Ribbon, click View. In the Window section, click Switch Windows, and select its name from the list

To support the ability the programmatically accept a workbook, the Workbook class is equipped with a method named Activate. Its syntax is:

Workbook.Activate()

This method takes no argument. Therefore, to call it, you can get a reference to the workbook you want to access, then call the Activate() method. Here is an example:

Private Sub cmdSelectWorkbook_Click()
    Dim SchoolRecords As Workbook
    
    Set SchoolRecords = Workbooks.Item(2)
    SchoolRecords.Activate
End Sub

You can also do this with less code by applying the index directly to the Workbooks collection. Here is an example:

Private Sub cmdSelectWorkbook_Click()
    Workbooks(2).Activate
End Sub

Practical LearningPractical Learning: Working With Many Workbooks

  1. To access one of the workbooks, on the taskbar, click BloodPressureTracker1
  2. To access another workbook, on the Ribbon, click View. In the Window section, click Switch Windows, and click TimeCard1 from the list

Viewing Many Workbooks

If you create or open many workbooks and while you are working on them, each is represented on the taskbar by a button. You can click the button of the desired workbook on the taskbar to access it. As an alternative, on the Ribbon, you can click View. In the Window section, click Switch Windows and click the desired document. The workbook you are currently using would have a check mark on it:

To programmatically refer to a workbook, access the Item property and pass either the index or the file name of the workbook to it. Here is an example:

Private Sub cmdSelectWorkbook_Click()
    Workbooks.Item (2)
End Sub

After refering to a workbook, if you want to perform an action on it, you must get a reference to it. To do this, declare a Workbook variable and assign the calling Item() to it. This would be done as follows:

Private Sub cmdSelectWorkbook_Click()
    Dim SchoolRecords As Workbook
    
    Set SchoolRecords = Workbooks.Item(2)
End Sub

Viewing Workbooks Side-By-Side

One of the most valuable features of Microsoft Excel views is that you can juxtapose two or more workbooks to share the same screen. After creating or opening at least two workbooks, to let them share the screen allocated to Microsoft Excel, on the Ribbon, click View. In the Window section, click View Side by Side. This would open the Compare Side by Side dialog box. From there, click the workbook that will share the screen with the current workbook:

After making the selection, click OK. Each workbook would be displayed each horizontally while they are sharing the work area of Microsoft Excel. Each workbook would have a title bar on its top, the vertical and scroll bars:

To access a workbook:

  • You can click its title bar
  • On the Ribbon, click View. In the Window section, click Switch Windows, and select its name from the list

To close a workbook, you can click its system Close button.

Practical LearningPractical Learning: Viewing Workbooks Side-By-Side

  1. On the Ribbon, click View if necessary.
    To view the workbooks side by side, in the Window section, click View Side by Side
  2. In the Compare Side by Side dialog box, click the ExpenseReport1 and click OK
  3. Close each workbook without closing Microsoft Excel
  4. When asked whether you want to save, click No
 

Previous Copyright 2008-2009, yevol.com Next