Excel VBA to Vlookup in Another Workbook Without Opening

Get FREE Advanced Excel Exercises with Solutions!

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.

Opening VBA window from the Developer tab

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.

Dataset from closed workbook to get vlookup value

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 for Using VBA VLookup Function with Cells Property

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.

VLookup values in dataset

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.

VLookup values in dataset

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 for Incorporating Excel VLOOKUP Function with VBA

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.

The VLookup results are indicated in the following image.

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 for Employing VBA VLookup Function with InputBox

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.

The VLookup results are indicated in the following image

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 for Using VBA Index Match Functions to Vlookup from Another Workbook Without Opening

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.

The VLookup results are indicated in the following image.

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

How to Vlookup in Same Worksheet Within Same Workbook Using VBA VLookup Function

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 to Vlookup in Same Worksheet Within Same Workbook Using VBA VLookup Function.

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.

Dataset from another worksheet of same workbook.

Just copy the attached code into a new module and run it. Change the sheet name according to your preference.

Code to Extract Value from Different Sheet of Same Workbook Using VBA VLookup in ExcelCode:

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.

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”.

dataset from closed workbook to use VLookup

=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 Vlookup from Another Workbook Without VBA in Excel


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 to Get Data from Another Workbook in Excel Without Opening Incorporating VBA

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.

The VLookup results 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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo