Vlookup with External Sheet in VBA

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
Hello
I hope all of you are doing well
I want to use vlookup from external file = Data and Worksheet = Support
as I already know how to use vlookup but I do not want to use workbook name and Worksheet name in my formula
this is the existing formula. Please help me to Write formula without using Workbook name and Worksheet Name in Formula

Code:
Range("B2").Formula = "=VLOOKUP(A2,'[DATA.xlsm]Support'!$A$2:$B$500,1,0)"
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It depends. Can you guarantee they will be the only workbooks open?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Then you can use code like this to get the name of the other workbook:

Code:
Dim wb As Workbook
For Each wb In Application.Workbooks

    If Not wb Is ThisWorkbook And Not wb.Windows(1).Visible = False Then
        Dim otherWorkbookName As String
        
        otherWorkbookName = wb.Name
        
        Exit For
    End If

Next wb

Range("B2").Formula = "=VLOOKUP(A2,'[" & otherworkbookname & "]Support'!$A$2:$B$500,1,0)"
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

I don't know what that means?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Yes, as long as the other workbook has a sheet called Support.
 

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
I am strange that it is stucking without running
Screenshot_1.jpg
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What is the error, and what is the value of otherworkbookname when the error occurs?
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Hello Vikas, follow me.
You are lucky man, it is possible to pull data from file with changed names.
To do that your active workbook need to know that new workbook is opened.
After that to create the difference between two workbook.
At the end to add the formula or to do any kind of manipulation between workbooks.
It's practice to create new class and after that to create "Public WithEvents" .
Don't be afraid from class module, in your case is very useful.
Create new module class and name it in the properties of the class "appEventClass".
Put this code in class code editor.
VBA Code:
Option Explicit

Public WithEvents varApp As Application

Private Sub varApp_WorkbookOpen(ByVal Wb As Workbook)
   
    Dim varWB1, varWB2 As Workbook
    If Workbooks.Count = 2 Then
        Set varWB1 = Workbooks(1)
        Set varWB2 = Workbooks(2)
        Application.DisplayAlerts = False
        varWB1.Sheets(1).Range("A1").Formula = "=VLOOKUP(A2,[DATA.xlsm]Support!$A$2:$B$500,1,0)"
        Application.DisplayAlerts = True
    End If
   
End Sub

Now you need to initialize class.
Put this code in the "ThisWorkbook" window.
VBA Code:
Dim varApplicationClass As New appEventClass

Private Sub Workbook_Open()
   
    Set varApplicationClass.varApp = Application
   
End Sub
That's it.
Note this...
Always first open workbook with class "appEventClass".
If you open workbook with data first, the code may replace workbooks places and do not work as you expect.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,577
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top