Using the VLOOKUP function in Excel is an excellent way to extract specific data from a dataset. Interestingly, the dataset can be from another worksheet or even another workbook. As we all know, Visual Basics for Application (VBA) can perform all variations of tasks in Excel; we can do the task of doing a lookup with the VBA VLookup function. Here, we will discuss with several examples the methods to VLookup data from another workbook without opening it in Excel VBA. In addition, we will show suitable alternatives to VBA VLookup and some bonus problems regarding Excel.
This video demonstrates how we run the code to perform a lookup in another closed workbook.
Introduction to Excel VBA VLookup Function
The VBA VLookup function is quite similar to the Excel VLOOKUP function. It searches for a lookup value from a table and gives respective data from the table as output.
- Syntax:
Application.VLookup(lookup_value, table_array, column_index, range_lookup)
- Arguments:
ARGUMENT | REQUIRED/OPTIONAL | VALUE |
---|---|---|
lookup_value | Required | The value that it looks for is in the leftmost column of the given table. Can be a single value or an array of values. |
table_array | Required | The table in which it looks for the lookup_value in the leftmost column. |
col_index | Required | The number of the column in the table from which a value is to be returned. |
[range_lookup] | Optional | Tells whether an exact or partial match of the lookup_value is required. 0 for an exact match, 1 for a partial match. The default is 1 (partial match). |
How to Launch VBA Editor in Excel
For inserting any VBA code, open the VBA window first. For that, just click on the Developer tab and select Visual Basic from the appeared options. Also, you can open the VBA window by pressing the keyboard shortcut Alt + F11.
If your Excel doesn’t have the Developer tab automatically, then you can enable the Developer tab in Excel.
After opening the VBA window, you need to insert a new Module.
Excel VBA to Vlookup in Another Workbook Without Opening: 2 Examples
Now it’s time to jump to the methods to Vlookup from another workbook without opening it. It’s not possible to use VBA VLookup without opening the workbook. So, in all the methods, we will open the workbook by VBA code in Excel, and after finishing our task, we will close the workbook by code.
Let’s say we have a dataset which is a “Sales report for Marico company”. The dataset is in another workbook named “Sales Report”.
1. Applying VBA VLookup Function to Find Value from Another Workbook Without Opening It
In the first method, we will discuss the direct use of the VBA Vlookup function to find the lookup value from a closed workbook. We have used several codes which are a bit different from each other in structure. Simply put, these differ based on how you’re taking the input from Excel files.
1.1 Using VBA VLookup Function with Cells Property
In this method, we have prepared a code to Vlookup from another workbook without opening it. In this case, we used the VBA Cells property of the Range object to pick the lookup value from the worksheet.
We have included a code in the following section. Just copy the code and insert it in a new module. Of course, you have to change the directory, workbook, and worksheet name in the code according to yours. Then, click on the Run button.
Code:
Sub Apply_VLookup_Closed_Book()
'Declare Required Variables
Dim closed_wb As Workbook, open_wb As Workbook
Dim closed_rng As Range, open_rng As Range
Dim result1 As Variant
Dim result2 As Variant
Dim result3 As Variant
Dim result4 As Variant
Dim result5 As Variant
' Set the closed workbook and range
Set closed_wb = Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx", ReadOnly:=True)
Set closed_rng = closed_wb.Worksheets("Sales").Range("B5:F14")
' Set the open workbook and range
Set open_wb = ThisWorkbook ' The open workbook
Set open_rng = open_wb.Worksheets(1).Range("B4:C9") ' The range in the open workbook
' Perform the VLOOKUP operation in the closed workbook and store the result in a variable
result1 = Application.VLookup(open_rng.Cells(2, 1), closed_rng, 3, False)
' Display the result in cell C7 of the open workbook
open_rng.Cells(2, 2).Value = result1
open_rng.Cells(3, 2).Value = result2
open_rng.Cells(4, 2).Value = result3
open_rng.Cells(5, 2).Value = result4
open_rng.Cells(6, 2).Value = result5
' Close the closed workbook
closed_wb.Close SaveChanges:=False
End Sub
Code Breakdown:
- Firstly, we declared a Sub and necessary variables, for example, closed_wb & open_wb as Workbook, closed_rng, & open_rng as Range, result1 to 5 as Variant.
- Then, we opened the worksheet from a closed workbook. For this, we used the Set statement with Workbooks.Open method where the ReadOnly parameter is True to open the workbook in read-only mode. Here, our closed workbook path is C:\ExcelDemy\Sales Report.xlsx and the range is B5:F14.
- After that, we applied the VLookup function to lookup value. The VLookup function searches for the value open_rng.Cells(2, 1) in the table_array closed_rng and give the respective data from column 3 of the table. False symbolizes the match should be exact.
- Lastly, we pasted the results in specific cells of the worksheet with Cells.Value property.
- Finally, we closed the workbook from where we extracted the data without changing anything.
After running the code, you’ll get the following output, as shown in the below video.
The following image shows the source dataset of the closed workbook from where we extracted the values.
Read More: Excel VBA Vlookup with Multiple Criteria
1.2 Utilizing VBA VLookup Function with Range Property
In this method, we will use a direct cell reference with the VBA Range property to specify the lookup value, which is a bit simpler to use.
Just copy the attached code, making the necessary changes to the directory path and workbook and worksheet names. After that, just run the code.
Code:
Sub VLookup_Specified_Range()
Dim LookupValue1 As String
Dim LookupValue2 As String
Dim LookupValue3 As String
Dim LookupValue4 As String
Dim LookupValue5 As String
Dim LookupRange As Range
Dim ResultRange As Range
Dim ExternalWorkbook As Workbook
Set ExternalWorkbook = Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx")
' replace "C:\Users\SHIMUL\OneDrive\Desktop\Softeko_home\95-0070\ales Report.xlsx" with the file path and name of the external workbook you want to lookup values from
Set LookupRange = ExternalWorkbook.Worksheets("Sales").Range("B4:F14")
' replace "Sales" with the name of the worksheet that contains the lookup and result ranges in the external workbook
LookupValue1 = ThisWorkbook.Worksheets("Specified_Range").Range("B5").Value
LookupValue2 = ThisWorkbook.Worksheets("Specified_Range").Range("B6").Value
LookupValue3 = ThisWorkbook.Worksheets("Specified_Range").Range("B7").Value
LookupValue4 = ThisWorkbook.Worksheets("Specified_Range").Range("B8").Value
LookupValue5 = ThisWorkbook.Worksheets("Specified_Range").Range("B9").Value
' replace "Specified_Range" with the name of the worksheet that contains the lookup value in the current workbook
ThisWorkbook.Worksheets("Specified_Range").Range("C5").Value = Application.VLookup(LookupValue1, LookupRange, 3, False)
ThisWorkbook.Worksheets("Specified_Range").Range("C6").Value = Application.VLookup(LookupValue2, LookupRange, 3, False)
ThisWorkbook.Worksheets("Specified_Range").Range("C7").Value = Application.VLookup(LookupValue3, LookupRange, 3, False)
ThisWorkbook.Worksheets("Specified_Range").Range("C8").Value = Application.VLookup(LookupValue4, LookupRange, 3, False)
ThisWorkbook.Worksheets("Specified_Range").Range("C9").Value = Application.VLookup(LookupValue5, LookupRange, 3, False)
ExternalWorkbook.Close False
' set "False" to "True" if you want to save changes to the external workbook before closing it
End Sub
Code Breakdown:
- Firstly, we opened a closed workbook Sales Report.xlsx, and set a variable LookupRange for range B4:F14 of the Sales worksheet from where we will extract the value.
- Then, we set LookupValue1 with a value from range B5 of the Specified_Range sheet.
- Here, we used the VLookup function to paste the value in a specific cell. The VLookup function searches for LookupValue1 in table_array LookupRange and gives respective column 3 output from the table_array. False means an exact match for looking for value.
- Lastly, we closed the external workbook.
The following image shows the source dataset of the closed workbook from where we extracted the values.
After running the code, you’ll get the following output, as shown in the below video.
1.3 Incorporating Excel VLOOKUP Function with VBA Code
Another much simpler way to do the same task is to insert the general Excel VLOOKUP function in the worksheet cell with the help of VBA code. So, without further delay, let’s jump to the procedures.
All you have to do is to insert the following attached code in a new module and run it.
Code:
Sub VLOOKUP_Closed_Workbook()
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C5").Formula = "=VLOOKUP(B5,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C6").Formula = "=VLOOKUP(B6,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C7").Formula = "=VLOOKUP(B7,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C8").Formula = "=VLOOKUP(B8,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C9").Formula = "=VLOOKUP(B9,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
' change the file path according to your one 'C:\Users\SHIMUL\OneDrive\Desktop\Softeko_home\95-0070\[Sales Report.xlsx]Vlookup_Apply'
'Also change the cell reference according to your worksheet
End Sub
Here, we just inserted the VLOOKUP formula with the help of VBA. The following part inside the VLOOKUP formula navigates the worksheet of the closed workbook.
The following image shows the source dataset from where we extracted the values.
After running the code, you’ll get the following output, as shown in the below video.
1.4 Employing VBA VLookup Function with InputBox
Now, we will change the code a bit to take the value to look for from the InputBox and give the lookup value in a MsgBox.
You just need to copy the attached code into a new module, change the file directory as necessary, and run the code.
Code:
Sub VLookup_msgbox()
Dim LookupValue As String
Dim LookupRange As Range
Dim ResultRange As Range
Dim ExternalWorkbook As Workbook
Set ExternalWorkbook = Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx")
' Replace "C:\Users\SHIMUL\OneDrive\Desktop\Softeko_home\95-0070\ales Report.xlsx" with the file path and name of the external workbook you want to lookup values from
Set LookupRange = ExternalWorkbook.Worksheets("Sales").Range("B4:F14")
' Replace "Sales" with the name of the worksheet that contains the lookup and result ranges in the external workbook
LookupValue = InputBox("Enter the Value for VLookup", "Input Box")
VLookup_Result = Application.VLookup(LookupValue, LookupRange, 3, False)
ExternalWorkbook.Close False
' Set "False" to "True" if you want to save changes to the external workbook before closing it
MsgBox "The VLookup Value(Quantity) is " & VLookup_Result
End Sub
In the code, we set an input box whose value we used in the VLookup formula. Also, we showed the Vlookup result value in a MsgBox.
The following image shows the source dataset of the closed workbook from where we extracted the values.
After running the code, you’ll get the following output, as shown in the below video.
Read More: Excel VBA to Vlookup Values for Multiple Matches
2. Using VBA Index Match Functions to Vlookup from Another Workbook Without Opening
As an efficient alternative to using the VBA VLookup function, we can use the VBA Index and Match functions together to vlookup in another workbook without opening it in Excel. Why not use them in the code? Let’s see how to use them to find the lookup value.
As usual, copy the attached code into a new module, change the directory and sheet name, and run the code. It’s so simple.
Code:
Sub Index_Match_ExternalWorkbook()
Dim LookupValue1 As String
Dim LookupValue2 As String
Dim LookupValue3 As String
Dim LookupValue4 As String
Dim LookupValue5 As String
Dim LookupRange As Range
Dim ResultRange As Range
Dim ExternalWorkbook As Workbook
Set ExternalWorkbook = Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx")
' replace "C:\Users\SHIMUL\OneDrive\Desktop\Softeko_home\95-0070\ales Report.xlsx" with the file path and name of the external workbook you want to lookup values from
Set LookupRange0 = ExternalWorkbook.Worksheets("Sales").Range("B5:B14")
Set LookupRange = ExternalWorkbook.Worksheets("Sales").Range("D5:D14")
' replace "Sales" with the name of the worksheet that contains the lookup and result ranges in the external workbook
LookupValue1 = ThisWorkbook.Worksheets("Index_Match").Range("B5").Value
LookupValue2 = ThisWorkbook.Worksheets("Index_Match").Range("B6").Value
LookupValue3 = ThisWorkbook.Worksheets("Index_Match").Range("B7").Value
LookupValue4 = ThisWorkbook.Worksheets("Index_Match").Range("B8").Value
LookupValue5 = ThisWorkbook.Worksheets("Index_Match").Range("B9").Value
' Replace "Vlookup_Apply" with the name of the worksheet that contains the lookup value in the current workbook
ThisWorkbook.Worksheets("Index_Match").Range("C5").Value = Application.Index(LookupRange, Application.Match(LookupValue1, LookupRange0, 0))
ThisWorkbook.Worksheets("Index_Match").Range("C6").Value = Application.Index(LookupRange, Application.Match(LookupValue2, LookupRange0, 0))
ThisWorkbook.Worksheets("Index_Match").Range("C7").Value = Application.Index(LookupRange, Application.Match(LookupValue3, LookupRange0, 0))
ThisWorkbook.Worksheets("Index_Match").Range("C8").Value = Application.Index(LookupRange, Application.Match(LookupValue4, LookupRange0, 0))
ThisWorkbook.Worksheets("Index_Match").Range("C9").Value = Application.Index(LookupRange, Application.Match(LookupValue5, LookupRange0, 0))
ExternalWorkbook.Close False
' set "False" to "True" if you want to save changes to the external workbook before closing it
End Sub
Code Breakdown:
- Here, we set the range LookupRange0 with data from range B5:B14 of the Sales sheet of the Closed workbook “Sales Report” which is later used in the Match function.
- Then, we set LookupRange with the range D5:D14 of the Sales sheet of the closed workbook from where the result value will come from with the Index function.
- Later on, we set LookupValue1 with a value from cell B5 which means the values to look for from our active sheet.
- At last, we applied the previously mentioned data as the arguments of the Index & Match functions to get the lookup result. The Match function searches for LookupValue1 in the table range LookupRange0 and gives the respective row number. 0 denotes the match should be exact. Then, the Index function gives the data from the row number(which comes from the Match function) of the range LookupRange.
The following image shows the source dataset of the closed workbook from where we extracted the values.
After running the code, you’ll get the following output, as shown in the below video.
Read More: How to Use Excel VBA VLookup with Named Range
How to Vlookup in Same Worksheet Within Same Workbook Using VBA VLookup Function in Excel
It’s a simple task to Vlookup from the same worksheet. We will show the way to do it with VBA.
Insert the added code in a new module and run it. You may need to change the workbook and sheet name.
Code:
Sub VLookup_Same_Sheet()
ThisWorkbook.Worksheets("VLookup_Same_Sheet").Range("C17") = Application.VLookup(ThisWorkbook.Worksheets("VLookup_Same_Sheet").Range("B17"), ThisWorkbook.Worksheets("VLookup_Same_Sheet").Range("B5:F14"), 3, False)
End Sub
In this code, we used the simple VBA VLookup function with all ranges from our active sheet.
How to Extract Value from Different Sheet of Same Workbook Using VBA VLookup in Excel
We can also perform Vlookup from another sheet of the active workbook. Let’s do it with the VBA code. We used the “Sales” sheet of our active workbook for the lookup.
Just copy the attached code into a new module and run it. Change the sheet name according to your preference.
Code:
Sub VLookup_Different_Sheet()
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C5") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B5"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C6") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B6"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C7") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B7"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C8") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B8"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C9") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B9"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
End Sub
In the code, we included the table_array for the VLookup function from another sheet of the active workbook. We changed the sheet name reference for that case.
The VLookup results are indicated in the following image.
How to Vlookup from Another Workbook Without VBA in Excel
We can easily use a simple Excel VLOOKUP function to get the lookup value from another workbook without opening it. Use the following formula to do the trick. For the Vlookup value we used the following dataset from the “Sales” worksheet of a closed workbook “Sales Report”.
=VLOOKUP(B5,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!$B$4:$F$14,3,FALSE)
Change the range of references and workbook directory according to your preferences.
How to Get Data from Another Workbook in Excel VBA Without Opening It
It’s quite simple to get data from another workbook without opening it. Let’s do it with the VBA code.
Insert the following code and run it. You may need to change the file directory, workbook, and worksheet name and range according to your needs.
Code:
Sub Copy_Data_Closed_Workbook()
Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx", _
True, True).Worksheets("Sales").Range("B5:F14").Select
Selection.Copy
Windows("Applying VLOOKUP on Another Closed Workbook.xlsm").Activate
Sheets("Copied_Sheet").Select
Range("B5").Select
ActiveSheet.Paste
Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx").Close
End Sub
Here, we selected a specific range from a closed workbook with the Select property. After that, we copied the selected range with the Copy property. Later on, we selected the range B5 of our active worksheet Finally, we pasted the copied value with the Paste property.
The Copied data from the closed workbook are indicated in the following image.
Things to Remember
- In some cases, you have to be careful about specific cell references with the”$” sign.
- Verify the lookup value is present in the lookup table.
- You have to change the file path directory and workbook name, worksheet name, and different ranges in the attached codes according to your file environment.
- It’s quite simple to include references in Excel formulas. For example, if you type VLOOKUP afterward you can select the specific cell from any sheet for lookup value or table_array etc. Excel will automatically include sheet reference if it’s not an active sheet.
- Note that the workbook name is enclosed with a square bracket. Also, the workbook name and sheet name are enclosed with a single quotation (‘) which is required for names having spaces and punctuation signs.
Frequently Asked Questions
1. Can I use VBA VLookup to search for a value in multiple workbooks at once?
Sure, you can utilize VBA VLookup to search for a value in multiple worksheets at once. To do this, you have to combine all data into a single table in a worksheet and then apply the VLookup function.
2. How can I use VBA to automate the process of performing a vlookup in another workbook?
Certainly, you can automate the process utilizing VBA. For this, you can create a macro button or user interface element and then assign the VBA code to the element.
3. Are there any alternatives to VBA to vlookup from another workbook without opening?
Undoubtedly! Simply, you can apply the Excel VLOOKUP function that we discussed earlier. Furthermore, you can incorporate Power Query to do the same task.
Wrapping Up
- In the first method, we did vlookup from a closed workbook with the VBA VLookup function. We used a different approach for doing that, we used the Cells property in the first submethod, the second submethod was for putting vlookup value in a specific range, and in the third one we inserted simple VLOOKUP formula in the worksheet with VBA, in the fourth one we used input box for the lookup value of Vlookup function also we displayed the result with MsgBox.
- In the second method, we used the Index and Match functions to do the same work as VLookup.
- Later on, we discussed the ways to vlookup from the same as well as different sheets.
- We also discussed the way to do vlookup without VBA.
- In addition, we showed how to get data from another workbook with VBA without opening the workbook.
Download Practice Workbook
You can download these practice workbooks from here.
Conclusion
In the article, we have shown various ways to get a lookup value from another workbook without opening it with the VBA code in Excel. Also, we included some basic lookup applications, like getting data from the same or different sheet of an active workbook with VBA. In addition, we have shown how to do the same task with a simple formula without VBA. Hopefully, this article will come in handy. Feel free to comment if you have any queries or suggestions.