An Excel Workbook is a file with the extension .xls or .xlsx .
( .xls for Excel 97 through 2003, .xlsx for Excel 2007 and greater).
The basic building block of Excel starts with the workbook.
[See the Excel object model]
Declare a variable for an Excel workbook
SET the workbook variable to an Existing workbook
SET the workbook variable to the ActiveWorkbook workbook
Set the workbook variable to a New workbook
Example: Open an existing workbook in VBA
Example: Open a new workbook in VBA
Referencing Objects in a Workbook
All workbook objects are part of the Workbooks Collection of all open Excel workbooks.
Creating or opening a specific workbook must reference Workbooks - plural.
The Workbooks Collection has only a few Methods, the primary ones being: Add, Copy, Delete, Move, and Select. You Open, Add, and Close an individual workbook from the workbooks collection object.
You will see this in the examples below.
The Workbooks Collection has very few Properties. The ones you will use most are Count and Item.
Dim x As Long
x= Workbooks.Count
The variable x will contain the number of open Workbooks.
The Item property is used to reference a specific workbook.
Dim wkb As Workbook
Set wkb = Workbooks.Item("myReport.xlsx")
'The word Item is often left out.
Set wkb = Workbooks("myReport.xlsx")
Declare a variable for an Excel workbook.
Put this declaration at the beginning of your VBA program.
Dim wkb as Workbook
You give your workbook a variable name. (In this example wkb).
This allows you to reference the workbook in your code by name.
You can use whatever name you want, but a shorter name will be easier to type,
which you will do often in the code,
and you should use a name that reminds you of what it means.
Sometimes you will have more than one workbook open, and you will need to give them separate names.
SET the workbook variable to an Existing workbook.
The SET statement is used to assign the variable to a specific workbook.
In this example, the SET statement references a workbook by name and also Opens the workbook.
Set wkb = Workbooks.Open("C:\BalanceRept.xlsx")
Notice that the SET statement Opens a workbook from the workbooks collection.
For the rest of the program we can refer to this workbook by its name wkb
SET the workbook variable to the ActiveWorkbook workbook.
If the VBA code is within a specific workbook, we can reference it as follows:
Set wkb = Workbooks.ActiveWorkbook
This works if you only have one workbook open, which is quite often.
Set the workbook variable to a New workbook.
This example uses the SET statement to create a NEW workbook.
Set wkb = Workbooks.Add
A New Excel workbook is opened. It has no name until we assign one.
Notice that the new workbook is added to the Workbooks collection.
Closing a workbook
To close a workbook, we use the Close method with our workbook variable name.
wkb.Close
If we reference the workbooks collection then ALL the Open workbooks are closed.
workbooks.Close
It's not possible to perform a Save on the Workbooks Collection. They must be saved individually.
Saving a Workbook
Either of these samples will Save the workbook.
To save an existing Excel file:
wkb.Save
You don't give the name of the file with Save.
To save and name a new or existing file (SAVEAS):
wkb.SaveAs filename:="C:\NewReport.xls"
SaveAs will assign a name and path. Use this to save a new file or to rename an existing one.
You can also assign a workbook name when you use the Close method.
wkb.Close(SaveChanges:=true, Filename:="C:\NewReport.xls")
These are the methods you will use the most often with a Workbook.
In later examples you will see more advanced methods of the Workbook.
* * * * * * * * * * * * * * * * * * * * * * * * * *
Putting It Together:
Open an existing workbook in VBA
'Declare a variable for the workbook
Dim wkb as Workbook
'Set the variable to an existing workbook
Set wkb = Workbooks.Open("c:\BalanceRept.xls")
'You can now reference a worksheet in the wkb workbook
wkb.Worksheets("Sheet1")
.'Save and then Close the workbook
wkb.Save
wkb.Close
Open a new workbook in VBA
This is the same as the above example except for the SET statement and the SAVEAS statement.
Dim wkb as Workbook
'Set the variable to the Workbooks collection.
'Use the command Add to open a new workbook.
Set wkb = Workbooks.Add
wkb.Worksheets("Sheet1")
.'Here is where you will place code to work with the worksheets
.'Use SaveAs and name the new workbook. Then close it.
wkb.SaveAs filename:="C:\NewReport.xls"
wkb.Close
Referencing Objects in a Workbook
In both of these example, the workbook will be refered in the VBA code as wkb, which is the variable assigned to it.
This will be used to reference objects within the Workbook, such as Worksheets.
For Example:
wkb.worksheets("Sheet1")
In this example, the workbook variable wkb references a specific worksheet in the worksheets collection.
See Excel Worksheets to see how to work with individual worksheets.