VBA Vlookup a file

[DataBlake]

I know this has probably been answered a bajillion times but i cant find what i'm looking for on google.
I want to vlookup another workbook (only contains 1 sheet) so i have this

Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-17],[MHTqty.xlsx]Item!R1:R1048576,3,FALSE)"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

but i want the workbook portion to prompt a window to select a file. like when you need to update a reference.
sometimes it does it sometimes it doesnt.

 

[DataBlake]

To know the name of the pages of a closed book, it can be achieved in 2 ways.

The first one is opening the book.
The second is creating a connection and requires the excel version, and that is always a problem to identify the excel version.

If you do not want to open the book, then you will have to use option 2.


You can follow the following thread:
https://www.mrexcel.com/forum/excel-questions/266703-retrieve-all-sheet-names-closed-workbook.html

thanks i will go with option 2 and follow that thread

 

[DanteAmor]

You're welcome & thanks for the feedback.