VBA Lookup value in another workbook

[Dani_LobP]

Hi,

I'm struggling to find the way in how to use xlookup function, to obtain data from another workbook, but without using the workbook name/path.
I want to avoid using the name of the workbook and the path, because its going to be changing each day both name and location, earlier in the macro i am actually opening the file manually by selecting it with a pop up box, and name it WB2. Having eventually both main file, and the one just opened.

Below code I am using:

After opening the workbook from where i want to obtain the data I name it WB2.

Set WB2 = ActiveWorkbook

then in the main file where i want to bring the data i do as below:

Range("AD2").Select
ActiveCell.FormulaR1C1 = _
    "=XLOOKUP(RC[-29],'[FileNameWithDataIWant.xlsx]Sheet1'!$A:$A,'[FileNameWithDataIWant.xlsx.xlsx]Sheet1'!$T:$T," ")"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD" & LastRow)

*LastRow was defined earlier in the macro.

The idea I would like, is to change FileNameWithDataIWant.xlsx into WB2... but I am not sure how to make the change.. :D
Not sure if its possible thou..

Also, for some reason when I try this manually, I am getting in some cells number 0 result, even thou its an empty cell in the original file.. does it make sense?
Lookup is done in order to transfer some comments from a file, to a new one, so not all rows will have data.

Thanks in advance!
Regards,
Dani

 

[Micron]

If you're asking how to get the file path into the code dynamically, consider using msoFileDialogFilePicker to return the path string to a variable in your code. As for the zeros problem, sorry but I have no idea about that.

 

[Dani_LobP]

If you're asking how to get the file path into the code dynamically, consider using msoFileDialogFilePicker to return the path string to a variable in your code. As for the zeros problem, sorry but I have no idea about that.

Its more about, how to Set a workbook that i open, with a Value, and then, when i use the formula, input the Value set to the workbook, instead of the workbook name.
That way, doesn't matter what name workbook has, that it will be tied to the value i assign with the Set..

Hope it makes sense now.

 

[Micron]

After opening the workbook from where i want to obtain the data I name it WB2.

You're not naming it WB2 - you're putting the workbook into an object variable. However, it will have a Name property, so that should be helpful. Try replacing your workbook name in code with WB2.Name. You might have to concatenate that with quotation marks. Sorry if I still am not getting it completely. My understanding is that you can open the wb and can set the variable to that workbook - you just need to know how to use the name of that wb.

 

[Dani_LobP]

Not sure if I follow what you mean. With what you said, I try do as below, but seems to be wrong somehow. I am not very advanced with VBA to be honest.

Range("AD2").Formula = "=XLOOKUP((Range(A2),'[" & WB2.Name & "]Sheet1'!A:A,'[" & WB2.Name & "]Sheet1'!N:N,"" "")"

So A2 is where lookup value is, column A in sheet1 is where should be found, and column N what should give back. If nothing, then will be left empty...

Hope it makes sense what i try explain that i am trying to do.

 

[Micron]

You seem to be having 2 or more issues here. If you're saying one of those issues is that the lookup isn't returning what you want, I cannot help with that because my knowledge of Excel formulae is quite weak. My answer was for how to get the name of the workbook when you set WB2 to the workbook you opened. It's not clear to me if that is working or not. If you're not sure, you could add Debug.Print WB2.Name right after this line
Set WB2 = ActiveWorkbook
and see what prints out in the immediate window. If you see the workbook name you want to see, then using WB2.Name should solve your problem about the workbook name being different each time.