How to use vlookup function in VBA by pointing table array from a closed source workbook?

Question

I'm trying to set a public workbook variable behind a command button on UserForm to use later in a module just to read data from it as source workbook with vlookup in VBA. I could not find a way to make this without opening the file, so I used below and then closed the file;

Set wb = Application.Workbooks.Open(filepath, UpdateLinks:=False)
wb.Close SaveChanges:=False

Also in one module I set variable as public as below;

Public wb As Workbook

But I noticed that I think because I close source workbook after setting, when VBA reaches to code line in module which is looking for already set source workbook, it gives error in vlookup function.

Actually, I never want source file to be opened. Because file is so big and open/close operations takes a lot of time. Data can be read in other file by vlookup etc. formula without opening the source but I need to do this via VBA. As result, is it possible to use a source workbook to get data from it without opening with application.vlookup?

So the real question is: "As result, is it possible to use a source workbook to get data from it without opening?" The answer is yes, if they not protected, you can look into ExcecuteExcel4Macro — JvdV
yes I want to get data but I need to do it via vlookup in vba module, so the problem is that I cannot point source file for search array in vlookup function while source file is closed. — Ibrahim Bayram
As said in my other comment I think you need to add more details. One idea would be to use ADODB in order to draw data from your worksheet. But for this you need to provide more information on the structure of the source worksheet and what you want. — Storax
Actually what I want to do is very simple but unfortunately could not succeed to run a vlookup in vba to pull data from a 'closed' book.. think that source book has only a sheet with first 2 columns including data, and in my original book I look for value in first column within first column of source book to get corresponding data from second column of source book while it is closed. If it was open, it would not be an issue but now does not seem possible :( for ADODB, first time I heard this, have no idea how to do :( — Ibrahim Bayram

Storax Storax · Accepted Answer · 2019-12-11T12:25:11

Try to use this function

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
Dim arg As String
    '   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
    '   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
          Range(ref).Range("A1").Address(, , xlR1C1)
    '   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

And a test

Sub TestIt()

    Const dataDir = "C:\Users\Me\Documents\"
    Const fileName = "MyReport.xlsx"
    Const sheetName = "Sheet1"
    Const cellRef = "A2"

    Debug.Print GetValue(dataDir, fileName, sheetName, cellRef)

End Sub

How to use vlookup function in VBA by pointing table array from a closed source workbook?

1 Answers