add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Handling a workbook includes opening, saving and/or closing. These are actions that you routinely perform in the course of your work with Excel.
The regular performance of these routine actions justifies learning shortcuts, as it will shorten the amount of time you spend on them. This is the goal of this chapter.
Opening a New Workbook
For your convenience, Excel opens a new workbook every time you start the program. The name of the file it opens is Book1.xls. This new workbook is an exact copy of the Template file named Book.xlt.
Additionally, during your ongoing work in Excel, you can open a new workbook or an additional workbook by using the keyboard shortcut, Ctrl+N or by clicking the New icon in the Standard toolbar.
You can also open a new workbook by opening the File menu and selecting New.
Opening a Previously Saved Workbook
You can open an existing (previously saved) workbook in a number of ways. The following is the standard method for opening a workbook.
1. Press Ctrl+O (the letter O, not the number zero), or from the File menu, select Open.
2. Locate the file in the folder on your hard drive and open it.
This procedure is slow and inefficient. You are better off using quicker methods.
Opening a workbook from the recently used file list
Excel allows you to save a list of up to nine of the files you have used most recently. From the File menu, view the list of most recently used files.
To open a workbook, press Alt+F and the workbook number as it appears on the list.
Be sure that the recently used file list is set to the maximum of nine files. From the Tools menu, select Options, and then select the General tab. In the Recently used file list box, change the number to 9.
Saving a Workbook
By default, Excel automatically saves all new files in C:\My Documents. From the Tools menu, select Options, and then select the General tab. Change the default by typing a new complete path to the desired folder in the Default file location box. New files will now automatically be saved to that location.
Saving a workbook using Save As
Press F12 or from the File menu, select Save As. To save the file in an existing folder, navigate in the Save in box to the folder where you want to store the file on your hard drive. Now save the file with a new name.
If you want to save the file in a new folder, navigate in the Save in box to the location on the hard drive where you want to open the new folder. Now, click the Create New Folder icon. In the Name box, type the name of the folder, and then click OK. In the File name box, type a name for the file, and click Save.
Inserting the path where the workbook is saved to the title bar or the status bar
When you open a workbook from some directory on the network, you do not always remember the path or name of the folder where the file is saved. Insert the full path to the title bar (the blue strip above the Excel menu bar), or to the status bar.
See the figure on next page –
A workbook called Monthly Reports is saved in the Reports folder on the G: drive.
1. In Press Alt+F11 to open VBE.
2. VBAProject, double-click the module called ThisWorkbook to open it.
3. Above the module sheet are two drop-down lists. Open the left-hand list(General) and select Workbook.
4. A macro called Workbook_Open appears. In the macro, type the code as it appears in the figure below.
Name of the title bar – Caption. Name of the status bar – Status Bar.
5. From the right-hand drop-down list, select the event (macro) Workbook_BeforeClose. In the macro, type the code as it appears in the figure below.
6. Save the file, close it, and then reopen it to check whether the file name and path appear in the blue title bar.
Explanation
1. When the workbook is opened, a Workbook_Open macro is activated. This macro performs the directions written in it.
2. When the workbook is closed, the Workbook_BeforeClose macro is activated. This macro deletes the path, and the title bar and status bar return to their former condition. It is very important to delete the path, because otherwise, the next workbook displays the old path.
3. Insert the macro into every workbook that you need to view the path.
Saving Workbooks in a Customized Workspace
Save Workspace allows you to create a shortcut that lets you open a group of workbooks in one step and eliminate the need to open them each individually.
From the Window menu, check the list of open files, and be sure that the list includes only the files want to group together as a package. Close all other files that may be open.
From the File menu, select Save Workspace, and navigate in the Save in box to the folder where you want to store the new file. The file name will end with the extension .xlw. The workspace file does not contain the workbooks themselves, but rather points to the files saved as a group.
Opening workbooks saved in a workspace
You have started Excel, and now you want to open all of the files you saved using Save Workspace. From the File menu, select Open and navigate to the folder where you saved the workspace file. Select the file, and click Open. All of the files that were saved as a group will now open one after the other.
Tip – Save in Favorites
Save the workspace file in your Favorites folder. This will be described in detail below.
Saving shortcuts to files/folders in Favorites
The Windows operating system lets you save important shortcuts to files, folders or URLs (Internet addresses) in a folder called Favorites. There are many of advantages to using this folder. First, create a shortcut. Then, from the File menu, select Open, and click the Favorites icon in the Open dialog box. Now, from the list of shortcuts, select the file or folder you want to open, and click Open.
Adding a shortcut to the Favorites folder
From the File menu, select Save As. In the Save As dialog box, select the workbook or folder for which you want to create a shortcut. Select Tools and then Add to Favorites.
Opening workbooks from a list of hyperlinks
Prepare a list of files in a worksheet along with a list of hyperlinks. Clicking on any of the shortcuts that were created through the hyperlinks (see column B in the figure below) will open the workbook. The figure below displays a list of files containing Excel tips.
Inserting a hyperlink
Select a cell in which you want to insert a hyperlink. Excel offers several shortcuts for opening the Insert Hyperlink dialog box.
1. Press Ctrl+K; or select the Insert Hyperlink icon from the Standard toolbar; or from the Insert menu, select Hyperlink.
2. In the Insert Hyperlink dialog box, select the workbook you want to link to, and click OK.
Closing a workbook / workbooks and Excel
To close a workbook, press Ctrl+F4.
To close all workbooks without exiting Excel, press Shift, and from the File menu, select Close All.
To exit Excel, press Alt+F4.