Automatically select the most latest File Name from similar names ending with dates

Automatically select the most latest File Name from similar names ending with dates

Dear Forum,

I need to use the VLOOKUP feature from the most latest files, I am using VLOOKUP in VBA code which is working fine, however the problem is that the files from where I do VLOOKUP are added randomly in a week.
Ex:

There are 2 Files, 1st File where the data would be looked up from the 2nd File, Now the 2nd File has names like this "Offline Data as on 10th May 14", "Offline Data as on 13th May 14", "Offline Data as on 23rd May 14", etc.

Now these files are send by another department, and each time the most recent data has to be searched from these files..
All of these files have to be maintained separately for record purposes.

Hence in the VLOOKUP, Lookup file keeps on changing...so is it possible to make a code in such a way that the code picks up the most recent file for reference, from the above example we would need to pull the data from the most latest file which is "Offline Data as on 23rd May 14" , if any other file is added in that folder more recent than the one mentioned than the code should automatically link it to that file.

I have managed to codify the lookup but due to this filenames being changed always I have to do this manually..as this has to be done at several columns in several sheets its extremely time-consuming.

SO if someone could help me with a logic ode to pick the most recent date which is embedded in the filename...

Thanks & Regards
e4excel

Hi..

You could also try something like this..

Change "YourfilePath" to suit..

It will put all files (sorted by modified) into an array and then display the most recent one in a message box..

Please Login or Register  to view this content.

Thanks both of you , I will look into this over the week end..

But what I need is to get the most recent file for particular like names and not any recent file...

I will come back on this...

If you want to look for the most recent file that has a filename such as "Offline Data as on the xx May".. then try this..

Change Filepath to suit..

Please Login or Register  to view this content.

Originally Posted by apo

If you want to look for the most recent file that has a filename such as "Offline Data as on the xx May".. then try this..

Change Filepath to suit..

Please Login or Register  to view this content.

Thanks a lot this works the way I want but please explain me what datatype is the variable z and what is Msgbox Z(0).

Can you also please help me get the name of the latest file as a string variable as i need to use a VLOOKUP(Lookup Value, THisSearchedFIled!Fixed Range, Col_Num, o)


Regards
e4excel

Thanks a lot this works the way I want but please explain me what datatype is the variable z and what is Msgbox Z(0).

OPen the Locals Window in the Vb Editor.. (it is in the View Menu)..

Here you will see that Z is a Variant.

The code above is placing the result from the Dir command into an array and sorting it by most recent file first. The array is zero based.. so Z(0) is the most recent file in the array. Z(1) will be the second most recent.

Thanks for the explanation , can you also please help me in getting the filename to be used in a VBA VLOOKUP..

DO I also need to open the file and then employ the VLOOKUP and then close it again.. ?

I typically use a VLOOKUP in this style..

Please Login or Register  to view this content.

Please also explain how to simply open the file and then after running the VLOOKUP close it.

Regards
e4excel

Something like this... if all files are in the 1 folder..

Change filepath to suit and add your vlookup code where indicated..

Please Login or Register  to view this content.

Dear Apo,

But I need to write the vlookup in a different file but want to extract data from this dynamically opened latest file, so still need help in tweakingthe VLOOKUP syntax...

The Latest FileName would be derived from your code but that gives the entire path..I can use an INDIRECT Function the way i always use in formulas, but I am not so good in VBA and therefore do not know how to write the code to get the name of only the file and not the entire path...

The SHeets in the latest found file are always going to remain the same and so I can simply append the code as show below..

But how to write the VLOOKUP code is still a mystery to me..

My code would be as below

Please Login or Register  to view this content.

LatesFileName should be the latest file searched from your code , but how do i write that...and I need to open the file and close the file in this

HTML Code: 

Is this right ?

Regards
e4excel

mm.. you've lost me..

Are you looking in the Locals Window to check the values of the variables as you step through the code..?

The Latest FileName would be derived from your code but that gives the entire path

That's not correct.. the array is populated by the filenames.. not including the filepath...

That is why i Had to add the filepath to the following line of code..

Please Login or Register  to view this content.

z(0) value is filename.

I would have thought that what I posted in Post #9 would have been enough for you to get over the line.. make sure you're making use of the Locals Window..

Dear Apo,

I dont know what was going earlier, maybe I was too fatigued, you were absolutely right it was working I made a mistake somewhere which I still did not figure but glad it worked...


Just one thing before I close this thread how do I check if the file to be searched is already open to move to the next step directly..

something like a BOOLEAN ..

Thanks in advance.
Regards
e4excel