VBA sub to complete Vlookup on closed file

[simonphillips]

Hi Everyone,

Thanks in advance for any assistance you can provide !

I am attempting to complete a series of "vlookup" function calls across ~150 files from a single workbook WITHOUT having to open each file.

I have attempted to write a function (inserted below) that returns the value of the lookup to a cell and as inputs it has the filename and lookup params

But I suspect that the file needs to be open for this function call to work ?

Any assistance would be appreciated

Function getexternaldata(Filename As String, Filenamerange As Variant, ProjectID As Variant, Year As String, Column As Variant) As Variant

    On Error GoTo ErrHandler
    
    Application.ScreenUpdating = False
    
    Dim src As Workbook
        
    
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    Set src = Workbooks.Open(Filename, True, True)
    
    ' Vlookup the value from the SOURCE WORKBOOK.
       getexternaldata = Application.VLookup(ProjectID, Filenamerange, Column, 0)
    
    
    ' CLOSE THE SOURCE FILE.
    src.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing
    
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Function

 

[simonphillips]

Hi,

In case anyone is looking, I have managed to craft a function to do what I want, however the file is not closing. Instead it remains as a hidden file. The .close in the instruction below does not throw any exception that I can tell ? I have even tried defining a variable (wb) and assigning the open workbook and then explicitly closing !

Am I supposed to do an unload ? Is there something about the "GetObject" process I am missing ?

Any support appreciated

Option Explicit
Public Function getexternaldata(Filename As String, ProjectID As String, Column As Integer)
    ' modified version of taken from
    ' https://www.mrexcel.com/board/threads/vba-sub-to-complete-vlookup-on-closed-file.1200205/
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    Dim LookupRange As Range
    Dim wb As Workbook
    
      
    With GetObject(Filename)
        Set LookupRange = Intersect(.Sheets("PPM Data extract").Range("A:CD"), .Sheets("PPM Data extract").UsedRange)
        getexternaldata = Application.WorksheetFunction.VLookup(ProjectID, LookupRange, Column, False)
        .Close (False)
    End With
    
    Set wb = Workbooks.Item(2)
    wb.Close (False)
    
ErrHandler:
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Function