Help needed VB code - file name with dates

[chinna]

Hi all,

I am new to this Discussion Board, hope I can get some help here. I am new to Excel/VBA, I am facing a problem.

Description of the Macro's functionality : We recieve a file on daily basis, by clubbing information on this file with another one we prepare a report.

Say we recieve file 1, which will be named jlm0324.xls, we have to do some Vlookup and join it with another file which is saved as kkd.xls (this file name never changes, so no worries).

Is there a way that I can make the Macro smart enough to pick up the file with latest date saved in the folder?

Not only this, in the report we have to Copy/paste some information from report prepared from prior day, as such the macro i have now shows "Active window" in the code and the numbers keep changing if the report is opened along with someother spreadsheet.

Any help in this matter would be greatly appreciated. TIA

[Trevor]

the eaiest way would be to save all the files in the same folder by date and then pull that file out or you can create a fso(file scripting object and use a class proportie to pull the file by the date created( I have found that if you manualy place a file in a directory the date created is the date it was created, but if it is done with automation, the file created date may change to the date the file was placed in that folder/directory

[chinna]

Trevor,

Thanks for the help.

The files are saved in one folder, my Macro is recorded with Path/File Name(With Date extension), is there a way that I can make this filename to be variable (Changeable) say by adding a "calendar" to the spreadsheet where user can select the Date of the file he wants to pick.

[Trevor]

you could place a self made or " custom" find dialog box for the user to input the date and have the macro pull the date from that texbox or excel cell

[chinna]

I have made a custom Calendar, but I am having trouble connecting the cell (containing date) to the filename in macro.

Say I have date in Cell "B4", how can i connect that to a filename where the path looks something like this

sharedrive08\data08\combined08\march-2008\file0325

In the above path, I just want to replace 0325 with a new date, is this possible?

[ProteanBeing]

Yes.
Use the value from the calander as so:
[vba]dim CalanderResults as date
dim TheFile as string

' get Calander info

TheFile = "sharedrive08\data08\combined08\march-2008\file" + format(CalanderResults, "mmdd")
[/vba]

[chinna]

Thanks for the help ProteanBeing