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.

 

[flora1]

So you need to do a vlookup on the closed workbooks?

 

[DataBlake]

So you need to do a vlookup on the closed workbooks?

yes
the lookup column won't change but the workbook name and where it is might.

 

[DanteAmor]

Try this.
Note: The book you select must have a sheet called "item"

Sub test2()
    Dim wfile As String, wdiag As Long, wpath As String, wbook As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Pick a excel file"
        .Filters.Add "Excel Files", "*.xls*"
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path & "\"
        If Not .Show Then Exit Sub
        wfile = .SelectedItems.Item(1)
        wdiag = InStrRev(wfile, "\")
        wpath = Left(wfile, wdiag)
        wbook = Mid(wfile, wdiag + 1)
    End With
    Range("R2").FormulaR1C1 = "=VLOOKUP(RC[-17],'" & wpath & "[" & wbook & "][COLOR=#ff0000]item[/COLOR]'!C1:C3,3,0)"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
End Sub

 

[DataBlake]

Try this.
Note: The book you select must have a sheet called "item"

Sub test2()
    Dim wfile As String, wdiag As Long, wpath As String, wbook As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Pick a excel file"
        .Filters.Add "Excel Files", "*.xls*"
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path & "\"
        If Not .Show Then Exit Sub
        wfile = .SelectedItems.Item(1)
        wdiag = InStrRev(wfile, "\")
        wpath = Left(wfile, wdiag)
        wbook = Mid(wfile, wdiag + 1)
    End With
    Range("R2").FormulaR1C1 = "=VLOOKUP(RC[-17],'" & wpath & "[" & wbook & "][COLOR=#ff0000]item[/COLOR]'!C1:C3,3,0)"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
End Sub

AWESOME, you are amazing.
now if i need this same thing but the sheet name is something other than item can i just replace every "item" in this code with the sheet name?

 

[DanteAmor]

It is correct, only change "item" by the name of the sheet. And try.

 

[DataBlake]

It is correct, only change "item" by the name of the sheet. And try.

actually is there syntax similar to the "wbook" that looks at the first sheet despite its name and despite its file type? the only thing that won't change will be the column lookup.

 

[DanteAmor]

I do not understand. What do you need?

 

[DataBlake]

I do not understand. What do you need?

I want the file dialog box to view XLS, CSV, XLSM (all file types), and the vlookup to read the first sheet of whatever workbook i select in the dialogue box

Sub test2()
    Dim wfile As String, wdiag As Long, wpath As String, wbook As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Pick a excel file"
        .Filters.Add "Excel Files", "*.all*"
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path & "\"
        If Not .Show Then Exit Sub
        wfile = .SelectedItems.Item(1)
        wdiag = InStrRev(wfile, "\")
        wpath = Left(wfile, wdiag)
        wbook = Mid(wfile, wdiag + 1)
    End With
    Range("R2").FormulaR1C1 = "=VLOOKUP(RC[-17],'" & wpath & "[" & wbook & "][COLOR=#ff0000]FirstSheet[/COLOR]'!C1:C3,3,0)"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
End Sub

something like that?

 

[DanteAmor]

I want the file dialog box to view XLS, CSV, XLSM (all file types), and the vlookup to read the first sheet of whatever workbook i select in the dialogue box

something like that?

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