Finding file name by numerical order

[FRIEL]

Hi all

I have an excel sheet that will create a standard filing system.

in a folder there are folders numbered like this:

"ENQ 0123, ENQ 0124" and go up 1 digit at a time.

there are hundereds in this folder.
I would just like to know if there is a quick way of finding the latest one so i can then tell my code to generate the next one.

[FRIEL]

I have cross posted but cant post links yet

[Simon Lloyd]

Crosspost is here http://www.mrexcel.com/forum/excel-q...cal-order.html

[FRIEL]

cheers.

its looking like im going to have to use a loop if i want to do this.

i wasnt keen on this because of the potential runtime.

[snb]

Adapt the foldername 'G:\OF\'

[VBA]Sub laatst_aangemaakte_map_snb()
MsgBox Split(CreateObject("wscript.shell").exec("cmd /c Dir G:\OF\*. /b /o-d").stdout.readall, vbCrLf)(0)
End Sub[/VBA]

[GTO]

If the filename pattern is consistent, maybe:
[VBA]Option Explicit

Sub example()
MsgBox NextNumber(ThisWorkbook.Path)
End Sub

Function NextNumber(Path As String) As Long
Dim File As Object
Dim lTmp As Long

With CreateObject("Scripting.FileSystemObject").GetFolder(ThisWorkbook.Path)
For Each File In .Files
If Mid(File.Name, InStrRev(File.Name, ".")) Like ".xls*" _
And Left(File.Name, InStrRev(File.Name, ".") - 1) Like "ENQ ####" Then
lTmp = CLng(Split(Left(File.Name, InStrRev(File.Name, ".") - 1), Chr(32))(1))
If lTmp > NextNumber Then NextNumber = lTmp
End If
Next
End With
End Function[/VBA]
Hope that helps,

Mark

[FRIEL]

this look promicing mark

however this seems to be looking at files im talking folders.
is there a way i could adapt it?

[Simon Lloyd]

There's a tutorial here http://www.thecodecage.com/forumz/vi...lesystemobject

[FRIEL]

thanks ill have a look into that.

looks like its somthing totaly new to me.

[GTO]

...in a folder there are folders numbered like this:

"ENQ 0123, ENQ 0124" and go up 1 digit at a time...

ACK! Sorry about that, and thank you for being so gracious in your correction. Your question was clear, my eyes were not

Maybe:
[VBA]Sub example()
MsgBox NextFolderNumber(ThisWorkbook.Path)
End Sub

Function NextFolderNumber(Path As String) As Long
Dim Folder As Object
Dim lTmp As Long

With CreateObject("Scripting.FileSystemObject")
If .FolderExists(Path) Then
For Each Folder In .GetFolder(Path).SubFolders
If Folder.Name Like "ENQ ####" Then
lTmp = CLng(Right(Folder.Name, 4))
If lTmp > NextFolderNumber Then NextFolderNumber = lTmp
End If
Next
End If
End With
End Function[/VBA]