Mahfuza Anika Era
Remembering long Workbook names takes extra time and effort. Using VBA, you can activate a Workbook without remembering it’s full name. Isn’t it great? With the help of VBA macro, you can easily activate a Workbook just by writing the partial name of the Workbook. In this article, I am going to cover 2 easy methods of using Excel VBA to Activate Workbook with Partial Name. Let’s go through the article now.
The overview video shows the use of the like operator in the VBA code to activate workbook with partial name.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
How to Launch a VBA Editor
There are various methods by which you can open the VBA editor. The most commonly used method is Developer Tab >> Visual Basic and the simplest way to launch the VBA editor is to press ALT+F11.
Then, go to Insert >> Module to create a new module.
If you don’t have the Developer tab in your Excel workbook, follow the below steps:
- First, right-click anywhere on the Tab section. Then, click on Customize the Ribbon…
- Go to Customize Ribbon >> Developer. Next, select it and finally click OK.
Excel VBA to Activate Workbook with Partial Name: 2 Easy Methods
Suppose, you have opened a Workbook named “Employee_details_12589” and for some reason you have minimized this Workbook. But when you need to activate this Workbook, you may not remember the full name of this Workbook as there are numbers in the name as well. In this case, you can solve this problem by running a VBA macro. Actually there are 2 easy methods to do this. Please follow the 2 methods and choose which one you want to use for your problem.
Method 01: Activate Workbook with Partial Name by Using Like Operator with a Wildcard “*”
Like operator is a comparison operator that allows you to compare a string to a pattern. It is often used in conjunction with the wildcard characters “?” and” * “. The “?” character represents a single character, while the “*” character represents any number of characters (including zero characters). I have used the like operator to compare the partial name of the Workbook with all the Workbooks available. If the partial name matches with a Workbook’s name, it will be activated.
Firstly, write the following code on your VBA code editor. Make necessary changes based on the Workbook Name that you want to activate.
Sub activate_workbook()
Dim wb As Workbook
Dim partial_name As String
partial_name = "Employee"
For Each wb In Application.Workbooks
If wb.Name Like partial_name & "*" Then
Debug.Print wb.Name
With wb.Sheets("Employee_details")
wb.Activate
End With
End If
Next wb
End Sub🛠️ Code Breakdown
-
Dim wb As Workbook Dim partial_name As String
Two variables are declared using the Dim keyword: wb as a workbook object and partial_name as a string variable.
-
partial_name = "Employee"
The value of partial_name is set to the string “Employee”.
-
For Each wb In Application.Workbooks
A For Each loop is used to iterate through all the workbooks in the current Excel application. The loop variable wb is assigned to each workbook in turn.
An If statement is used to check if the name of the current workbook (wb.Name) matches the string stored in partial_name followed by any number of characters (& “*”) using the Like operator.
-
If wb.Name Like partial_name & "*" Then Debug.Print wb.Name
If the workbook name matches, then the Debug.Print statement writes the workbook name to the Immediate window.
-
With wb.Sheets("Employee_details") wb.Activate End With End If Next wb
The With statement is used to temporarily activate the current workbook and set its ActiveSheet to the worksheet called “Employee_details”. The wb.Activate method is called to activate the workbook, although this is not strictly necessary since the With statement already activates the workbook.
Now, run the code by pressing the Run button.
Method 02: Activate Workbook with Partial Name by Looping Through All Open Workbooks
You can use InStr Function as well to activate the workbook by writing the partial name. The InStr function in VBA allows you to search for a specific substring within a larger string. With two arguments, the function returns the position of the first occurrence of the substring within the larger string. If the substring is not found, the function returns 0.
Copy the following code to the VBA code window.
Sub ActivateWorkbookWithName()
Dim partialName As String
Dim wb As Workbook
partialName = "Employee" 'replace with the partial name _
of the workbook you want to activate
For Each wb In Workbooks
If InStr(1, wb.Name, partialName, vbTextCompare) > 0 Then
wb.Activate
Exit For
End If
Next wb
End Sub🛠️ Code Breakdown
-
Dim partialName As String Dim wb As Workbook
Two variables are declared using the “Dim” statement: “partialName” as a string and “wb” as a workbook.
-
partialName = "Employee"
The value “Employee” is assigned to the “partialName” variable. This value is used later to match the name of a workbook.
-
c wb.Activate Exit For End If Next wb End Sub
The code enters a “For Each” loop that will iterate through all open workbooks. The “InStr” function is used to determine whether the name of the current workbook being evaluated contains the value in the “partialName” variable. The “InStr” function returns a number indicating the position of one string within another. If the value returned is greater than 0, then the “partialName” is found within the workbook name. The “vbTextCompare” parameter is passed to the “InStr” function to specify a case-insensitive comparison.
-
wb.Activate Exit For End If Next wb End Sub
If the “partialName” is found within the workbook name, then the “Activate” method is called on the workbook object to activate that workbook. The “Exit For” statement is used to exit the “For Each” loop after the first workbook that matches the “partialName” is found.
To see the result, run the code.
How to Activate Workbook with Partial Name and Get a MsgBox If Workbook Is Not Found
If the workbook doesn’t exist or is not open, then the code cannot activate the mentioned Workbook. It will be helpful if a MsgBox appears if the Workbook is not found. It will notify the user that the Workbook is not found and so it is not activated.
Write the code below in your VBA editor.
Sub MsgBox_If_file_not_found()
Dim i As Long
For Each wb In Application.Workbooks
If wb.Name Like "Student*" Then
i = i + 1
wb.Activate
Exit For
End If
Next wb
If i = 0 Then MsgBox "File is Not Found"
End Sub🛠️ Code Breakdown
-
Dim i As Long
The Sub procedure initializes a variable “i” as a Long data type.
-
For Each wb In Application.Workbooks i = i + 1 wb.Activate Exit For End If Next wb
The code then loops through each open workbook in the current instance of Excel using a “For Each” loop and assigns each open workbook to the “wb” variable. Within the loop, the code checks if the workbook’s name matches the pattern “Employee*”. If a matching workbook is found, the code increments the value of the “i” variable by 1, activates the workbook, and exits the loop using the “Exit For” statement.
-
If i = 0 Then MsgBox "File is Not Found" End Sub
After the loop completes, the code checks if the value of the “i” variable is equal to 0. If so, it displays a message box with the text “File is not open” using the “MsgBox” function.
Now, run the code by pressing Alt+F5 so that you can see the result.
Key Takeaways from This Article
- Activating Workbook with partial name using Like Operator.
- Activating Workbook with partial name with the help of InStr Function.
Conclusion
In this article, I have covered 2 easy ways of using Excel VBA to Activate Workbook with Partial Name. Hope this article will greatly help you to solve your problem. The VBA code may need to be modified based on your Workbook name. Follow the steps carefully so that you get the desired result. If you have any questions regarding the article, please comment so that I can help. Follow ExcelDemy for more tips and tricks that will help you to use Excel more effectively.
