Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSearch
The file search object does not work properly on all platforms. This is a
known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch ..Newsearch ..Lookin = K:\ ..SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSearch
nokia phone?,
Sub LatestFile() 'Jim Cone - San Francisco, USA - June 02, 2005 'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll) 'Displays the latest file name in the strPath folder. Dim objFSO As Scripting.FileSystemObject Dim objFolder As Scripting.Folder Dim objFile As Scripting.File Dim strPath As String Dim strName As String Dim varDate As Variant ' Specify the folder... strPath = "C:\Program Files\Microsoft Office\Office" Set objFSO = New Scripting.FileSystemObject Set objFolder = objFSO.GetFolder(strPath) For Each objFile In objFolder.Files If objFile.DateLastModified varDate Then varDate = objFile.DateLastModified strName = objFile.Name End If Next 'objFile MsgBox strName & " - is latest file - " & varDate Set objFSO = Nothing Set objFolder = Nothing Set objFile = Nothing End Sub '------------------------------- "Enohp Aikon" wrote in message The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch ..Newsearch ..Lookin = K:\ ..SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSearch
You could do it like this. Pretty fast as well.
Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch .Newsearch .Lookin = K:\ .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
Thanks very much. This works great and works faster than the FileSearch
object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch .Newsearch .Lookin = K:\ .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
<nokia phone Yes, so many places that need a user name - I needed one that
is easy to remeber Thanks Jim, I though there must be a way to use the scripting object but I am not familiar with it and the help in Excel is lame. This works great but my only concern is many different users and computers will be using this file and I will not know if a reference to the scripting DLL will exist. Thoughts? "Jim Cone" wrote: nokia phone?, Sub LatestFile() 'Jim Cone - San Francisco, USA - June 02, 2005 'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll) 'Displays the latest file name in the strPath folder. Dim objFSO As Scripting.FileSystemObject Dim objFolder As Scripting.Folder Dim objFile As Scripting.File Dim strPath As String Dim strName As String Dim varDate As Variant ' Specify the folder... strPath = "C:\Program Files\Microsoft Office\Office" Set objFSO = New Scripting.FileSystemObject Set objFolder = objFSO.GetFolder(strPath) For Each objFile In objFolder.Files If objFile.DateLastModified varDate Then varDate = objFile.DateLastModified strName = objFile.Name End If Next 'objFile MsgBox strName & " - is latest file - " & varDate Set objFSO = Nothing Set objFolder = Nothing Set objFile = Nothing End Sub '------------------------------- "Enohp Aikon" wrote in message The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch ..Newsearch ..Lookin = K:\ ..SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
EA,
The Windows Script Host / FileSystemObject is part of Windows and is included with all Windows systems released after Windows 95. The help file is found here... http://msdn.microsoft.com/library/de...ist/webdev.asp I think you will find your own name is very easy to remember. Jim Cone San Francisco, USA "Enohp Aikon" wrote in message <nokia phone Yes, so many places that need a user name - I needed one that is easy to remeber Thanks Jim, I though there must be a way to use the scripting object but I am not familiar with it and the help in Excel is lame. This works great but my only concern is many different users and computers will be using this file and I will not know if a reference to the scripting DLL will exist. Thoughts? "Jim Cone" wrote: nokia phone?, '---------------------------- Sub LatestFile() 'Jim Cone - San Francisco, USA - June 02, 2005 'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll) 'Displays the latest file name in the strPath folder. Dim objFSO As Scripting.FileSystemObject Dim objFolder As Scripting.Folder Dim objFile As Scripting.File Dim strPath As String Dim strName As String Dim varDate As Variant ' Specify the folder... strPath = "C:\Program Files\Microsoft Office\Office" Set objFSO = New Scripting.FileSystemObject Set objFolder = objFSO.GetFolder(strPath) For Each objFile In objFolder.Files If objFile.DateLastModified varDate Then varDate = objFile.DateLastModified strName = objFile.Name End If Next 'objFile MsgBox strName & " - is latest file - " & varDate Set objFSO = Nothing Set objFolder = Nothing Set objFile = Nothing End Sub '------------------------------- "Enohp Aikon" wrote in message The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch ..Newsearch ..Lookin = K:\ ..SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
More information...
On the approximately 2000 files in my System32 folder, finding the latest file: 1. FileSystem object code took approx. 0.3 seconds. 2. RB Smissaert code took approx. 0.2 seconds. Jim Cone San Francisco, USA "Jim Cone" wrote in message ... EA, The Windows Script Host / FileSystemObject is part of Windows and is included with all Windows systems released after Windows 95. The help file is found here... http://msdn.microsoft.com/library/de...ist/webdev.asp I think you will find your own name is very easy to remember. Jim Cone San Francisco, USA "Enohp Aikon" wrote in message <nokia phone Yes, so many places that need a user name - I needed one that is easy to remeber Thanks Jim, I though there must be a way to use the scripting object but I am not familiar with it and the help in Excel is lame. This works great but my only concern is many different users and computers will be using this file and I will not know if a reference to the scripting DLL will exist. Thoughts? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
If I run this on C:\WINDOWS\SYSTEM32 with no filetype specified I get:
With scripting 280 milliSecs With Dir 140 milliSecs But if I specify a txt file the difference is much bigger even: With scripting 550 milliSecs With Dir 2 milliSecs Not sure though this what is the fastest way to specify the file type with scripting: For Each objFile In objFolder.Files If UCase(Right$(objFile.Name, 3)) = "TXT" Then 'If Right(objFile.Name, 3) = "txt" Then 'If objFile.Name Like "*.txt" Then If objFile.DateLastModified varDate Then varDate = objFile.DateLastModified strName = objFile.Name End If End If Next 'objFile RBS "Jim Cone" wrote in message ... More information... On the approximately 2000 files in my System32 folder, finding the latest file: 1. FileSystem object code took approx. 0.3 seconds. 2. RB Smissaert code took approx. 0.2 seconds. Jim Cone San Francisco, USA "Jim Cone" wrote in message ... EA, The Windows Script Host / FileSystemObject is part of Windows and is included with all Windows systems released after Windows 95. The help file is found here... http://msdn.microsoft.com/library/de...ist/webdev.asp I think you will find your own name is very easy to remember. Jim Cone San Francisco, USA "Enohp Aikon" wrote in message <nokia phone Yes, so many places that need a user name - I needed one that is easy to remeber Thanks Jim, I though there must be a way to use the scripting object but I am not familiar with it and the help in Excel is lame. This works great but my only concern is many different users and computers will be using this file and I will not know if a reference to the scripting DLL will exist. Thoughts? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
RBS,
My results are about the same as yours. The Like operator is what I have always used. What is interesting is that with only a handful of text files in the System32 folder the search time is faster screening for the file date first. '---------------------------------------------- So this takes about 0.6 seconds... For Each objFile In objFolder.Files If objFile.Name Like "*.txt" Then If objFile.DateLastModified varDate Then varDate = objFile.DateLastModified strName = objFile.Name End If End If Next 'objFile While this takes about 0.4 seconds... For Each objFile In objFolder.Files If objFile.DateLastModified varDate Then ' moved up 1 line If objFile.Name Like "*.txt" Then varDate = objFile.DateLastModified strName = objFile.Name End If End If Next 'objFile '-------------------- Regards, Jim Cone San Francisco, USA "RB Smissaert" wrote in message If I run this on C:\WINDOWS\SYSTEM32 with no filetype specified I get: With scripting 280 milliSecs With Dir 140 milliSecs But if I specify a txt file the difference is much bigger even: With scripting 550 milliSecs With Dir 2 milliSecs Not sure though this what is the fastest way to specify the file type with scripting: For Each objFile In objFolder.Files If UCase(Right$(objFile.Name, 3)) = "TXT" Then 'If Right(objFile.Name, 3) = "txt" Then 'If objFile.Name Like "*.txt" Then If objFile.DateLastModified varDate Then varDate = objFile.DateLastModified strName = objFile.Name End If End If Next 'objFile RBS "Jim Cone" wrote in message ... More information... On the approximately 2000 files in my System32 folder, finding the latest file: 1. FileSystem object code took approx. 0.3 seconds. 2. RB Smissaert code took approx. 0.2 seconds. Jim Cone San Francisco, USA |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
<I think you will find your own name is very easy to remember.
Yes, it took some time, but I think I got it memorized (for now). :-) There are a few places though, where I don't want to use my real name and/or email address as a log-on (ie: online banking), hence the easy to remember log-on alias. Its almost Alzheimer-proof so it should be good a few more years. Most importantly, thanks for the Windows Script-Host help file link. It appears that the this is a powerful tool that I need to learn how to use. I am curious, is there away to use VBA to create a reference to the scrrun.dll programmatically? In any event, many thanks to both you and RBS for sharing both your knowledge and time to help. "Jim Cone" wrote: EA, The Windows Script Host / FileSystemObject is part of Windows and is included with all Windows systems released after Windows 95. The help file is found here... http://msdn.microsoft.com/library/de...ist/webdev.asp I think you will find your own name is very easy to remember. Jim Cone San Francisco, USA "Enohp Aikon" wrote in message <nokia phone Yes, so many places that need a user name - I needed one that is easy to remeber Thanks Jim, I though there must be a way to use the scripting object but I am not familiar with it and the help in Excel is lame. This works great but my only concern is many different users and computers will be using this file and I will not know if a reference to the scripting DLL will exist. Thoughts? "Jim Cone" wrote: nokia phone?, '---------------------------- Sub LatestFile() 'Jim Cone - San Francisco, USA - June 02, 2005 'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll) 'Displays the latest file name in the strPath folder. Dim objFSO As Scripting.FileSystemObject Dim objFolder As Scripting.Folder Dim objFile As Scripting.File Dim strPath As String Dim strName As String Dim varDate As Variant ' Specify the folder... strPath = "C:\Program Files\Microsoft Office\Office" Set objFSO = New Scripting.FileSystemObject Set objFolder = objFSO.GetFolder(strPath) For Each objFile In objFolder.Files If objFile.DateLastModified varDate Then varDate = objFile.DateLastModified strName = objFile.Name End If Next 'objFile MsgBox strName & " - is latest file - " & varDate Set objFSO = Nothing Set objFolder = Nothing Set objFile = Nothing End Sub '------------------------------- "Enohp Aikon" wrote in message The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch ..Newsearch ..Lookin = K:\ ..SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
EA,
"I am curious, is there away to use VBA to create a reference to the scrrun.dll programmatically?" There is code available to do that if you do a Google search thru this newsgroup. Here's a start... http://support.microsoft.com/default...&Product=xlw97 XL97: How to Programmatically Create a Reference I have so far not had a problem with a missing reference. If a workbook has the reference established and you provide that workbook to others then the reference goes with it. I have a couple hundred add-ins, with the scripting runtime reference in them, provided to users around the world and a "missing reference" is not a complaint I have heard. Of course a problem could occur but that could also be said about any code to automatically add the reference. Regards, Jim Cone San Francisco, USA "Enohp Aikon" wrote in message <I think you will find your own name is very easy to remember. Yes, it took some time, but I think I got it memorized (for now). :-) There are a few places though, where I don't want to use my real name and/or email address as a log-on (ie: online banking), hence the easy to remember log-on alias. Its almost Alzheimer-proof so it should be good a few more years. Most importantly, thanks for the Windows Script-Host help file link. It appears that the this is a powerful tool that I need to learn how to use. I am curious, is there away to use VBA to create a reference to the scrrun.dll programmatically? In any event, many thanks to both you and RBS for sharing both your knowledge and time to help. "Jim Cone" wrote: EA, The Windows Script Host / FileSystemObject is part of Windows and is included with all Windows systems released after Windows 95. The help file is found here... http://msdn.microsoft.com/library/de...ist/webdev.asp I think you will find your own name is very easy to remember. Jim Cone San Francisco, USA "Enohp Aikon" wrote in message <nokia phone Yes, so many places that need a user name - I needed one that is easy to remeber Thanks Jim, I though there must be a way to use the scripting object but I am not familiar with it and the help in Excel is lame. This works great but my only concern is many different users and computers will be using this file and I will not know if a reference to the scripting DLL will exist. Thoughts? "Jim Cone" wrote: nokia phone?, '---------------------------- Sub LatestFile() 'Jim Cone - San Francisco, USA - June 02, 2005 'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll) 'Displays the latest file name in the strPath folder. Dim objFSO As Scripting.FileSystemObject Dim objFolder As Scripting.Folder Dim objFile As Scripting.File Dim strPath As String Dim strName As String Dim varDate As Variant ' Specify the folder... strPath = "C:\Program Files\Microsoft Office\Office" Set objFSO = New Scripting.FileSystemObject Set objFolder = objFSO.GetFolder(strPath) For Each objFile In objFolder.Files If objFile.DateLastModified varDate Then varDate = objFile.DateLastModified strName = objFile.Name End If Next 'objFile MsgBox strName & " - is latest file - " & varDate Set objFSO = Nothing Set objFolder = Nothing Set objFile = Nothing End Sub '------------------------------- "Enohp Aikon" wrote in message The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch ..Newsearch ..Lookin = K:\ ..SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
New method new problem.
The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch .Newsearch .Lookin = K:\ .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
No need to load all the files in an array and sort, unless you need that for
some other reason. Just make a small modifiction to the code: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lSuffix = Val(Right$(strDirReturn, 4)) If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "Enohp Aikon" wrote in message ... New method new problem. The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to K:. I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------------ With Application.FileSearch .Newsearch .Lookin = K:\ .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------------- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
wouldn't you need to strip off the extenstion first?
Do While Len(strDirReturn) if instr(1,strDirReturn,".") then _ strDirReturn = Left(strDirReturn,Instr(1,strDirReturn,".")-1) lSuffix = Val(Right$(strDirReturn, 4)) -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... No need to load all the files in an array and sort, unless you need that for some other reason. Just make a small modifiction to the code: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lSuffix = Val(Right$(strDirReturn, 4)) If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "Enohp Aikon" wrote in message ... New method new problem. The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to "K:". I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. -------------------------------------------------------------------------- ---- With Application.FileSearch .Newsearch .Lookin = "K:\" .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) -------------------------------------------------------------------------- ----- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
Yes, quite right.
This was air-code and hadn't thought about the details. RBS "Tom Ogilvy" wrote in message ... wouldn't you need to strip off the extenstion first? Do While Len(strDirReturn) if instr(1,strDirReturn,".") then _ strDirReturn = Left(strDirReturn,Instr(1,strDirReturn,".")-1) lSuffix = Val(Right$(strDirReturn, 4)) -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... No need to load all the files in an array and sort, unless you need that for some other reason. Just make a small modifiction to the code: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lSuffix = Val(Right$(strDirReturn, 4)) If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "Enohp Aikon" wrote in message ... New method new problem. The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to "K:". I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. -------------------------------------------------------------------------- ---- With Application.FileSearch .Newsearch .Lookin = "K:\" .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) -------------------------------------------------------------------------- ----- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
that's pretty impressive air-code. Not like the typo filled crap I churn
out <blush -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... Yes, quite right. This was air-code and hadn't thought about the details. RBS "Tom Ogilvy" wrote in message ... wouldn't you need to strip off the extenstion first? Do While Len(strDirReturn) if instr(1,strDirReturn,".") then _ strDirReturn = Left(strDirReturn,Instr(1,strDirReturn,".")-1) lSuffix = Val(Right$(strDirReturn, 4)) -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... No need to load all the files in an array and sort, unless you need that for some other reason. Just make a small modifiction to the code: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lSuffix = Val(Right$(strDirReturn, 4)) If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "Enohp Aikon" wrote in message ... New method new problem. The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to "K:". I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------- - ---- With Application.FileSearch .Newsearch .Lookin = "K:\" .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------- - ----- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
Well, it wasn't really air code as I just altered the function
that worked with FileDateTime. RBS "Tom Ogilvy" wrote in message ... that's pretty impressive air-code. Not like the typo filled crap I churn out <blush -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... Yes, quite right. This was air-code and hadn't thought about the details. RBS "Tom Ogilvy" wrote in message ... wouldn't you need to strip off the extenstion first? Do While Len(strDirReturn) if instr(1,strDirReturn,".") then _ strDirReturn = Left(strDirReturn,Instr(1,strDirReturn,".")-1) lSuffix = Val(Right$(strDirReturn, 4)) -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... No need to load all the files in an array and sort, unless you need that for some other reason. Just make a small modifiction to the code: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lSuffix = Val(Right$(strDirReturn, 4)) If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "Enohp Aikon" wrote in message ... New method new problem. The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to "K:". I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. ------------------------------------------------------------------------- - ---- With Application.FileSearch .Newsearch .Lookin = "K:\" .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) ------------------------------------------------------------------------- - ----- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
With Tom's correction the function should be:
Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lDotPos As Long Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lDotPos = InStr(1, strDirReturn, ".", vbBinaryCompare) If lDotPos 0 Then lSuffix = Val(Mid$(strDirReturn, lDotPos - 4, 4)) Else lSuffix = Val(Right$(strDirReturn, 4)) End If If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function Tom, didn't test, but altering strDirReturn looks a bit suspicious. RBS "Tom Ogilvy" wrote in message ... wouldn't you need to strip off the extenstion first? Do While Len(strDirReturn) if instr(1,strDirReturn,".") then _ strDirReturn = Left(strDirReturn,Instr(1,strDirReturn,".")-1) lSuffix = Val(Right$(strDirReturn, 4)) -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... No need to load all the files in an array and sort, unless you need that for some other reason. Just make a small modifiction to the code: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lSuffix = Val(Right$(strDirReturn, 4)) If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "Enohp Aikon" wrote in message ... New method new problem. The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to "K:". I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. -------------------------------------------------------------------------- ---- With Application.FileSearch .Newsearch .Lookin = "K:\" .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) -------------------------------------------------------------------------- ----- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
If there were files in the same folder with no suffix you may need to alter
the code like this: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String, _ lSuffixLen As Long) As String Dim strDirReturn As String Dim lDotPos As Long Dim lMax As Long Dim lSuffix As Long Dim bNewFile As Boolean Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lDotPos = InStr(1, strDirReturn, ".", vbBinaryCompare) bNewFile = False If lDotPos 0 Then If lDotPos lSuffixLen Then lSuffix = Val(Mid$(strDirReturn, _ lDotPos - lSuffixLen, _ lSuffixLen)) bNewFile = True End If Else If Len(strDirReturn) lSuffixLen Then lSuffix = Val(Right$(strDirReturn, lSuffixLen)) bNewFile = True End If End If If bNewFile Then If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "RB Smissaert" wrote in message ... With Tom's correction the function should be: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lDotPos As Long Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lDotPos = InStr(1, strDirReturn, ".", vbBinaryCompare) If lDotPos 0 Then lSuffix = Val(Mid$(strDirReturn, lDotPos - 4, 4)) Else lSuffix = Val(Right$(strDirReturn, 4)) End If If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function Tom, didn't test, but altering strDirReturn looks a bit suspicious. RBS "Tom Ogilvy" wrote in message ... wouldn't you need to strip off the extenstion first? Do While Len(strDirReturn) if instr(1,strDirReturn,".") then _ strDirReturn = Left(strDirReturn,Instr(1,strDirReturn,".")-1) lSuffix = Val(Right$(strDirReturn, 4)) -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... No need to load all the files in an array and sort, unless you need that for some other reason. Just make a small modifiction to the code: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lSuffix = Val(Right$(strDirReturn, 4)) If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "Enohp Aikon" wrote in message ... New method new problem. The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to "K:". I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. -------------------------------------------------------------------------- ---- With Application.FileSearch .Newsearch .Lookin = "K:\" .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) -------------------------------------------------------------------------- ----- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
I realized that the code needed to be changed slightly to account for the
file extension prior to reading the latest replies. I was able to get it working but your way is WAY better (if instr(1,strDirReturn,"."). Thanks Jim, RBS and Tom for both your help and your time! You guys underscore the difference between amateur and experts. "RB Smissaert" wrote: Yes, quite right. This was air-code and hadn't thought about the details. RBS "Tom Ogilvy" wrote in message ... wouldn't you need to strip off the extenstion first? Do While Len(strDirReturn) if instr(1,strDirReturn,".") then _ strDirReturn = Left(strDirReturn,Instr(1,strDirReturn,".")-1) lSuffix = Val(Right$(strDirReturn, 4)) -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... No need to load all the files in an array and sort, unless you need that for some other reason. Just make a small modifiction to the code: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lSuffix = Val(Right$(strDirReturn, 4)) If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "Enohp Aikon" wrote in message ... New method new problem. The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to "K:". I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. -------------------------------------------------------------------------- ---- With Application.FileSearch .Newsearch .Lookin = "K:\" .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) -------------------------------------------------------------------------- ----- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return most recent file in subdirectory with out using FileSea
No problem.
Tom and Jim may be experts, but not me. Post plenty of questions myself. RBS "Enohp Aikon" wrote in message ... I realized that the code needed to be changed slightly to account for the file extension prior to reading the latest replies. I was able to get it working but your way is WAY better (if instr(1,strDirReturn,"."). Thanks Jim, RBS and Tom for both your help and your time! You guys underscore the difference between amateur and experts. "RB Smissaert" wrote: Yes, quite right. This was air-code and hadn't thought about the details. RBS "Tom Ogilvy" wrote in message ... wouldn't you need to strip off the extenstion first? Do While Len(strDirReturn) if instr(1,strDirReturn,".") then _ strDirReturn = Left(strDirReturn,Instr(1,strDirReturn,".")-1) lSuffix = Val(Right$(strDirReturn, 4)) -- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... No need to load all the files in an array and sort, unless you need that for some other reason. Just make a small modifiction to the code: Function MostRecentFileInFolder2(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim lMax As Long Dim lSuffix As Long Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) lSuffix = Val(Right$(strDirReturn, 4)) If lSuffix lMax Then lMax = lSuffix strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder2 = strLastFile Exit Function ERROROUT: MostRecentFileInFolder2 = "" On Error GoTo 0 End Function RBS "Enohp Aikon" wrote in message ... New method new problem. The FileDateTime returns the date and time when a file was created or last modified. I was using the FileSearch object to return the date the file was created (not modified). I did not fully explain exactly what I was trying to accomplish in my original post. I have a procedure that creates a workbook on demand, names it and then saves it to a particular directory. The file names consist of a common prefix name combined with a incremental suffix number (ie: MyWorkbook-0001, MyWorkbook-0002, and so on). I need to: Find the file with the highest suffix number Get the suffix portion of the name and convert it to a number Increment the number by 1 Convert the number back into text with leading zeros (0003). Create a new file name by combining the constant prefix name with the newly created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix) I guess I will need to load all of the files into an array, sort by descending order and they select the 1st item in the array (or something like that). Any suggestions? "Enohp Aikon" wrote: Thanks very much. This works great and works faster than the FileSearch object. "RB Smissaert" wrote: You could do it like this. Pretty fast as well. Function MostRecentFileInFolder(strFolder As String, _ strFileMask As String) As String Dim strDirReturn As String Dim dMax As Date Dim strLastFile As String On Error GoTo ERROROUT If Not Right$(strFolder, 1) = "\" Then strFolder = strFolder & "\" End If strDirReturn = Dir$(strFolder & strFileMask, _ vbArchive Or _ vbHidden Or _ vbReadOnly Or _ vbSystem) Do While Len(strDirReturn) If FileDateTime(strFolder & strDirReturn) dMax Then dMax = FileDateTime(strFolder & strDirReturn) strLastFile = strFolder & strDirReturn End If strDirReturn = Dir$() Loop MostRecentFileInFolder = strLastFile Exit Function ERROROUT: MostRecentFileInFolder = "" On Error GoTo 0 End Function Sub test() MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt") End Sub RBS "Enohp Aikon" wrote in message ... The file search object does not work properly on all platforms. This is a known issue but the workarounds provided in article 305342 do not resolve the problem when using a local hard drive mapped to "K:". I need recommendations for returning the most recent file in a subdirectory with out using the FileSearch object. The following code works as expected except when using Office XP with Windows 2000. -------------------------------------------------------------------------- ---- With Application.FileSearch .Newsearch .Lookin = "K:\" .SearchSubFolders = false NumFound = .Execute(SortBy: msoSortByLastModified, _ SortOrder: = msoSortOrderDescending _ AlwaysAccurate = True) If NumFound 0 then NewestFile = FilesFound(1) -------------------------------------------------------------------------- ----- I need code that works on any combination of Office 2000 and above with Window 2000 and above. The code above is generic and I will actually be using a function. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup return most recent date (value) | Excel Discussion (Misc queries) | |||
lookup part number in a vertical list and return the most recent . | Excel Discussion (Misc queries) | |||
formula to return date of most recent update | Excel Worksheet Functions | |||
FileSearch & .zip file | Excel Programming | |||
Find Correct Subdirectory | Excel Programming |