Vlookup using VBA Constants

[seahawk56]

I want to use a VLOOKUP formula to pull data from additional workbooks. I would like to use constants/dimensions in VBA to reference the “table_array” in the formula. The pathways will always stay the same but the name will change to the date. The formula will bring back the data but I have to select the workbook each time. Is there any helpful hints to make it work? Or is this a limitation of the VLOOKUP?
Formula:
"=IF(ISERROR(VLOOKUP($P120,MyStaff1!MonProd,3,FALSE)),0,VLOOKUP($P120,MyStaff1!MonProd,3,FALSE))"

Public Const MyFile As String = "OPLdailyworkplanning"
Public Const MyLoc As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\OPL\Planned Work\"
Public Const MyProdFile As String = "ProdTrack"
Public Const MyProdLoc As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\OPL\Production Tracking\"
Public Const MyTempLoc As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\OPL\Production Tracking\Templates"
Public Const MyTempFile As String = "ProdTrack_Template.xls"
StaffName1 = ActiveSheet.Range("A3").Value
MyWeek = " " & MyProdFile & "_" & ConvertToJulian(ActiveSheet.Range("D1").Value) & "_" & ConvertToJulian(ActiveSheet.Range("E1").Value) & ".xls"
MyStaff1 = MyProdLoc & StaffName1 & "\" & StaffName1 & MyWeek

 

[xld]

I don't know what that code has to do with VLOOKUP, but it is straightforward, use

    If IsError Application.VLOOKUP(Range("P120").Value,Workbooks("thatbook.xls").Worksheets("thatSheet").Range("M1:P50"),3,False) Then

        myVal = ""
Else

        myVal = Application.VLOOKUP(Range("P120").Value,Workbooks("thatbook.xls").Worksheets("thatSheet").Range("M1:P50"),3,False)
End If