On Jan 13, 1:32*pm, Chris wrote:
Hi Joel,

Thanks for this, it does work however, I haven't been explcit enough
in my original email.

The identifier for the file isn't quite year & month; you are right in
that 2010 is the year, however the second is just a sequential
reference number. So I would only want the macro to count 2010-01 as a
group, then 2010-02. So from the example above it would say that I
have 2 x 2010-01 files and the latest version is v1.1.

I have tweaked the macro, it runs, but does not give me any count
values in column E? I know it is going to be something simple, but no
error is coming up and I can't see where I have gone wrong (it also
might be slight inefficient!)

Here's the code:

Sub GetLatestFile()

Dim MyLr As Long
Dim Consh As Worksheet

Set Consh = Sheets("Reporter")

MyLr = Consh.Cells(Rows.Count, "b").End(xlUp).Row

Folder = "c:\"

LatestDate = 0
LatestVersion = 0
LatestFName = ""

'make sure folder name has last backslash
If Right(Folder, 1) < "\" Then
Folder = Folder & "\"
End If

If myLR1 < 9 Then
* * myLR1 = 9
End If

For r = 9 To MyLr

CountFile = 0
FName = Dir(Folder & "*.xls")
Do While FName < ""
'split file name into two piece around the space
NewVersionArray = Split(FName, " ")
'spit the date portion of the file name into year and month
NewDateArray = Split(Trim(NewVersionArray(0)), "-")
'convert the date to a number (serial date), use the first day of
'the Month
NewDate = DateSerial(NewDateArray(0), NewDateArray(1), 1)

If NewVersionArray(0) = Consh.Cells(r, "B").Value Then
CountFile = CountFile + 1
Consh.Cells(r, "E") = CountFile

End If

'remove the V from verion so version is a number
NewVersion = Val(Mid(Trim(NewVersionArray(1)), 2))

If NewDate LatestDate And _
NewVersion LatestVersion Then

LatestDate = NewDate
LatestVersion = NewVersion
LatestFName = FName
End If

Cells(r, "F") = NewVersion

FName = Dir()
Loop

'add folder name to latest file name
LatestFName = Folder & LatestFName

Next r

End Sub

Thanks Again.

Chris

Just an Update, Have now got the Filename count to work perfectly,
just re-entered the values in the cells and it worked fine, now just
having an issue with the latter version control section.

Cheers