Range to VLOOKUP as a Variable (range in another file)

[LuisE]

I need to do a VLOOKUP in a close file which name I'm getting from a variable
named FileToOpen using GetOpenFilename.

My problem starts when I try to add the the Sheet and the range to the
formula. The sheet name is UK and it'll be looking up from Column A to Column
E.

Here is what I have (not working obviously)

ActiveCell.FormulaR1C1 = _
'"=VLOOKUP(RC[-9]," & "[" & FileToOpen & "]" & "UK!C1:C5" & " ,5,FALSE)"

Thanks in advance

 

[Dave Peterson]

The FileToOpen & "uk!c1:c5" should be built so that it looks like:
'C:\My Documents\excel\[book1.xls]UK'!C1:C5

Option Explicit
Sub testme()

Dim FileToOpen As Variant
Dim myFolderName As String
Dim myFileName As String
Dim LastBackSlashPos As Long
FileToOpen = Application.GetOpenFilename
If FileToOpen = False Then
'user hit cancel
Exit Sub '??
End If

LastBackSlashPos = InStrRev(FileToOpen, "\", -1, vbTextCompare)

If LastBackSlashPos = 0 Then
'no backslashes, what the heck happened???
Else
myFolderName = Left(FileToOpen, LastBackSlashPos)
myFileName = Mid(FileToOpen, LastBackSlashPos + 1)
End If

' shooting for: 'C:\My Documents\excel\[book1.xls]UK'!C1:C5
ActiveCell.FormulaR1C1 _
= "=VLOOKUP(RC[-9],'" & myFolderName & "[" & myFileName & "]" _
& "UK'!C1:C5" & " ,5,FALSE)"

End Sub

I need to do a VLOOKUP in a close file which name I'm getting from a variable
named FileToOpen using GetOpenFilename.

My problem starts when I try to add the the Sheet and the range to the
formula. The sheet name is UK and it'll be looking up from Column A to Column
E.

Here is what I have (not working obviously)

ActiveCell.FormulaR1C1 = _
'"=VLOOKUP(RC[-9]," & "[" & FileToOpen & "]" & "UK!C1:C5" & " ,5,FALSE)"

Thanks in advance

 

[LuisE]

Dave,

Thanks a lot, works like a charm
Actually I was hoping that you read the posting, I read your related ones
but couldn't find anything for the file as a variable.
Is it the only was? I was trying to get the path as a string out of the
GetFile... but I couldn't
Thanks again.

The FileToOpen & "uk!c1:c5" should be built so that it looks like:
'C:\My Documents\excel\[book1.xls]UK'!C1:C5

Option Explicit
Sub testme()

Dim FileToOpen As Variant
Dim myFolderName As String
Dim myFileName As String
Dim LastBackSlashPos As Long
FileToOpen = Application.GetOpenFilename
If FileToOpen = False Then
'user hit cancel
Exit Sub '??
End If

LastBackSlashPos = InStrRev(FileToOpen, "\", -1, vbTextCompare)

If LastBackSlashPos = 0 Then
'no backslashes, what the heck happened???
Else
myFolderName = Left(FileToOpen, LastBackSlashPos)
myFileName = Mid(FileToOpen, LastBackSlashPos + 1)
End If

' shooting for: 'C:\My Documents\excel\[book1.xls]UK'!C1:C5
ActiveCell.FormulaR1C1 _
= "=VLOOKUP(RC[-9],'" & myFolderName & "[" & myFileName & "]" _
& "UK'!C1:C5" & " ,5,FALSE)"

End Sub

I need to do a VLOOKUP in a close file which name I'm getting from a variable
named FileToOpen using GetOpenFilename.

My problem starts when I try to add the the Sheet and the range to the
formula. The sheet name is UK and it'll be looking up from Column A to Column
E.

Here is what I have (not working obviously)

ActiveCell.FormulaR1C1 = _
'"=VLOOKUP(RC[-9]," & "[" & FileToOpen & "]" & "UK!C1:C5" & " ,5,FALSE)"

Thanks in advance

 

[Dave Peterson]

There are lots of ways to skin a cat <ouch>!

In fact, if you were using a version of excel before xl2k, then instrrev
wouldn't be available. You could loop backwards looking for a backslash.

Dim iCtr as long
for ictr = len(filetoopen) to 1 step -1
if mid(filetoopen,ictr,1) = "\" then
'found it
myFoldername = left(filetoopen,ictr)
myfilename = mid(filetoopen,ictr+1)
exit for 'stop looking for more!
end if
next ictr

.....

(Untested, uncompiled. Watch for typos.)

Dave,

Thanks a lot, works like a charm
Actually I was hoping that you read the posting, I read your related ones
but couldn't find anything for the file as a variable.
Is it the only was? I was trying to get the path as a string out of the
GetFile... but I couldn't
Thanks again.

The FileToOpen & "uk!c1:c5" should be built so that it looks like:
'C:\My Documents\excel\[book1.xls]UK'!C1:C5

Option Explicit
Sub testme()

Dim FileToOpen As Variant
Dim myFolderName As String
Dim myFileName As String
Dim LastBackSlashPos As Long
FileToOpen = Application.GetOpenFilename
If FileToOpen = False Then
'user hit cancel
Exit Sub '??
End If

LastBackSlashPos = InStrRev(FileToOpen, "\", -1, vbTextCompare)

If LastBackSlashPos = 0 Then
'no backslashes, what the heck happened???
Else
myFolderName = Left(FileToOpen, LastBackSlashPos)
myFileName = Mid(FileToOpen, LastBackSlashPos + 1)
End If

' shooting for: 'C:\My Documents\excel\[book1.xls]UK'!C1:C5
ActiveCell.FormulaR1C1 _
= "=VLOOKUP(RC[-9],'" & myFolderName & "[" & myFileName & "]" _
& "UK'!C1:C5" & " ,5,FALSE)"

End Sub

I need to do a VLOOKUP in a close file which name I'm getting from a variable
named FileToOpen using GetOpenFilename.

My problem starts when I try to add the the Sheet and the range to the
formula. The sheet name is UK and it'll be looking up from Column A to Column
E.

Here is what I have (not working obviously)

ActiveCell.FormulaR1C1 = _
'"=VLOOKUP(RC[-9]," & "[" & FileToOpen & "]" & "UK!C1:C5" & " ,5,FALSE)"

Thanks in advance