Vlook is not working using VBScript

Hello,

I need some help in Vlookup using two excel sheets. The code works fine if I copy the excel files on the same location of the vbScript but if I put the excel sheets to a different location, it does not work. Looks like I'm failing to specify the path in the lookup function and hence it is failing to compare. Here is the code:

'################################################
On error resume next

Set obj = CreateObject("wscript.shell")
strFilePath1 = obj.CurrentDirectory & "\Temp\ALL Users1.xlsx"
strFilePath2 = obj.CurrentDirectory & "\Output\ALL Users2.xlsx"

Set oXL = CreateObject("Excel.Application")
Set oWB1 = oXL.Workbooks.Open(strFilePath1)
Set oWB2 = oXL.Workbooks.Open(strFilePath2)

oXL.Visible = false

Set oS2 = oWB2.Worksheets("Great") ' using sheet # 2 named as Great
Set oS1 = oWB1.Worksheets("Cool") ' using sheet # 2 named as Cool

oS2.Columns(6).Insert
oS2.Cells(1,6) = "Number"
oS2.Cells(1,6).Font.Bold = True

oS2.Range("F2:F" & oS2.UsedRange.Rows.Count).Formula = "=VLOOKUP(C2, ' [strFilePath1] oS1 ' ! $A:$B,2,FALSE) "
' it cannot recognize strFilePath1 as path and oS1 as worksheet as above

oS2.Range("F2:F" & oS2.UsedRange.Rows.Count).Formula = oS2.Range("F2:F" & oS2.UsedRange.Rows.Count).Value

oWB1.Close
oWB2.Save
oWB2.Close
oXL.Quit

Basically need a way so that the lookup function can understand the path to do the comparing. Would appreciate your help.

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Excel.PNG","data-attachmentid":1208922}[/ATTACH]

Hi Carim,

You are correct. If I manually define the exact path it works. But the script will go to different users (C:\Users\%username%\Desktop\ScriptFolderName) and based on the folder location \Temp & \Output will be inside the parent folder. Problem seems to be in =VLOOKUP(C2, ' [strFilePath1] oS1, it is not detecting strFilePath1 & oS1 as fileObject.

Hence need some assistance how to call this so that the vlookup knows this is a file not a path of a file.

Make sense?

Hi Carim,

String doesn't work as the formula requires a file object.

Hi Carim,

Thank you so much for your help. I tied but didn't work. I think we need to find the correct format to concatenate.