Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count File Names
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count File Names
See if this code works. I assume that 2010-01 is year and month. Sub GetLatestFile() Folder = "c:\temp\" LatestDate = 0 LatestVersion = 0 LatestFName = "" 'make sure folder name has last backslash If Right(Folder, 1) < "\" Then Folder = Folder & "\" End If CountYear = 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) 'count if file is this year If Year(NewDate) = 2010 Then CountYear = CountYear + 1 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 FName = Dir() Loop 'add folder name to latest file name LatestFName = Folder & LatestFName End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169466 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count File Names
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Names NOT #N/A | Excel Worksheet Functions | |||
Match file names listed in column with file names in folder | Excel Programming | |||
Count Names | Excel Worksheet Functions | |||
Count Names | Excel Worksheet Functions | |||
Count Names | Excel Discussion (Misc queries) |