Programming Excel with VBA and .NET

Add, Open, Save, and Close

Use Workbook objects to open, save, and control files in Excel. To create a new, empty file in Excel, use the Add method on the Workbooks collection:

Dim wb As Workbook
Set wb = Application.Workbooks.Add

Use the Workbook object ’s Save or SaveAs method to name the workbook and save the file to disk. The default save location in Excel is set in the Application object’s DefaultFilePath property, which is usually My Documents. For example, the following line saves the workbook created before as NewWorkbook.xls in My Documents:

wb.SaveAs "NewWorkbook"

The Save method is similar to SaveAs, except it uses the default filename (Bookn.xls) the first time a file is saved. Use SaveAs the first time you save a file or to save an existing workbook in a new file; use Save when you want to keep the workbook’s current name.

Use the Close method to close an open workbook. Closing does not automatically save changes to the workbook, but if there are any changes, Excel displays a Save Changes dialog box before closing. You must close a workbook before it can be deleted. Excel doesn’t provide objects to delete workbooks since they are simply files stored on disk. Instead, you use the Kill method or a similar technique to delete a workbook. The following code closes the workbook created previously and deletes it:

wb.Close
VBA.Kill "NewWorkbook.xls"

If you want to open an existing workbook, use the Open method:

Set wb = Application.Workbooks.Open("MyBook.xls")

As with Save, Excel ...