Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
GEORGIA
 
Posts: n/a
Default Open last file with certain file name

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!
  #2   Report Post  
Posted to microsoft.public.excel.misc
Gazeta
 
Posts: n/a
Default Open last file with certain file name


U¿ytkownik "GEORGIA" napisa³ w
wiadomo¶ci ...
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




  #3   Report Post  
Posted to microsoft.public.excel.misc
GEORGIA
 
Posts: n/a
Default Open last file with certain file name

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!


"Gazeta" wrote:


U¿ytkownik "GEORGIA" napisa³ w
wiadomo¶ci ...
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





  #4   Report Post  
Posted to microsoft.public.excel.misc
GEORGIA
 
Posts: n/a
Default Open last file with certain file name

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" wrote:


U¿ytkownik "GEORGIA" napisa³ w
wiadomo¶ci ...
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





  #5   Report Post  
Posted to microsoft.public.excel.misc
Gazeta
 
Posts: n/a
Default Open last file with certain file name

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




  #6   Report Post  
Posted to microsoft.public.excel.misc
GEORGIA
 
Posts: n/a
Default Open last file with certain file name

Thank you.....

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

"Gazeta" wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
file is locked does not appear in Excel if file already open by an Leslie New Users to Excel 3 April 10th 06 09:40 PM
Advised file is open when not the case deniseh Excel Discussion (Misc queries) 1 November 3rd 05 02:37 PM
How do you open a template at startup? James Kendall Excel Discussion (Misc queries) 7 July 26th 05 07:33 PM
Excel file does not open Marcin Excel Discussion (Misc queries) 2 April 29th 05 05:46 PM
csv file will not open Scott Randolph New Users to Excel 0 April 29th 05 02:18 AM


All times are GMT +1. The time now is 05:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"