Return most recent file in subdirectory with out using FileSearch

[Guest]

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

 

[Jim Cone]

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"
<[email protected]>
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)

 

[RB Smissaert]

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

 

[Guest]

Thanks very much. This works great and works faster than the FileSearch
object.

 

[Guest]

<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]

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/default.asp?url=/downloads/list/webdev.asp

I think you will find your own name is very easy to remember.

Jim Cone
San Francisco, USA


"Enohp Aikon"
<[email protected]>
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]

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



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/default.asp?url=/downloads/list/webdev.asp
I think you will find your own name is very easy to remember.
Jim Cone
San Francisco, USA



"Enohp Aikon"
<[email protected]>
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?

 

[RB Smissaert]

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]

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" <[email protected]>
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

 

[Guest]

<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. It’s 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]

EA,

Re: "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.aspx?scid=kb;en-us;160647&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" <[email protected]>
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. It’s 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.

 

[Guest]

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?

 

[RB Smissaert]

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

 

[Tom Ogilvy]

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))

 

[RB Smissaert]

Yes, quite right.
This was air-code and hadn't thought about the details.

RBS

 

[Tom Ogilvy]

that's pretty impressive air-code. Not like the typo filled crap I churn
out <blush>

 

[RB Smissaert]

Well, it wasn't really air code as I just altered the function
that worked with FileDateTime.

RBS

 

[RB Smissaert]

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

 

[RB Smissaert]

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

 

[Guest]

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.