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

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"
<EnohpAikon@discussions.microsoft.com>
wrote in message
news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com
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

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" <EnohpAikon@discussions.microsoft.com> wrote in message
news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
> 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
>

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" <EnohpAikon@discussions.microsoft.com> wrote in message
> news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
> > 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
> >
>
>

<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"
> <EnohpAikon@discussions.microsoft.com>
> wrote in message
> news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com
> 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
>
>

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"
<EnohpAikon@discussions.microsoft.com>
wrote in message
news:65E9B8C3-5962-4CCD-8447-0691F9BC2D8C@microsoft.com
<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"
> <EnohpAikon@discussions.microsoft.com>
> wrote in message
> news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com
> 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

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" <jim.coneXXX@rcn.comXXX> wrote in message news:eZsyCV8xFHA.2076@TK2MSFTNGP14.phx.gbl...
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"
<EnohpAikon@discussions.microsoft.com>
wrote in message
news:65E9B8C3-5962-4CCD-8447-0691F9BC2D8C@microsoft.com
<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?

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" <jim.coneXXX@rcn.comXXX> wrote in message
news:eU07F$8xFHA.3312@TK2MSFTNGP09.phx.gbl...
> 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" <jim.coneXXX@rcn.comXXX> wrote in message
> news:eZsyCV8xFHA.2076@TK2MSFTNGP14.phx.gbl...
> 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"
> <EnohpAikon@discussions.microsoft.com>
> wrote in message
> news:65E9B8C3-5962-4CCD-8447-0691F9BC2D8C@microsoft.com
> <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?
>

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" <bartsmissaert@blueyonder.co.uk>
wrote in message
news:OiqrXvByFHA.3588@tk2msftngp13.phx.gbl
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" <jim.coneXXX@rcn.comXXX> wrote in message
news:eU07F$8xFHA.3312@TK2MSFTNGP09.phx.gbl...
> 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

<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" 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"
> <EnohpAikon@discussions.microsoft.com>
> wrote in message
> news:65E9B8C3-5962-4CCD-8447-0691F9BC2D8C@microsoft.com
> <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"
> > <EnohpAikon@discussions.microsoft.com>
> > wrote in message
> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com
> > 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
>
>

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...&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" <EnohpAikon@discussions.microsoft.com>
wrote in message
news:DF7BC97B-D962-41C6-B58A-87615CBA3E50@microsoft.com
<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" 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"
> <EnohpAikon@discussions.microsoft.com>
> wrote in message
> news:65E9B8C3-5962-4CCD-8447-0691F9BC2D8C@microsoft.com
> <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"
> > <EnohpAikon@discussions.microsoft.com>
> > wrote in message
> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com
> > 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
>
>

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" <EnohpAikon@discussions.microsoft.com> wrote in message
> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
> > > 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
> > >
> >
> >

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" <EnohpAikon@discussions.microsoft.com> wrote in message
news:25A6378E-05BB-49F1-A23B-165585127435@microsoft.com...
> 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" <EnohpAikon@discussions.microsoft.com> wrote in message
>> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
>> > > 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
>> > >
>> >
>> >

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" <bartsmissaert@blueyonder.co.uk> wrote in message
news:u9gd0DEyFHA.2800@TK2MSFTNGP10.phx.gbl...
> 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" <EnohpAikon@discussions.microsoft.com> wrote in message
> news:25A6378E-05BB-49F1-A23B-165585127435@microsoft.com...
> > 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" <EnohpAikon@discussions.microsoft.com> wrote in message
> >> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
> >> > > 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
> >> > >
> >> >
> >> >
>

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

RBS

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:eWKoucFyFHA.1412@TK2MSFTNGP09.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:u9gd0DEyFHA.2800@TK2MSFTNGP10.phx.gbl...
>> 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" <EnohpAikon@discussions.microsoft.com> wrote in message
>> news:25A6378E-05BB-49F1-A23B-165585127435@microsoft.com...
>> > 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" <EnohpAikon@discussions.microsoft.com> wrote in
>> >> > message
>> >> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
>> >> > > 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
>> >> > >
>> >> >
>> >> >
>>
>
>

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

--
Regards,
Tom Ogilvy

"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:eOvG$sFyFHA.2540@TK2MSFTNGP09.phx.gbl...
> Yes, quite right.
> This was air-code and hadn't thought about the details.
>
> RBS
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:eWKoucFyFHA.1412@TK2MSFTNGP09.phx.gbl...
> > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> > news:u9gd0DEyFHA.2800@TK2MSFTNGP10.phx.gbl...
> >> 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" <EnohpAikon@discussions.microsoft.com> wrote in message
> >> news:25A6378E-05BB-49F1-A23B-165585127435@microsoft.com...
> >> > 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" <EnohpAikon@discussions.microsoft.com> wrote in
> >> >> > message
> >> >> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
> >> >> > > 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
> >> >> > >
> >> >> >
> >> >> >
> >>
> >
> >
>

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

RBS

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:ujrGHxFyFHA.3588@tk2msftngp13.phx.gbl...
> that's pretty impressive air-code. Not like the typo filled crap I churn
> out <blush>
>
> --
> Regards,
> Tom Ogilvy
>
> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:eOvG$sFyFHA.2540@TK2MSFTNGP09.phx.gbl...
>> Yes, quite right.
>> This was air-code and hadn't thought about the details.
>>
>> RBS
>>
>> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>> news:eWKoucFyFHA.1412@TK2MSFTNGP09.phx.gbl...
>> > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
>> > news:u9gd0DEyFHA.2800@TK2MSFTNGP10.phx.gbl...
>> >> 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" <EnohpAikon@discussions.microsoft.com> wrote in message
>> >> news:25A6378E-05BB-49F1-A23B-165585127435@microsoft.com...
>> >> > 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" <EnohpAikon@discussions.microsoft.com> wrote in
>> >> >> > message
>> >> >> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
>> >> >> > > 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
>> >> >> > >
>> >> >> >
>> >> >> >
>> >>
>> >
>> >
>>
>
>

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" <twogilvy@msn.com> wrote in message
news:eWKoucFyFHA.1412@TK2MSFTNGP09.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:u9gd0DEyFHA.2800@TK2MSFTNGP10.phx.gbl...
>> 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" <EnohpAikon@discussions.microsoft.com> wrote in message
>> news:25A6378E-05BB-49F1-A23B-165585127435@microsoft.com...
>> > 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" <EnohpAikon@discussions.microsoft.com> wrote in
>> >> > message
>> >> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
>> >> > > 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
>> >> > >
>> >> >
>> >> >
>>
>
>

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" <twogilvy@msn.com> wrote in message
> news:eWKoucFyFHA.1412@TK2MSFTNGP09.phx.gbl...
> > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> > news:u9gd0DEyFHA.2800@TK2MSFTNGP10.phx.gbl...
> >> 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" <EnohpAikon@discussions.microsoft.com> wrote in message
> >> news:25A6378E-05BB-49F1-A23B-165585127435@microsoft.com...
> >> > 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" <EnohpAikon@discussions.microsoft.com> wrote in
> >> >> > message
> >> >> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
> >> >> > > 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
> >> >> > >
> >> >> >
> >> >> >
> >>
> >
> >
>
>

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" <bartsmissaert@blueyonder.co.uk> wrote in message
news:eWjhWWGyFHA.1456@TK2MSFTNGP11.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:eWKoucFyFHA.1412@TK2MSFTNGP09.phx.gbl...
>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
>> news:u9gd0DEyFHA.2800@TK2MSFTNGP10.phx.gbl...
>>> 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" <EnohpAikon@discussions.microsoft.com> wrote in message
>>> news:25A6378E-05BB-49F1-A23B-165585127435@microsoft.com...
>>> > 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" <EnohpAikon@discussions.microsoft.com> wrote in
>>> >> > message
>>> >> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
>>> >> > > 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
>>> >> > >
>>> >> >
>>> >> >
>>>
>>
>>
>

No problem.
Tom and Jim may be experts, but not me.
Post plenty of questions myself.

RBS


"Enohp Aikon" <EnohpAikon@discussions.microsoft.com> wrote in message
news:CEABED54-D6E8-42CA-9422-5C5AC1A2E81E@microsoft.com...
>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" <twogilvy@msn.com> wrote in message
>> news:eWKoucFyFHA.1412@TK2MSFTNGP09.phx.gbl...
>> > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
>> > news:u9gd0DEyFHA.2800@TK2MSFTNGP10.phx.gbl...
>> >> 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" <EnohpAikon@discussions.microsoft.com> wrote in message
>> >> news:25A6378E-05BB-49F1-A23B-165585127435@microsoft.com...
>> >> > 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" <EnohpAikon@discussions.microsoft.com> wrote in
>> >> >> > message
>> >> >> > news:7F751B96-3BB0-43FA-9030-5B98737F48A0@microsoft.com...
>> >> >> > > 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
>> >> >> > >
>> >> >> >
>> >> >> >
>> >>
>> >
>> >
>>
>>