Ideifying Latest File using string name...

[peterg999]

Hi,
Hoping someone can help me, I'm wanting to write a VBA Macro that identifies the latest filename based on FILENAME, and the lsuffix of that filename... so, in the example below, the answers would:

Q001035 - Peter G - 0002.pdf, and the suffix would be 0002

I am not wanting to obtain the latest filename based on modified date. Can anyone please help me. Peter



Q001035 - Peter G - 0001.pdf
Q001035 - Peter G - 0002.pdf

[dflak]

Well, if you don't mind an array formula: =MAX(1*MID($A$3:$A$5,FIND("-",$A$3:$A$5,FIND("-",$A$3:$A$5)+1)+1,LEN($A$3:$A$5)-FIND("-",$A$3:$A$5,FIND("-",$A$3:$A$5)+1)-4))

Where A3:A5 contains the list of file names assuming they are all *** - *** - nnnn.pdf in format.

Attached is a file with the helper columns that helped me get to this formula.

[peterg999]

Hi dflak,

Much appreciated for this... Can this be written in VBA code, and then let it loop to ensure it goes for all files?

Peter

[Akuini]

Hi, peter
Try this, assuming the list in column A, the result will be in C & D.
First, you have to sort ascending column A then run macro.

Please Login or Register  to view this content.

[peterg999]

Akuini,
Thanks for your help... I think I have not conveyed the right message to you and this is my fault... these files are all in the same directory and sit as ".pdf". Can the macro be re-written to search in a directory, and then look for the oldest file based on filename.

Peter

[Akuini]

Ok, try this (Assuming no pdf files with name shorter then 9 character in the directory)
Open a new sheet
Run the macro
Select the folder

Please Login or Register  to view this content.

[peterg999]

Akuini

Your help is much appreciated.

Peter

[Akuini]

You're welcome Peter & thanks for replying.