Open last file with certain file name

[GEORGIA]

Hi,
I have previous go the cod to open the last saved/modified file from the
folder
like so:
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim strFolderName As String
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Excel.Application.DisplayAlerts = False

strFolderName = "http://srfi.inc.xxxxxx./Daily_Dir/" (our intranet folder)
Dim strFileName
With Application.FileSearch
.NewSearch
.LookIn = strFolderName
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
.Execute SortBy:=msoSortBySize

.LookIn = strFolderName
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = False
If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
strFileName = .FoundFiles(1)
Workbooks.Open strFileName

End If
End With

it works like a charm.

Now, i have a new problem. I would like to use the code above to get a file
from other folder, except that I want to open the last modified file with
certain name. There are several types of report saved in this folder. The
one I am interested in are saved like so:
4/1/06 Open Tickets.xls
4/2/06 Open Tickets.xls
4/3/06 Open Tickets.xls

and new ones are added with dates.

So what i would like to do is open the last modified file with name
containing "Open Tickets".

How would I go about doing so?

Thank you!

 

[Gazeta]

Hi,
I have previous go the cod to open the last saved/modified file from the
folder
like so:
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim strFolderName As String
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Excel.Application.DisplayAlerts = False

strFolderName = "http://srfi.inc.xxxxxx./Daily_Dir/" (our intranet folder)
Dim strFileName
With Application.FileSearch
.NewSearch
.LookIn = strFolderName
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
.Execute SortBy:=msoSortBySize

.LookIn = strFolderName
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = False
If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
strFileName = .FoundFiles(1)
Workbooks.Open strFileName

End If
End With

it works like a charm.

Now, i have a new problem. I would like to use the code above to get a file
from other folder, except that I want to open the last modified file with
certain name. There are several types of report saved in this folder. The
one I am interested in are saved like so:
4/1/06 Open Tickets.xls
4/2/06 Open Tickets.xls
4/3/06 Open Tickets.xls

and new ones are added with dates.

So what i would like to do is open the last modified file with name
containing "Open Tickets".

How would I go about doing so?

Thank you!

add If Not .FoundFiles(i) Like "*Open Tickets*" Then
....
strFileName = .FoundFiles(1)
If .FoundFiles(i) Like "*Open Tickets*" Then
Workbooks.Open strFileName
....
mcg

 

[GEORGIA]

i don't know what i'm doing wrong but it's not working..


Private Sub Command6_Click()
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim strFolderName As String
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Excel.Application.DisplayAlerts = False

strFolderName = "http://srfi.inc.xxxxxxx/Daily_Dir/"

Dim strFileName
With Application.FileSearch
.NewSearch
.LookIn = strFolderName
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
.Execute SortBy:=msoSortBySize

.LookIn = strFolderName
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = False
If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
strFileName = .FoundFiles(1)
If .FoundFiles(i) Like "*Open Tickets*" Then
Workbooks.Open strFileName

End If
End If

End With

End Sub

it just opens a excel and nothing happens.
help!

 

[GEORGIA]

I think i know why it's not working but I don't know how to fix it.

If i'm not mistaken, the code below will open if the filename contains
"*open tickets*" and if it's the last modified file.

in my case it won't, because there are other reports exists in this folder.
Folder contains reports like so:

Name of file Date modified
4/12/06 Contract report.xls 4/12/2006 1:10 pm
4/11/06 Open Tickets.xls 4/11/2006 3:00 pm
4/10/06 Open Tickets.xls 4/10/2006 2:15 pm
4/10/06 Contract report.xls 4/10/2006 4:15 pm

I want to open the last modified file with name "*Open Tickets*"
which will be 4/11/06 Open tickets.xls file.

is this possible?
Thank you!

 

[Gazeta]

i found nice page
might be helpful for you
http://vbnet.mvps.org/index.html?code/fileapi/recursivefiles_minimal.htm
mcg

 

[GEORGIA]

Thank you.....

but i'm still lost..... :/