An Excel
Workbook is a file with the extension
.xls .
The basic building block of Excel starts with the
workbook.
[See the Excel object model]
Dim wkb as Excel.Workbook
Set wkb = Workbooks.Open filename:="c:\BalanceRept.xls"
OR
Set wkb = Workbooks.Add
Declare a variable for an Excel workbook.
Dim wkb as Excel.Workbook
SET the workbook variable to an Existing workbook.
The
SET statement is used to assign a variable to an object.
In this example, the
SET statement also
OPENS the workbook.
Set wkb = Workbooks.Open("c:\BalanceRept.xls")
Notice that the SET statement Opens a workbook from the workbooks collection.
All workbook objects are part of the workbook collection of all Excel workbooks.
Creating or opening a specific workbook must reference the workbooks collection.
Set the workbook variable to a New workbook.
This example uses the
SET statement to create a
NEW workbook
and then save it with its new name:
Set wkb = Workbooks.Add
wkb.SaveAs filename:="C:\NewReport.xls"
Notice that the new workbook is added to the workbooks collection.
In both of these example, the workbook will be refered to in the VBA code as
wkb.
This will be used to reference objects within the workbook.
For Example:
wkb.Worksheets(1)
OR
wkb.worksheets("Sheet1")
Closing a workbook
To close a workbook, we use the
Close method with our workbook name.
wkb.close
Saving and Closing a workbook
Either of these samples will save the workbook before closing it.
wkb.SaveAs filename:="C:\NewReport.xls"
wkb.close
OR
wkb.close(SaveChanges:=true, Filename:="C:\NewReport.xls")