Count File Names
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
|