Page 161 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

Caution Remember that the name of a file is case-sensitive and includes the file exten;
Chapter 7
sion, which is usually .xls for Excel files. Typing SalesSummary or salessummary.xls into
this procedure’s InputBox wouldn’t indicate that the file SalesSummary.xls was open!

Saving Workbooks
Just as it’s important to save your workbooks when you enter data or change formatting
manually, it’s vital that you save your workbooks when you make significant changes using
VBA. One scenario where it’s possible for things to go wrong is if you were to import data
into a workbook but close the workbook before you save the new data. If the source file is on
another computer and you aren’t able to re-establish your connection to it for some reason,
it would be as if you hadn’t run the macro at all.
There is a property of the Application object that comes in handy when you want to save the
workbook that contains your macro code. That property is the ThisWorkbook property,
which returns a Workbook object representing the workbook that contains the VBA code
you’re running. With the new Workbook object in hand, you can call the Save method to save
a copy of your workbook.
The code to save the workbook containing the code is simply this:

ThisWorkbook.Save

Note If you use the ThisWorkbook.Save method to save a workbook for the first time,
Excel attempts to save the workbook using its current name. If it’s the first workbook you’ve
created in this Excel session, its name will be Book1. If there is another workbook in the
same directory with the same name, a message box will appear offering you the opportu;
nity to overwrite the existing file by clicking Yes or to abort the operation by clicking No or
Cancel. If you decide not to overwrite the existing file, a Microsoft Visual Basic run-time
error message box appears, indicating that the method failed (error 1004).

If you want to save a workbook with a new name or in a new location, you can use the SaveAs
method of the ThisWorkbook property. However, just as clicking the Save toolbar button is
much less complicated than clicking File, Save As to open the Save As dialog box and all of its
possibilities, so is using the ThisWorkbook.Save method much less complicated than using the
ThisWorkbook.SaveAs method. But, truth to tell, using the ThisWorkbook.SaveAs method is
fairly straightforward. The SaveAs method has the following full syntax:

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

The parameters are listed in Table 7-3 for your convenience.

135