excel - Find last filled row without opening file and with conditions -

what wish :

i.   locate excel document using path , doc name √ ii.  obtain last filled row’s number spreadsheet name without opening file x  iii. obtain last filled row's number before row number, x      ex: last filled row before row 40. there may filled cells between 45-52,       function return "32" , not "52" me   

what have:

the path file classic directory + file name, written in 2 separate cells in spreadsheet has macro. it's irrelevant, show file_path = path ...

file_path = chr(34) & server & "\" & range(workbook_loc).value  'that's step 1 

in file located @ file_path, wish go in spreadsheet named month year (ex: file_path("july 2015")) , find last filled row's number. in 3rd line, try fill cell last row's value in b. know there lot online this, can't work somehow...:

set wb = workbooks.open(file_path) ' should open workbook lastrow = wb(chr(34) & month & " " & year & chr(34)).cells(1000, col).end(xlup).row  range(currentvalue_loc).formula = "=index('" & server & "\" & workbook & month & " " & year & "'!b" & lastrow & ",1)" 

the previous code stops @ second line ; opens workbook located @ file_path , gives me following error:

run-time error '438' object doesn't support property method 

what doing wrong?

finally, iii. point (see top example), tried this:

    lastrow = wb(chr(34) & month & " " & year & chr(34)).cells(40, col).end(xlup).row  

but still gives me last filled row, if after row40

thank you!

you not have type name of workbook again, when attempting find last rows. use workbook variable, have set.

sub lastrow()     dim lastrow long      lastrow = wb.sheets(1).cells(rows.count, 2).end(xlup).row     lastrow = wb.sheets(1).cells(40, 2).end(xlup).row end sub 

just use variable have declared, , sheets() method define sheet you're finding last row on. in example above finding last row on first worksheet in workbook.