Match from closed workbook

[YasserKhalil]

Hello everyone
In fact this issue seems easy but I tried and failed to achieve it .. I have two workbooks "Main" where I need to put the expected results in column F by looking up column A and the closed workbook "ClosedWB" the data are in columns D & E
If there is a faster way to do such task I would appreciate that a lot using ADO for example

Thanks advanced for great help

[Rudi]

You obviously want to avoid VLOOKUP's and Power Query?

Are you needing a macro that runs a query statement?
Is there any reason for avoiding the other scenarios?

[YasserKhalil]

Thanks a lot for reply Mr. Rudi
In fact I am searching all the time for the best method and faster vba codes .. so if it is found a better way I appreciate that a lot. But if it is only one way no problem of that way in that case

[HansV]

Open both workbooks.
In F2 in Sheet1 of Main.xlsm, enter the formula

=IFERROR(VLOOKUP(A2,[ClosedWB.xlsm]Sheet1!$D:$E,2,FALSE),"Not Found")

Fill down as far as needed.
Now close ClosedWB.xlsm. The formulas will still work!

[YasserKhalil]

Thanks a lot Mr. Hans
I have this code and it works except for "Not Found" part

Code: Select all

Sub VLOOKUP_Closed_Workbook()
    Dim wbk             As Workbook
    Dim ws As Worksheet
    Dim sh As Worksheet
    Dim x             As Variant
    Dim str          As String
    Dim i               As Integer
    Dim sFolder       As String
    Dim fName        As String

    Application.ScreenUpdating = False
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        sFolder = ThisWorkbook.Path & "\"
        fName = Dir(sFolder & "ClosedWB.xlsm")
        Set wbk = Workbooks.Open(sFolder & fName)
        Set sh = wbk.Worksheets("Sheet1")
        
        For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
            x = ""
            str = ws.Cells(i, 1).Value
            On Error Resume Next
                x = Application.WorksheetFunction.VLookup(str, sh.Range("D2:E" & sh.Cells(Rows.Count, 4).End(xlUp).Row), 2, False)
                If IsEmpty(x) Or IsError(x) Or x = "" Then ws.Cells(i, 6) = "Not Found"
            On Error GoTo 0
            ws.Cells(i, 6) = x
        Next i
    
        wbk.Close False
    Application.ScreenUpdating = True
End Sub

[YasserKhalil]

After seeing your formula I used it in the code

Code: Select all

        With ws.Range("F2:F" & ws.Cells(Rows.Count, 1).End(xlUp).Row)
            .Formula = "=IFERROR(VLOOKUP(A2,[ClosedWB.xlsm]Sheet1!$D:$E,2,FALSE),""Not Found"")"
            .Value = .Value
        End With

Thanks a lot for great help

In your opinion is there a faster way for that or that is considered the faster way?

[Rudi]

I was about to suggest this (like Hans)

=IFERROR(VLOOKUP(A2,[ClosedWB.xlsm]Sheet1!$D$1:$E$6,2,0),"Not Found")

Why use code? A formula will be easier to maintain and probably more optimal (unless there is a reason for VBA)?

[HansV]

If you want to use code, setting the formula and then replacing the formulas with their values is probably the most efficient way.

[YasserKhalil]

Thanks a lot .. This is a part of huge project that rely on VBA ..so I don'u use formulas in that case

[YasserKhalil]

Thanks Mr. Hans
I already took your advice as for setting the formula and then replacing the formulas with their values
May you have a look and see if there is something wrong with it now .. And if there is any point please tell me about it
Best Regards for both of you

[HansV]

As you would have found out if you had tried to create the formula yourself, you have to enclose the workbook/worksheet name in single quotes if either contains spaces:

Code: Select all

            .Formula = "=IFERROR(VLOOKUP(A2,'[Closed WB.xlsm]Sheet 1'!$D:$E,2,FALSE),""Not Found"")"

Substitute the actual names.

[YasserKhalil]

Thanks a lot for that
What if the workbook is not in the same path .. how can I fix that?

[HansV]

Change

Code: Select all

        sFolder = ThisWorkbook.Path & "\"

[YasserKhalil]

I don't mean that part Mr. Hans
I mean how to fix the formula to suit the code

[HansV]

There is no need to change the formula if the other workbook is open. In that situation, you don't have to specify the path, only the workbook name.
If you want to create the formula while the workbook is closed, you do have to include the path:

Code: Select all

            .Formula = "=IFERROR(VLOOKUP(A2,'C:\MyFolder\MySubFolder\[Closed WB.xlsm]Sheet 1'!$D:$E,2,FALSE),""Not Found"")"

Substitute the actual path, workbook name and worksheet name.

[YasserKhalil]

Thank you very very much Mr. Hans
Best and Kind regards
Have a nice time

[YasserKhalil]

Hello again
I have an issue in that thread ..
I need to lookup in two closed workbooks not only one as shown earlier. The scenario would be to search the first closed workbook with name "ClosedWB" and if it is found to put the result .. But if it is not found then to search another closed workbook named "ClosedWB2" and if found that is ok and the result would be put .. Finally if the search in the two closed workbooks have no results then type "Not Found"

Thanks a lot for great help

[HansV]

Something like

Code: Select all

            .Formula = "=IFERROR(VLOOKUP(A2,'C:\MyFolder\MySubFolder\[Closed WB1.xlsm]Sheet 1'!$D:$E,2,FALSE),IFERROR(VLOOKUP(A2,'C:\MyFolder\MySubFolder\[Closed WB1.xlsm]Sheet 1'!$D:$E,2,FALSE) ,IFERROR(VLOOKUP(A2,'C:\MyFolder\MySubFolder\[Closed WB2.xlsm]Sheet 1'!$D:$E,2,FALSE),"Not Found"") )"

[YasserKhalil]

That's awesome. Thanks a lot Mr. Hans

[Doc.AElstein]

Hello, a slightly late Merry New Year everyone :-)

Here is a very simple way that I use to get the tricky syntax correct for a closed workbook reference..

So, as example:
Say I have a Workbook, Workbook1.xlsm , where I need a closed reference to some range in a closed Workbook, Workbook2.xlsm ( As example, I chose arbitrarily, E3:E5 in the second worksheet of Workbook2 )


_A) Open both Workbooks:

_B) Select any spare cell ( I use arbitrarily C2 here ) in Workbook1, and type a _....
=
_....in it
Put a Equal sign in cell C2.jpg : https://imgur.com/lkCD2p7" onclick="window.open(this.href);return false;

_C) Select the required range in Workbook2. ( I chose as example, arbitrarily, E3:E5 here )
Select range In Wb2.jpg : https://imgur.com/dQKTjl6" onclick="window.open(this.href);return false;

_D) Hit Enter.

You should see something taking this sort of a form up in the Formula Bar in Workbook1:
=[Workbook2.xlsm]Shite2!$E$3:$E$5
OpnRefUpInFxBarWb1.jpg : https://imgur.com/T3CfRJV" onclick="window.open(this.href);return false;

_E) Close Workbook2
You should see that the formula in the formula bar changes to a closed reference type form, something like this:
='G:\Excel0202015Jan2016\OffenFragensForums\RCA1JorgeAllenJan\[Workbook2.xlsm]Shite2'!$E$3:$E$5
ClsdRefUpInFxBarWb1.jpg : https://imgur.com/9FiVYE6" onclick="window.open(this.href);return false;

This final formula is that which you require for a closed reference placed in Workbook1 and referring to the range E3:E5 in Workbook2

_F) Copy that formula, paste it in where you need it, and finally delete the content in Cell C2 in Workbook1


Alan

_._______________________________--

P.s.
Just out of interest:
Lets say that you wanted that closed reference across some range such as B2:B4 in the first Worksheet of Workbook1

You could do that by pasting a single formula into that range. The used single formula would need to be modified such that is was in the fixed vector relative referencing form, ( in other words, change $E$3:$E$5 to E3 )

Code: Select all

Sub PutMyTestEIn()
 Let Worksheets.Item(1).Range("B2:B4").Value = "=" & "'G:\Excel0202015Jan2016\OffenFragensForums\RCA1JorgeAllenJan\[Workbook2.xlsm]Shite2'!E3"
End Sub

( By virtue of using the fixed vector relative referencing form, the actual “seen” formulas are adjusted accordingly to reflect the relative offset position of where they are
= ……… …[Workbook2.xlsm]Shite2'!E3
= ……… …[Workbook2.xlsm]Shite2'!E4
= ……… …[Workbook2.xlsm]Shite2'!E5


I have often wondered if using that single formula “fixed vector relative referencing form” might have any advantages over this line , which one most usually sees, and which, at first glance does the same
Let Worksheets.Item(1).Range("B2:B4").FormulaArray …………\[Workbook2.xlsm]Shite2'!$E$3:$E$5"




Ref
http://www.eileenslounge.com/viewtopic.php?f=30&t=25213" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 20#p197707" onclick="window.open(this.href);return false;
https://www.excelforum.com/development- ... ost4608252" onclick="window.open(this.href);return false;
https://www.excelforum.com/development- ... ost4618975" onclick="window.open(this.href);return false;
https://www.excelforum.com/excel-progra ... ost4813760" onclick="window.open(this.href);return false;
https://www.excelforum.com/excel-progra ... ost4321006" onclick="window.open(this.href);return false;
https://exceloffthegrid.com/getting-val ... -workbook/" onclick="window.open(this.href);return false;