M
MSCom
Neuer Benutzer
Threadstarter
- Dabei seit
- 20.09.2016
- Beiträge
- 1
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!
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!