This macro writes the workbook name to “Sheet1” and cell “A1” of each open workbook. The ability to call different workbooks by name allows you to interact with multiple workbooks (Excel files) from a single VBA macro.
Fortunately, you don’t have to define the workbook name each time you write a new line. VBA defaults the file location to the ActiveWorkbook.
Because ActiveWorkbook is the default location, you don’t have to mention it if you’re only working with one workbook. This is why we were able to omit it in our Range Object Examples.
The Workbooks.Add Method creates a new workbook and sets it to the ActiveWorkbook.
Because the new Excel File is the ActiveWorkbook, you can manipulate the new file with the ActiveWorkbook Property.
Declare Workbook Object
If you’re working with multiple workbooks, you don’t even have to type the long Workbook string each time. Let’s pretend you have an open workbook called MyWorkbook.xlsm, but it’s not your active workbook. Normally, you would have to type the following code at the front of each line of your macro that interacts with your workbook.
The modification adds 2 more lines, but it’s a much cleaner macro. You’ve replaced the long lines of code with the variable wb1. Notice, the Dim statement is used to declare a workbook object variable.
We’ll talk more about variables and declarations in two lessons, so stay tuned!
Your lines of code are much shorter this way and you’re far less likely to make a mistake. Consider this scenario. Let’s say your workbook is renamed MyWorkbook2.xlsm. If you used the 1st example, you’d have to change the name of your workbook in your macro 3 times. However, by using the second example, you only have to change the workbook name once!
You now know how to reference workbooks by name, activate new workbooks and add a new workbook. Since you are familiar with the Workbook Object, I hope you’ll join me as I introduce the Worksheet Object. Worksheets are far more exciting than Workbooks.