Page 167 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

by trying to save the file and clicking the Save In down arrow to find the folder in which your
workbook is stored, using the Immediate Window means you don’t have to leave the Visual
Chapter 7
Basic Editor, which should help you keep your work flowing.

Note The full name of a file includes a path, but the path will be blank if the file hasn’t
been saved yet.

Earlier in this chapter you used the Save and SaveAs methods of the ThisWorkbook property
to save the files you change using VBA. Excel keeps track of whether a workbook has unsaved
changes, which is helpful if you want to check the workbook and save it every time you run a
procedure that affects the workbook’s contents, or if you want to close a workbook without
saving any of the changes you’ve made (such as when you run a series of formatting routines
to highlight different aspects of your data but want everything reset when you’re done). The
following code fragment would have Excel close the active workbook without saving any
unsaved changes:

ThisWorkbook.Saved = True
ThisWorkbook.Close

Another aspect of saving values in a workbook relates to how extensively you use links to
external data sources. For example, if you were part of an enterprise that made a lot of indi-
vidual sales that you tracked in a Microsoft Access database, you might want to create links
from each cell in the workbook to the corresponding cell in the database table. If you had
Excel continue its default behavior of recalculating the workbook by checking the values in the
database table, you could have a long wait on your hands every time you open the workbook.
The code you use to save the link values is

ThisWorkbook.SaveLinkValues = True

Setting the SaveLinkValues property to False would cause Excel to re-check the values every
time the workbook is opened and, if the values were not available, would generate an error.

Requiring a Password to Open a Workbook

Figuring out ways to maintain the integrity of your data in a corporate environment is one of
the most important parts of working in the information industry. While you certainly need
to protect your data against snoopers who get into your system from the outside or from
internal users who gain extra privileges and browse through the intranet, you can also grant
anyone access to one of your workbooks but still require them to provide the proper pass-
word when they try to save any changes to the workbook.
The following code listing lets a user set a password that will be required to open the work-
book. Once someone opens the workbook, they’ll be able to make any changes they want, but
only if they know the password!

141