Count File Names

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hi All,

I having an issue with version control on Buisness cases. I currently
have a spreadsheet that looks into a folder and picks up details from
a number of files. However, they require version control numbers so
there will eventually be multiple files say 2010-01 v1.0, 2010-01
v1.1. Is there a way I can:

1) Get a macro to count the number of 2010-01 files there are in the
folder, and
2) Get my version control sheet to look at the latest version of the
file (in this example: 2010-01 v1.1)

Cheers,

Chris
 
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
 
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
Click to expand...

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
 
Back
Top