The Workbooks object in VBA
In an Excel application you can have several workbooks objects. They correspond to the excel files that are currently open in your excel session. You can refer to the current workbook as ActiveWorkbook. If you know the name of the workbook ("Book2.xlsx" for example here), you can find it as Workbooks("Book2.xlsx"). You can also grab it using an index number: Workbooks(1), but this is very much unreliable as you don?t precisely know what workbook you are dealing with.
In an Excel application you can have several workbooks objects. They correspond to the excel files that are currently open in your excel session. You can refer to the current workbook as ActiveWorkbook. If you know the name of the workbook (“Book2.xlsx” for example here), you can find it as Workbooks(“Book2.xlsx”). You can also grab it using an index number: Workbooks(1), but this is very much unreliable as you don’t precisely know what workbook you are dealing with.
What is important here is that the Workbooks object provides a list of all the workbooks that are currently open in this Excel application. That is actually why we can see an “s” at the end of Workbooks: there may well be several of them!
Refer to specific workbooks with variables
ActiveWorkbook to refer to the workbook currently in focus
Just like any object, it is possible to store workbooks in a specific variable. Let’s take a classic situation: you are working on two different workbooks with a macro. You need to access easily one or the other. You can store the currently opened workbook object in a variable like this:
Set MyWorkbook = Activeworkbook
This way, you can use this workbook later, even if it no longer the active workbook. Let’s suppose you open another workbook, that then becomes the new active workbook, and you want to copy a cell from the first to the second workbook, all in one line of code:
Activeworkbook.Sheets("sheet1").Range("A1").Value = MyWorkbook.Sheets("sheet1").Range("A1").Value
This is actually very useful to loop through Excel files using one macro stored on a separate Excel file. As a general rule, as soon as you need to use 2 workbooks in a macro, I would recommend to refer to them with a variable, as it makes the code clearer and less prone to error. A very common mistake is to forget that as soon as you open a new Excel file, it becomes the active workbook, and that may create errors when you refer to sheets, charts, pivotcaches, and other workbook specific objects. Indeed if you call these objects without specifying the parent workbook, VBA will infer that you are referring to the Activeworkbook… When you write sheets(1), VBA sees ActiveWorkbook .sheets(1)… A good practice is to refer to all of the workbooks you use with a variable, and use this variable for all workbook-specific objects you call.
Loop through open workbooks with the workbooks object
The workbooks object contains a list of opened workbooks. Then it is very easy to loop through all opened workbooks with a “for each” loop:
For Each Wkb In Workbooks
'' Run some code here
It can be very powerful to use in addition to this a workbook object that we defined before. For example, imagine that I have an excel file that aggregates data from smaller reports. There are many small reports that I want to aggregate in a large file. Let’s suppose I have opened all the files I want to aggregate plus the large consolidated file. I have set a workbook object called ConsolidatedFile and all the other opened files are reports I want to aggregate. I can use this code:
On Error GoTo NextWkb
'' We add an error handler so that if there is an error, we simply ignore the consolidation code and go to the next workbook
For Each Wkb In Workbooks
If Wkb.Name <> ConsolidatedFile.Name Then
'' We execute the consolidation code on Wkb only if it is not the consolidated file,
'' Remember that at some point Wkb will be equal to ConsolidatedFile because ConsolidatedFile is among the opened workbooks
'' Consolidation code here