Hello
I am trying to open the most recent file in a folder. I have found 2
possible solutions here but neither seem to work for me.

First:
Const FilePath = "Mypath"
Workbooks.Open Filename:=FindNewestFile(FilePath)

This one gives me Undefined sub or function on
FindNewestFile

The other:

With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.Filename = "*.jnk"
.Execute SortBy:=msoSortBySize
.NewSearch
.LookIn = "mypath"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderDescending) 0 Then
MsgBox "The newest file is " & .FoundFiles(1) & " created " &
FileDateTime(.FoundFiles(1))
Workbooks.Open .FoundFiles(1)
Else
MsgBox "There were no files found."
End If
End With

This one gives me "Object doesn't support this action" on
With Application.FileSearch

Can anyone help?
Thanks!

Try this code. FILESEARCH doesn't work in excel 2007. This method will work
in all excel versions. Yu need to modify FilePath below as required.


Sub GetLatestFile()

Const FilePath = "Mypath"


Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(FilePath)

MyDate = 1
Myfile = ""
For Each fl In folder.Files
PeriodPos = InStrRev(fl.Name, ".")
EXT = Mid(fl.Name, PeriodPos + 1)
If UCase(EXT) = "JNK" Then
If fl.DateLastModified MyDate Then
Myfile = fl.Path
MyDate = fl.DateLastModified
End If
End If
Next fl
If Myfile = "" Then
MsgBox "There were no files found."
Else
MsgBox "The newest file is " & Myfile & _
" created " & MyDate

Workbooks.Open Myfile
End If

End Sub


"Sandy" wrote:

Hello
I am trying to open the most recent file in a folder. I have found 2
possible solutions here but neither seem to work for me.

First:
Const FilePath = "Mypath"
Workbooks.Open Filename:=FindNewestFile(FilePath)

This one gives me Undefined sub or function on
FindNewestFile

The other:

With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.Filename = "*.jnk"
.Execute SortBy:=msoSortBySize
.NewSearch
.LookIn = "mypath"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderDescending) 0 Then
MsgBox "The newest file is " & .FoundFiles(1) & " created " &
FileDateTime(.FoundFiles(1))
Workbooks.Open .FoundFiles(1)
Else
MsgBox "There were no files found."
End If
End With

This one gives me "Object doesn't support this action" on
With Application.FileSearch

Can anyone help?
Thanks!

Thanks Joel I got that to work. Once I have the file open how do I reference
it.
Dim myBook As Workbook
Set myBook = ?
Something like that
"Joel" wrote:

Try this code. FILESEARCH doesn't work in excel 2007. This method will work
in all excel versions. Yu need to modify FilePath below as required.


Sub GetLatestFile()

Const FilePath = "Mypath"


Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(FilePath)

MyDate = 1
Myfile = ""
For Each fl In folder.Files
PeriodPos = InStrRev(fl.Name, ".")
EXT = Mid(fl.Name, PeriodPos + 1)
If UCase(EXT) = "JNK" Then
If fl.DateLastModified MyDate Then
Myfile = fl.Path
MyDate = fl.DateLastModified
End If
End If
Next fl
If Myfile = "" Then
MsgBox "There were no files found."
Else
MsgBox "The newest file is " & Myfile & _
" created " & MyDate

Workbooks.Open Myfile
End If

End Sub


"Sandy" wrote:

Hello
I am trying to open the most recent file in a folder. I have found 2
possible solutions here but neither seem to work for me.

First:
Const FilePath = "Mypath"
Workbooks.Open Filename:=FindNewestFile(FilePath)

This one gives me Undefined sub or function on
FindNewestFile

The other:

With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.Filename = "*.jnk"
.Execute SortBy:=msoSortBySize
.NewSearch
.LookIn = "mypath"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderDescending) 0 Then
MsgBox "The newest file is " & .FoundFiles(1) & " created " &
FileDateTime(.FoundFiles(1))
Workbooks.Open .FoundFiles(1)
Else
MsgBox "There were no files found."
End If
End With

This one gives me "Object doesn't support this action" on
With Application.FileSearch

Can anyone help?
Thanks!

When a workbook is opened it automatically becomes the active workbook. You
can use ActiveWorkbook but I prefer

from
Workbooks.Open Myfile
to
set bk = Workbooks.Open(filename:=Myfile)

When using an equal sign in a workbook open you must use parenthesis. If
there is no equal sign, you have the option of using parenthesis or not. May
VBA instructions require the parenthesis when you have an equal sign.



"Sandy" wrote:

Thanks Joel I got that to work. Once I have the file open how do I reference
it.
Dim myBook As Workbook
Set myBook = ?
Something like that
"Joel" wrote:

Try this code. FILESEARCH doesn't work in excel 2007. This method will work
in all excel versions. Yu need to modify FilePath below as required.


Sub GetLatestFile()

Const FilePath = "Mypath"


Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(FilePath)

MyDate = 1
Myfile = ""
For Each fl In folder.Files
PeriodPos = InStrRev(fl.Name, ".")
EXT = Mid(fl.Name, PeriodPos + 1)
If UCase(EXT) = "JNK" Then
If fl.DateLastModified MyDate Then
Myfile = fl.Path
MyDate = fl.DateLastModified
End If
End If
Next fl
If Myfile = "" Then
MsgBox "There were no files found."
Else
MsgBox "The newest file is " & Myfile & _
" created " & MyDate

Workbooks.Open Myfile
End If

End Sub


"Sandy" wrote:

Hello
I am trying to open the most recent file in a folder. I have found 2
possible solutions here but neither seem to work for me.

First:
Const FilePath = "Mypath"
Workbooks.Open Filename:=FindNewestFile(FilePath)

This one gives me Undefined sub or function on
FindNewestFile

The other:

With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.Filename = "*.jnk"
.Execute SortBy:=msoSortBySize
.NewSearch
.LookIn = "mypath"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderDescending) 0 Then
MsgBox "The newest file is " & .FoundFiles(1) & " created " &
FileDateTime(.FoundFiles(1))
Workbooks.Open .FoundFiles(1)
Else
MsgBox "There were no files found."
End If
End With

This one gives me "Object doesn't support this action" on
With Application.FileSearch

Can anyone help?
Thanks!

OK Thanks---I guess I didnt give enough information. From workbook A I want
to search and open workbook B. Once open I want to copy workbook B sheet 1
to workbook A. Then copy book B sheet 2 to workbook A etc. Im not sure how
to get back the second time.

Thanks again!

"Joel" wrote:

When a workbook is opened it automatically becomes the active workbook. You
can use ActiveWorkbook but I prefer

from
Workbooks.Open Myfile
to
set bk = Workbooks.Open(filename:=Myfile)

When using an equal sign in a workbook open you must use parenthesis. If
there is no equal sign, you have the option of using parenthesis or not. May
VBA instructions require the parenthesis when you have an equal sign.



"Sandy" wrote:

Thanks Joel I got that to work. Once I have the file open how do I reference
it.
Dim myBook As Workbook
Set myBook = ?
Something like that
"Joel" wrote:

Try this code. FILESEARCH doesn't work in excel 2007. This method will work
in all excel versions. Yu need to modify FilePath below as required.


Sub GetLatestFile()

Const FilePath = "Mypath"


Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(FilePath)

MyDate = 1
Myfile = ""
For Each fl In folder.Files
PeriodPos = InStrRev(fl.Name, ".")
EXT = Mid(fl.Name, PeriodPos + 1)
If UCase(EXT) = "JNK" Then
If fl.DateLastModified MyDate Then
Myfile = fl.Path
MyDate = fl.DateLastModified
End If
End If
Next fl
If Myfile = "" Then
MsgBox "There were no files found."
Else
MsgBox "The newest file is " & Myfile & _
" created " & MyDate

Workbooks.Open Myfile
End If

End Sub


"Sandy" wrote:

Hello
I am trying to open the most recent file in a folder. I have found 2
possible solutions here but neither seem to work for me.

First:
Const FilePath = "Mypath"
Workbooks.Open Filename:=FindNewestFile(FilePath)

This one gives me Undefined sub or function on
FindNewestFile

The other:

With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.Filename = "*.jnk"
.Execute SortBy:=msoSortBySize
.NewSearch
.LookIn = "mypath"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderDescending) 0 Then
MsgBox "The newest file is " & .FoundFiles(1) & " created " &
FileDateTime(.FoundFiles(1))
Workbooks.Open .FoundFiles(1)
Else
MsgBox "There were no files found."
End If
End With

This one gives me "Object doesn't support this action" on
With Application.FileSearch

Can anyone help?
Thanks!

You are searching for *.JNK which aren't workbooks. You can't open these
files from excel. What are you really doing? Is the search for XLS files?

"Sandy" wrote:

OK Thanks---I guess I didnt give enough information. From workbook A I want
to search and open workbook B. Once open I want to copy workbook B sheet 1
to workbook A. Then copy book B sheet 2 to workbook A etc. Im not sure how
to get back the second time.

Thanks again!

"Joel" wrote:

When a workbook is opened it automatically becomes the active workbook. You
can use ActiveWorkbook but I prefer

from
Workbooks.Open Myfile
to
set bk = Workbooks.Open(filename:=Myfile)

When using an equal sign in a workbook open you must use parenthesis. If
there is no equal sign, you have the option of using parenthesis or not. May
VBA instructions require the parenthesis when you have an equal sign.



"Sandy" wrote:

Thanks Joel I got that to work. Once I have the file open how do I reference
it.
Dim myBook As Workbook
Set myBook = ?
Something like that
"Joel" wrote:

Try this code. FILESEARCH doesn't work in excel 2007. This method will work
in all excel versions. Yu need to modify FilePath below as required.


Sub GetLatestFile()

Const FilePath = "Mypath"


Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(FilePath)

MyDate = 1
Myfile = ""
For Each fl In folder.Files
PeriodPos = InStrRev(fl.Name, ".")
EXT = Mid(fl.Name, PeriodPos + 1)
If UCase(EXT) = "JNK" Then
If fl.DateLastModified MyDate Then
Myfile = fl.Path
MyDate = fl.DateLastModified
End If
End If
Next fl
If Myfile = "" Then
MsgBox "There were no files found."
Else
MsgBox "The newest file is " & Myfile & _
" created " & MyDate

Workbooks.Open Myfile
End If

End Sub


"Sandy" wrote:

Hello
I am trying to open the most recent file in a folder. I have found 2
possible solutions here but neither seem to work for me.

First:
Const FilePath = "Mypath"
Workbooks.Open Filename:=FindNewestFile(FilePath)

This one gives me Undefined sub or function on
FindNewestFile

The other:

With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.Filename = "*.jnk"
.Execute SortBy:=msoSortBySize
.NewSearch
.LookIn = "mypath"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderDescending) 0 Then
MsgBox "The newest file is " & .FoundFiles(1) & " created " &
FileDateTime(.FoundFiles(1))
Workbooks.Open .FoundFiles(1)
Else
MsgBox "There were no files found."
End If
End With

This one gives me "Object doesn't support this action" on
With Application.FileSearch

Can anyone help?
Thanks!