Open most recent file

[Sandy]

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!

 

[Joel]

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]

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]

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]

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]

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]

Youre right. I had read that there was a problem with file search in 2007
and this was reported as a fix. There is actually another iteration where
xls is specifed
I missed it when I clipped the code out to post. So the file actually opens
when I execute the code. So now what?

Thanks

 

[Joel]

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) = "XLS" 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

Set bk = Workbooks.Open(Filename:=Myfile)
For Each sht In bk.Sheets
With ThisWorkbook
sht.Copy after:=.Sheets(.Sheets.Count)
End With
Next sht
bk.close savechanges:=False
End If

End Sub

 

[Sandy]

Thanks again
It looks like this may work but it is bringing over links. Is there anyway
to leave the links behind?

Thanks!

 

[Joel]

Not sure what you want. There ae a few intepretation See items 1 - 4 below.

1) Just copy values. Use Paste Special values. This will loose a formulas
2) Unlink

Removes the link to a Microsoft Windows SharePoint Services site from a list.

Remarks
After this method is called and the list is unlinked, it cannot be reversed.

Example

The following example unlinks a list from a SharePoint site.

Sub UnlinkList()
Dim wrksht As Worksheet
Dim objListObj As ListObject

Set wrksht = ActiveWorkbook.Worksheets("Sheet1")
Set objListObj = wrksht.ListObjects(1)

objListObj.Unlink
End Sub

3) Breaklink

Converts formulas linked to other Microsoft Excel sources or OLE sources to
values.

expression.BreakLink(Name, Type)
expression Required. An expression that returns one of the objects in the
Applies To list.

Name Required String. The name of the link.

Type Required XlLinkType. The type of link.

XlLinkType can be one of these XlLinkType constants.
xlLinkTypeExcelLinks A link to a Microsoft Excel souce.
xlLinkTypeOLELinks A link to an OLE source.

Example
In this example, Microsoft Excel converts the first link (an Excel link
type) in the active workbook. This example assumes at least one formula
exists in the active workbook that links to another Excel source.

Sub UseBreakLink()

Dim astrLinks As Variant

' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

' Break the first link in the active workbook.
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks

End Sub

4) ChangeLink

Changes a link from one document to another.

expression.ChangeLink(Name, NewName, Type)
expression Required. An expression that returns one of the objects in the
Applies To list.

Name Required String. The name of the Microsoft Excel or DDE/OLE link to
be changed, as it was returned from the LinkSources method.

NewName Required String. The new name of the link.

Type Optional XlLinkType. The link type.

XlLinkType can be one of these XlLinkType constants.
xlLinkTypeExcelLinks default
xlLinkTypeOLELinks. Use for both DDE and OLE links.

Example
This example changes a Microsoft Excel link.

ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _
"c:\excel\book2.xls", xlExcelLinks

 

[Joel]

Not sure what you want. There ae a few intepretation See items 1 - 4 below.

1) Just copy values. Use Paste Special values. This will loose a formulas
2) Unlink

Removes the link to a Microsoft Windows SharePoint Services site from a list.

Remarks
After this method is called and the list is unlinked, it cannot be reversed.

Example

The following example unlinks a list from a SharePoint site.

Sub UnlinkList()
Dim wrksht As Worksheet
Dim objListObj As ListObject

Set wrksht = ActiveWorkbook.Worksheets("Sheet1")
Set objListObj = wrksht.ListObjects(1)

objListObj.Unlink
End Sub

3) Breaklink

Converts formulas linked to other Microsoft Excel sources or OLE sources to
values.

expression.BreakLink(Name, Type)
expression Required. An expression that returns one of the objects in the
Applies To list.

Name Required String. The name of the link.

Type Required XlLinkType. The type of link.

XlLinkType can be one of these XlLinkType constants.
xlLinkTypeExcelLinks A link to a Microsoft Excel souce.
xlLinkTypeOLELinks A link to an OLE source.

Example
In this example, Microsoft Excel converts the first link (an Excel link
type) in the active workbook. This example assumes at least one formula
exists in the active workbook that links to another Excel source.

Sub UseBreakLink()

Dim astrLinks As Variant

' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

' Break the first link in the active workbook.
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks

End Sub

4) ChangeLink

Changes a link from one document to another.

expression.ChangeLink(Name, NewName, Type)
expression Required. An expression that returns one of the objects in the
Applies To list.

Name Required String. The name of the Microsoft Excel or DDE/OLE link to
be changed, as it was returned from the LinkSources method.

NewName Required String. The new name of the link.

Type Optional XlLinkType. The link type.

XlLinkType can be one of these XlLinkType constants.
xlLinkTypeExcelLinks default
xlLinkTypeOLELinks. Use for both DDE and OLE links.

Example
This example changes a Microsoft Excel link.

ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _
"c:\excel\book2.xls", xlExcelLinks