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:
- Option Explicit
- Public Sub Example()
- Const FolderPath = "Z:\RETAIL\STOCK MOVEMENT FORMS\"
- Const FileExt = "xlsx"
- Debug.Print GetNextFileNum(FolderPath, FileExt)
- End Sub
- Public Function GetNextFileNum(ByVal TargetFolder As String, ByVal Extension As String) As Integer
- Dim Folder As Object, File As Object
- With CreateObject("Scripting.FileSystemObject")
- Set Folder = .GetFolder(TargetFolder)
- For Each File In Folder.Files
- If IsNumeric(Left(File.Name, 5)) And FileExt(File.Name) = Extension Then
- GetNextFileNum = IIf(CInt(Left(File.Name, 5)) > _
- GetNextFileNum, CInt(Left(File.Name, 5)), GetNextFileNum)
- End If
- Next File
- End With
- Set File = Nothing
- Set Folder = Nothing
- End Function
- Public Function FileExt(ByVal Path As String) As String
- With CreateObject("Scripting.FileSystemObject"): FileExt = .GetExtensionName(Path): End With
- End Function
... 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:
… 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.