Sorting file names
 

This challange has caused many (new) grey hairs and none of the approaches I
have tried is working for me. I hope someone can help me out!!!

I have a folder that contain files with names containing two parts:
XX-YY.xls (both XX and YY are numeric values).

In a template file cell A1 is an XX number located. It is also in the
template the code should be located.

The code should read the value in cell A1 and look at files containing this
number (XX). Of the XX files the file name with the highest YY value should
be identified and the template should now save itself with the name
XX-YY+1.xls.

If the XX in cell A1 does not exit the filename should be XX-01.xls.

Let's say the folder contain these files:
01-01.xls
01-02.xls
01-03.xls
02-01.xls

If cell A1 is:
01 the file should be named 01-04.xls
02 the file should be named 02-02.xls
03 the file should be named 03-01.xls

Does this make any sense? If so I would really appreciate your help!

Thanks
The Doctor

Sorting file names
 

Sub test5()
Folder = "c:\temp"
HighNum = 0
Do
If HighNum = 0 Then
FName = Dir(Folder & "\" & Range("A1") & "-*.xls")

Else
FName = Dir()
End If
NewHighNum = Val(Mid(FName, 4, 2))
If NewHigNum HighNum Then
HighNum = NewHighNum
End If
Loop While FName < ""

NewFileName = Range("A1") & "-" & Format(HighNum + 1, "#00") & ".xls"


End Sub

"Dr. Schwartz" wrote:

This challange has caused many (new) grey hairs and none of the approaches I
have tried is working for me. I hope someone can help me out!!!

I have a folder that contain files with names containing two parts:
XX-YY.xls (both XX and YY are numeric values).

In a template file cell A1 is an XX number located. It is also in the
template the code should be located.

The code should read the value in cell A1 and look at files containing this
number (XX). Of the XX files the file name with the highest YY value should
be identified and the template should now save itself with the name
XX-YY+1.xls.

If the XX in cell A1 does not exit the filename should be XX-01.xls.

Let's say the folder contain these files:
01-01.xls
01-02.xls
01-03.xls
02-01.xls

If cell A1 is:
01 the file should be named 01-04.xls
02 the file should be named 02-02.xls
03 the file should be named 03-01.xls

Does this make any sense? If so I would really appreciate your help!

Thanks
The Doctor

Sorting file names
 

The code I previously postedc will fail when no file is returned. small change

Sub test5()
Folder = "c:\temp"
HighNum = 0
Do
If HighNum = 0 Then
FName = Dir(Folder & "\" & Range("A1") & "-*.xls")

Else
FName = Dir()
End If
If FName < "" Then
NewHighNum = Val(Mid(FName, 4, 2))
If NewHigNum HighNum Then
HighNum = NewHighNum
End If
End If
Loop While FName < ""

NewFileName = Range("A1") & "-" & Format(HighNum + 1, "#00") & ".xls"


"Joel" wrote:


Sub test5()
Folder = "c:\temp"
HighNum = 0
Do
If HighNum = 0 Then
FName = Dir(Folder & "\" & Range("A1") & "-*.xls")

Else
FName = Dir()
End If
NewHighNum = Val(Mid(FName, 4, 2))
If NewHigNum HighNum Then
HighNum = NewHighNum
End If
Loop While FName < ""

NewFileName = Range("A1") & "-" & Format(HighNum + 1, "#00") & ".xls"


End Sub

"Dr. Schwartz" wrote:

This challange has caused many (new) grey hairs and none of the approaches I
have tried is working for me. I hope someone can help me out!!!

I have a folder that contain files with names containing two parts:
XX-YY.xls (both XX and YY are numeric values).

In a template file cell A1 is an XX number located. It is also in the
template the code should be located.

The code should read the value in cell A1 and look at files containing this
number (XX). Of the XX files the file name with the highest YY value should
be identified and the template should now save itself with the name
XX-YY+1.xls.

If the XX in cell A1 does not exit the filename should be XX-01.xls.

Let's say the folder contain these files:
01-01.xls
01-02.xls
01-03.xls
02-01.xls

If cell A1 is:
01 the file should be named 01-04.xls
02 the file should be named 02-02.xls
03 the file should be named 03-01.xls

Does this make any sense? If so I would really appreciate your help!

Thanks
The Doctor