PDA

View Full Version : Finding file name by numerical order



FRIEL
09-12-2012, 02:12 AM
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
09-12-2012, 02:12 AM
I have cross posted but cant post links yet :(

Simon Lloyd
09-12-2012, 02:23 AM
Crosspost is here http://www.mrexcel.com/forum/excel-questions/658749-finding-file-name-numerical-order.html

FRIEL
09-12-2012, 02:32 AM
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
09-12-2012, 03:46 AM
Adapt the foldername 'G:\OF\'

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

GTO
09-12-2012, 05:27 AM
If the filename pattern is consistent, maybe:
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
Hope that helps,

Mark

FRIEL
09-12-2012, 06:06 AM
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
09-12-2012, 06:36 AM
There's a tutorial here http://www.thecodecage.com/forumz/view.php?pg=filesystemobject

FRIEL
09-12-2012, 08:00 AM
thanks ill have a look into that.

looks like its somthing totaly new to me.

GTO
09-12-2012, 12:13 PM
...in a folder there are folders numbered like this:

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

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

Maybe:
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