vlookup with closed workbook

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hello,
The following formula works fine :
=VLOOKUP(A1,'E:\Donnees\Daniel\mpfe\test.xls'!liste,2,FALSE)
Is it possible to do the same with VBA, the test.xls workbook being closed ?
Thanks in advance.
Daniel
 
Can you use a helper cell in a that worksheet?

If yes, you could plop the formula into that cell and get the value. This
worked ok for me.

Option Explicit
Sub testme02()
Dim HelpCell As Range
Dim myVal As Variant

With ActiveSheet
Set HelpCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
With HelpCell
.Formula _
= "=VLOOKUP(A1,'C:\My Documents\excel\" _
& "[Book2.xls]Sheet1'!$A:$B,2,FALSE)"
myVal = .Value
.ClearContents
End With
End With

If IsError(myVal) Then
MsgBox "error!"
Else
MsgBox myVal
End If

End Sub
 
Thanks for all.
Daniel
Dave Peterson said:
Can you use a helper cell in a that worksheet?

If yes, you could plop the formula into that cell and get the value. This
worked ok for me.

Option Explicit
Sub testme02()
Dim HelpCell As Range
Dim myVal As Variant

With ActiveSheet
Set HelpCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
With HelpCell
.Formula _
= "=VLOOKUP(A1,'C:\My Documents\excel\" _
& "[Book2.xls]Sheet1'!$A:$B,2,FALSE)"
myVal = .Value
.ClearContents
End With
End With

If IsError(myVal) Then
MsgBox "error!"
Else
MsgBox myVal
End If

End Sub


Hello,
The following formula works fine :
=VLOOKUP(A1,'E:\Donnees\Daniel\mpfe\test.xls'!liste,2,FALSE)
Is it possible to do the same with VBA, the test.xls workbook being
closed ?
Thanks in advance.
Daniel
Click to expand...
Click to expand...
 
Back
Top