vlookup with closed workbook

[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

 

[Dave Peterson]

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

 

[Daniel]

Thanks for all.
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

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