excel - VBA vlookup formula error -

i newbie in excel macro vba. have problem on vlookup code refers workbook selected user.

here's code:

private sub vlookups()  dim data_file_new string dim integer dim string, b string, path string  data_file_new = cstr(application.getopenfilename(filefilter:="excel workbooks (*.xls*),*.xls*", title:="select new data file vlookup"))  path = data_file_new  = "=vlookup(a:a,'[" & path & "]source'!$a:$ab,28,0)" b = "=vlookup(a:a,'[" & path & "]source'!$a:$aj,36,0)" = 7  until sheets("macro template").cells(i, 1) = ""      sheets("macro template").cells(i, 37) =     sheets("macro template").cells(i, 38) = b      = + 1 loop  end sub 

my problem code doesn't give correct formula vlookup. instead, gives formula:

=vlookup(a:a,'[e:\ap no approval\[no approval monitoring log_june 2015 xlsx.xlsx]source]no approval monitoring log_june'!$a:$ab,28,0) 

the correct formula this:

=vlookup(a:a,'e:\ap no approval\[no approval monitoring log_june 2015 xlsx.xlsx]source'!$a:$ab,28,0) 

any appreciated.

thanks!

try (untested)

private sub vlookups()     dim ws worksheet     dim lrow long     dim spath string, sfile string, stemp string     dim ret      set ws = thisworkbook.sheets("macro template")      ret = application.getopenfilename(filefilter:="excel workbooks (*.xls*),*.xls*", _                                       title:="select new data file vlookup")      if ret = false exit sub      sfile = getfilenamefrompath(ret)     spath = split(ret, sfile)(0)     stemp = "=vlookup(a:a,'" & spath & "[" & sfile      ws         lrow = .range("a" & .rows.count).end(xlup).row          .range("ak7:ak" & lrow).formula = stemp & "]source'!$a:$ab,28,0)"         .range("al7:al" & lrow).formula = stemp & "]source'!$a:$aj,36,0)"     end end sub  public function getfilenamefrompath(byval strpath string) string     if right$(strpath, 1) <> "\" , len(strpath) > 0         getfilenamefrompath = _         getfilenamefrompath(left$(strpath, len(strpath) - 1)) + right$(strpath, 1)     end if end function 

explanation:

1. application.getopenfilename() returns variant. handle shown in code above.

2. the formula looking =vlookup(a:a,'e:\ap no approval\[no approval monitoring log_june 2015 xlsx.xlsx]source'!$a:$ab,28,0) , ret give straight file path , name e:\ap no approval\no approval monitoring log_june 2015 xlsx.xlsx. vlookup puts [] around file name. have first extract filename file path , reconstruct entire string. use function getfilenamefrompath in above code retrieve that.

3. you don't need loop cells enter formula. can enter formula in 1 go in entire range.