Use data from another file

Hello,

I have an excel time sheets for every day of the week, what I am trying to make is a weekly time sheet that uses the data from single day entries. Right now I can get it to work by using

A5=John

=VLOOKUP(A5&" Total",[06.17.09.xls]Sheet1!B1:[06.17.09.xls]Sheet1!D99,3,FALSE)

it returns time that John worked on 06.17.09
~this works but 06.17.09.xls has to be opened at the time (is there a workaround)

my problem is that I have to change the date for every day that I want, but what I want to do is have a cell with the date, like

C4=06.17.09

VLOOKUP(..........[--C4--.xls].......)

Basically I want excel to generate the file name, not be manually inputted

Thank you in advance

So is there a way to do that? or will I have to merge it all into one file?