Using Vlookup on other Workbook whichs path is stored in a Cell

[MSCom]

Good Day

Im Trying to do Vlookup on a closed Workbook whichs path is stored in a Cell.

For that im writing a Function which opens the Workbook by the path given, and then does the VLookup on a predefined range in the newly opened Workbook. Somehow my function doesnt work and i cant find out why.

Heres the function:

PosType is the Value to lookup in VLookup


Function getPos(PosType As String, FilePath As String) As String



Dim FileName As String


FileName = GetFilenameFromPath(FilePath)

Workbooks.Open FilePath





getPos = Application.VLookup(PosType, Workbooks(FileName).Worksheets("Sheet0").Range("A1:B50").Value, 2, False)



End Function


GetFilenameFromPath() returns only the Filename ("example.xlsx") like so:


Function GetFilenameFromPath(ByVal strPath As String) As String

' Returns the rightmost characters of a string upto but not including the rightmost '\'

' e.g. 'c:\winnt\win.ini' returns 'win.ini'



If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then

GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)

End If

End Function


I have been trying to solve this for hours now but i just cant seem to find whats wrong with my code. Thanks for your help!