Working With Other Workbooks And Files

Ex m Using the Close method you can specify that you want to close all workbooks you have open in Excel. If the SaveChanges parameter does not have a value specified, Excel checks each workbook to ensure that you have saved it since its last modification. If a workbook contains modifications, Excel prompts you to save the workbook.

When you close all workbooks, the workbooks all close but the application, Excel, remains running. If you want the Excel application to close, you can use the Quit method with the

Application object: Application.Quit.

Before closing Excel, the Quit method first closes the open workbooks. If any of the workbooks contain current changes, Excel you to save the changes. If you do not want to save modified worksheets, and you want to avoid the dialog box asking you to save changes, you can use the DisplayAlerts property. This property determines whether the alert message displays when Excel closes workbooks, or performe any other tasks.

Example:

Application.DisplayAlerts = False

—0 Type SaveChange:=True.

■ If desired, specify additional parameter values.

■ The specified workbook file is closed. If changes have been made, the workbook is saved.

—0 Type SaveChange:=True.

■ If desired, specify additional parameter values.

■ The specified workbook file is closed. If changes have been made, the workbook is saved.

ATE A NEW WORKBOOK

You can create a new Excel workbook using the Add method of the Workbooks Collection. When you create a new workbook, Excel creates a new Workbook object and adds it to the Workbooks Collection. The Add method has one optional parameter that you can use as shown in the following code: Workbooks.Add(Template).

You can combine the Template parameter with the Add method to specify how Excel creates the workbook. You can use another workbook as the template for the new workbook or one of the four xlWBATemplate constant values.

When you use a workbook as the template, Excel copies all elements of the specified worksheet into the new workbook including all macros, text, and any settings. Be sure to specify the complete path of the workbook so that Excel can locate the file when the macro runs.

The xlWBATemplate has four different constant values that you can use to create a new workbook containing one sheet of the type specified with the constant value. Use xlWBATWorksheet to create a workbook containing one worksheet. If you want a workbook containing a chart, specify a constant value of xlWBATChart. To create an Excel 4.0 macro sheet, use xlWBATExcel4MacroSheet. Use xlWBATExcel4IntMacroSheet to create an international macro sheet.

When you use the Add method, without specifying a template, Excel creates a new workbook with the name Book1.xls. If a workbook already exists with that name, Excel assigns a name of Book2.xls. You can customize the workbook with the different properties of the Workbook object, such as the Title property, to specify the title for the workbook. You can change the name of the new workbook using the SaveAs method. See the section "Save a Workbook" for more information on the SaveAs method.

CREATE A NEW WORKBOOK

CREATE A NEW WORKBOOK

—n Create a new subroutine.

-0 Type Dim NewWB As Workbook, replacing NewWB with the name of the workbook variable.

—n Create a new subroutine.

-0 Type Dim NewWB As Workbook, replacing NewWB with the name of the workbook variable.

< Type Set NewWB = Workbooks.Add("filename.

xls"), replacing filename.xls with the name of the workbook to use as the template.

0 0

Post a comment