Vlookup with External Sheet in VBA

[vikas_newports]

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

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

 

[RoryA]

It depends. Can you guarantee they will be the only workbooks open?

 

[vikas_newports]

It depends. Can you guarantee they will be the only workbooks open?

Yes only these 2 first vb code's book and 2nd other book

 

[RoryA]

Then you can use code like this to get the name of the other workbook:

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)"

 

[vikas_newports]

Not working as it stuck on vlookup formula

 

[RoryA]

I don't know what that means?

 

[vikas_newports]

it means the error in formula, is it working for u ?

 

[RoryA]

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

 

[vikas_newports]

I am strange that it is stucking without running

 

[RoryA]

What is the error, and what is the value of otherworkbookname when the error occurs?

 

[EXCEL MAX]

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.

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.

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.