Étiquette : macro_box_userforms

In many situations, it’s necessary to access other workbooks located in the same folder or in a subfolder of the current workbook. To do this reliably, you first need to determine the file path of the workbook that contains your VBA code (ThisWorkbook) or any currently active workbook.

The following example demonstrates how to retrieve the path for three different workbooks:

Sub DeterminePath()
    Workbooks.Open "C:\Users\MAC 2015\Desktop\Titanic-Dataset.csv"
    MsgBox "Titanic-Dataset Mappel is located in: " & ActiveWorkbook.Path
    ActiveWorkbook.Close
    MsgBox "This Titanic-Dataset is located in: " & ThisWorkbook.Path
    Workbooks.Open ThisWorkbook.Path & "C:\Users\MAC 2015\Desktop\titanic.xls"
    MsgBox "The titanic Test is located in: " & ActiveWorkbook.Path
    ActiveWorkbook.Close
End Sub.

Explanation of the Procedure:

• Opening an external workbook and retrieving its path

• Open « C:\Users\MAC 2015\Desktop\Titanic-Dataset.csv »
• MsgBox « Titanic-Dataset Mappel is located in:  » & ActiveWorkbook.Path

1. • This opens the file located at « C:\Users\MAC 2015\Desktop\Titanic-Dataset.csv ».
   • As it becomes the active workbook, the .Path property returns its directory path (C:\Users\MAC 2015\Desktop in this case).
   • The path is then shown in a message box.
   • The workbook is subsequently closed.

• Retrieving the path of the workbook that contains the macro

• MsgBox « This Titanic-Dataset is located in:  » & ThisWorkbook.Path

1. • ThisWorkbook refers specifically to the workbook containing the macro code, not necessarily the active workbook.
   • Its .Path property returns the folder in which this workbook is saved.
   • This is useful when you want your macro to refer to other files relative to its own location, regardless of which workbooks are currently active.

• Opening a file in a subfolder relative to the current workbook

• Open ThisWorkbook.Path & « C:\Users\MAC 2015\Desktop\titanic.xls »
• MsMsgBox « The titanic Test is located in:  » & ActiveWorkbook.Path

1. • Here, we use the path of ThisWorkbook and append \Subfolder\Test.xlsx to construct the full path to another file located in a subfolder named Subfolder.
   • After opening the file, the path is again displayed and the workbook is closed.

Practical Use Case:

This method is commonly used in VBA projects to ensure that files are accessed reliably, especially when deploying your Excel solution on different machines where absolute paths may not be consistent. By dynamically referencing paths based on ThisWorkbook.Path, your macros become portable and adaptable to various file system environments.

Selecting a Workbook by Index or Name

The following macro demonstrates two different techniques for selecting (activating) already opened workbooks:

Sub SelectWorkbookFromList()
    Workbooks(1).Activate
    MsgBox ActiveWorkbook.Name
    Workbooks("Workbook2.xlsm").Activate
    MsgBox ActiveWorkbook.Name
End Sub

Explanation:

This VBA subroutine shows how to activate workbooks using:

1. Selection by Index:

Workbooks(1).Activate

• The Workbooks collection contains all the currently open workbooks in the Excel session.
• Each workbook in this collection has a position index, starting at 1 for the first opened workbook, up to Workbooks.Count (the total number of open workbooks).
• The line above activates the workbook at position 1.
• MsgBox ActiveWorkbook.Name then displays the name of the currently active workbook, confirming which one was selected.

⚠️ Be cautious:
If you try to use an index that doesn’t exist (for example, Workbooks(3) when only two workbooks are open), Excel will throw a runtime error.
Techniques for managing such errors will be covered in Chapter 4: Error Handling.

2. Selection by Name:

Workbooks(« Workbook2.xlsm »).Activate

• Instead of using an index, you can specify the name of the workbook file as a string in quotation marks.
• This is often more reliable than using the index because the order of open workbooks can change (especially if the user opens or closes files dynamically).
• The name used must be exactly the file name as shown in Excel’s title bar (but without the full file path).
• After activation, the MsgBox line again displays the name of the now active workbook.

✅ Important:
In Excel, you cannot open two workbooks with the same name at the same time, even if they are located in different folders. This guarantees that the name is a unique identifier among open workbooks.

Recommendation:

Although selecting a workbook by index may work in some cases, using the workbook name is more precise and less error-prone. It’s easier to understand and maintain in your code, especially when multiple workbooks are open.

The following procedure saves the current workbook in two different ways:

Sub SaveWorkbook()
    ThisWorkbook.Save
    ThisWorkbook.SaveAs "C:\Users\MAC 2015\Desktop\woorbook.xls"
    MsgBox "Saved: " & ThisWorkbook.Saved
End Sub

Explanation:

This macro performs two main saving actions using VBA:

1. ThisWorkbook.Save
   This line calls the Save method on the ThisWorkbook object, which refers to the workbook containing the macro. It saves the current state of the workbook using its existing name and location. If the workbook hasn’t been saved before (i.e., it’s a new workbook), this would usually trigger a save prompt or fallback to a default path.
2. ThisWorkbook.SaveAs « C:\Users\MAC 2015\Desktop\titanic.csv »
   This command performs a « Save As » operation, creating a copy of the current workbook under the new name Workbook2.xlsm in the specified folder C:\Users\MAC 2015\Desktop.
   ⚠️ After this line is executed, your VBA session continues within this newly saved copy. That is, any further operations now apply to Workbook2.xlsm, not the original file.
3. MsgBox « Saved:  » & ThisWorkbook.Saved
   This displays a message box showing the status of the workbook’s Saved property. This property is a Boolean (True or False) that indicates whether the workbook has unsaved changes:
   • True means the workbook is currently saved (no changes made since last save).
   • False means the workbook has been modified and those changes haven’t been saved yet.

Since the workbook was just saved (twice), the Saved property should return True at this point.

Note:
You can use the Saved property in conditional logic (like If…Then statements) to determine whether saving is necessary. For instance, if ThisWorkbook.Saved = False, it would be a good idea to prompt the user to save the workbook to avoid losing changes.

The following VBA procedure demonstrates how to activate a workbook that is already open. This is especially useful when multiple workbooks are open and you want to make sure that further actions are performed in a specific one:

Sub ActivateWorkbook()
    ThisWorkbook.Activate
    MsgBox ActiveWorkbook.Name
End Sub

Explanation:

This procedure uses the Activate() method of the Workbook object. In this case, it calls ThisWorkbook.Activate, which activates the workbook that contains this very VBA code.

When a workbook is activated, Excel brings it into focus—making it the currently selected and interactive file. This is important when automating tasks across multiple open workbooks and you want to ensure that subsequent operations (such as writing data, inserting sheets, or saving files) affect the correct one.

To confirm the activation, the procedure then displays the name of the currently active workbook using ActiveWorkbook.Name.

Identifying the Active Workbook

The following VBA procedure demonstrates how to determine the name of the active workbook in two different situations:

Sub IdentifyActiveWorkbook()
    Workbooks.Open "C:\Users\MAC 2015\Desktop\Titanic-Dataset.csv"
    MsgBox "Active after opening: " & ActiveWorkbook.Name
    ActiveWorkbook.Close
    MsgBox "Active after closing: " & ActiveWorkbook.Name
End Sub

Explanation:

This procedure begins by opening an additional workbook from a specified file path. It assumes that the file « C:\Temp\Mappe1.xlsm » exists and can be opened without error.

The keyword ActiveWorkbook refers to the workbook that is currently in focus—the one the user is interacting with or the one that has been programmatically activated. In Excel, any workbook that is just opened automatically becomes the active workbook.

To verify this behavior, the name of the active workbook is displayed using MsgBox() immediately after opening the file.

Next, the Close() method is called on the ActiveWorkbook object, which closes the workbook that was just opened. Once it is closed, Excel automatically reactivates the workbook that was active before the new one was opened.

To verify the change, the procedure again displays the name of the currently active workbook using MsgBox().

Additional Tip:

If you want to close a specific workbook by its name rather than using ActiveWorkbook, you can write:

Workbooks(« Titanic-Dataset.csv »).Close

The following VBA procedure demonstrates two ways to retrieve the name of a workbook:

Sub GetWorkbookName()
    MsgBox "Name: " & ThisWorkbook.Name
    MsgBox "Name with Path: " & ThisWorkbook.FullName
End Sub

Explanation:

The keyword ThisWorkbook always refers to the workbook in which this code resides, regardless of which workbook is currently active on the screen.

In this procedure:

• ThisWorkbook.Name retrieves and displays the name of the file only, such as Titanic-Dataset.csv. It does not include the file path.

• ThisWorkbook.FullName, on the other hand, retrieves and displays the full file path, including the name of the workbook. For example:
  C:\Users\MAC 2015\Desktop\Titanic-Dataset.csv

These properties are useful when you want to display or store workbook information, or when you need to log or reference the file in other operations, such as saving copies or linking to external files.

Creating a New Workbook

The following VBA procedure demonstrates how to create and open a new workbook in Excel. To confirm that the new workbook has been successfully added, it displays the number of currently open workbooks before and after the operation:

Sub CreateNewWorkbook()
    MsgBox "Before: " & Workbooks.Count & " workbook(s)"
    Workbooks.Add
    MsgBox "After: " & Workbooks.Count & " workbook(s)"
End Sub

Explanation:

This procedure calls the Add() method of the Workbooks collection. The Add() method instructs Excel to create a new, empty workbook and open it. As a result, this new file becomes the active workbook, meaning it is now in focus and ready for editing or further automation.

Simultaneously, the Workbooks collection is updated to include this new workbook. You can clearly observe this change by looking at the two message boxes: one shows the number of workbooks before creation, and the other shows the count afterward. The difference confirms that a new workbook has been added to the collection.

This approach is useful when your macro needs to generate a new workbook—for example, to export reports, create backups, or collect data—without altering the currently open files.

The following VBA procedure demonstrates how to open an existing Excel workbook:

Sub OpenExistingWorkbook()
    Workbooks.Open "C:\Users\MAC 2015\Desktop\exercice.xlsm"
End Sub

Explanation:

This procedure uses the Open() method of the Workbooks object. When Workbooks.Open is called with a file path as an argument, Excel opens the specified workbook file from the given location.

Once opened, the workbook becomes the active workbook, meaning it is now in focus and ready for user input or further processing through code.

By opening this file, a new item is also added to the Workbooks collection, increasing the count of open workbooks.

In the example shown above, an absolute file path (C:\Users\MAC 2015\Desktop\exercice.xlsm) is used to specify the workbook location. However, you can also open files using relative paths or paths pointing to the same directory as the workbook executing the code. 

Important Note:

If the specified file does not exist at the given location, the program will fail and throw a runtime error. Excel displays an error message and halts execution, as illustrated in Figure 2.2.

These types of runtime interruptions are common when dealing with file operations. In Chapter 4, « Error Handling », you will learn how to catch and handle such errors properly, so your VBA code can respond gracefully instead of crashing.

The following VBA procedure shows how to determine the number of Excel workbooks currently open:

Sub CountWorkbooks()
    MsgBox "Number of open workbooks: " & Workbooks.Count
End Sub

Explanation:

This procedure retrieves the value of the Count property from the Workbooks collection. The Count property returns a numerical value indicating how many workbooks are currently open in Excel.

The result is then displayed in a message box using the MsgBox() function. This is a simple yet effective way to monitor the number of active workbooks during the execution of your macros.

Important Note:

The count also includes hidden workbooks, such as the Personal Macro Workbook (usually named Personal.xlsb), if it is open. This special workbook is often used to store macros that are available globally across all workbooks. You can verify its presence by checking the Project Explorer in the VBA Editor (VBE).

Illustration Note:

From this point on in the source material, message box titles and the OK button will no longer be shown in figure illustrations for simplicity.