Find workbook name containing highest number in the middle of the name

  • Hey,

    I want to have a macro find, in a specific folder, the name of the workbook containing the highest 5-digit code “in the middle” of the file name. (And then make a copy of that workbook and do stuff with it, but that part is a future problem). The folder contains maximumly some 500 files (out of which some 250 are Excels), as files are archived regularly.

    The beginning of the .xlsm files' names' in that folder follows a pattern: a date string (YYYYMMDD) followed by space, dash, space and a 5-digit code, like:

    20200705 – 16227.xlsm

    For the ending part, there are some variations, like:

    20200705 – 16227 - copy.xlsm

    20200705 – 16227_2.xlsm

    The 5-digit code gets assigned an increment >1 for each new workbook created, but exactly what that increment will be is unpredictable. So, there may or may not be multiple workbooks with the same YYYYMMDD but they have different 5-digit codes.

    A solution to a problem very similar to mine in another forum (maximizing the first 3 digits of the file name) is below:

    ... which I can run errorlessly and I get "20200". I'm not actually sure if it works as intended, because as I try to increase the amount of digits to maximize beyond 5 I get an Overflow error, but it looks promising. Anyway, I figured I could just utilize Split to meet my needs:

    1. For Each File In Folder.Files
    2. If IsNumeric(Left(Split(File.Name(1), " - "), 5)) And FileExt(File.Name) = Extension Then
    3. GetNextFileNum = IIf(CInt(Left(Split(File.Name(1), " - "), 5)) > _
    4.                 GetNextFileNum, CInt(Left(Split(File.Name(1), " - "), 5)), GetNextFileNum)
    5. End If
    6. Next File

    … and then get the Dir of FileName. But I get error “Property let procedure not defined and property get procedure did not return an object (Error 451)” on the first line containing Split. Maybe I cannot use Split on an Object? Any workarounds come to mind?

    I'm about to tear my hair off so any input would be greatly appreciated.



  • I don't have a Folder to try this on but you have it setup to loop through a Folder.

    Change to loop through your Folder instead of a Range as in this example.

  • I have very slim knowledge on arrays so got to read up a bit. As a side effect I also had some epiphanies on object orientation – boy am I glad you did not include much commenting in your reply! And thanks for that code – very interesting!

    After some hours of struggling I understand the logic down to line 9. Ran it on some test data in a sheet and got the desired result. Curious why you chose to write to Cells(1,16).Resize(,2). I was stuck on this for quite a while since 16 is the character amount of e.g. “20200715 - 12345”, but that’s just coincidence, right?

    As for modifying for Range -> Folder. I guess I will need to deal with files as Objects (not Long) and use Scripting.FileSystemObject?

    Also, from running the test, I noticed I had a bad criteria before, since there may be multiple files with same 5digit code (“…_2.xlsm” or “… - copy.xlsm”). To fix, I guess (?) :

    1. If Left(Trim(Split(MyFile.Name, Chr(45))(1)), 5) > arr1(1, 1) _
    2. And Len(Trim(MyFile.Name) = 19) Then 'or 14 if ".xlsm" does not count
  • Beautiful jolivanes - you teached me a lot and saved some grey hairs!