Macro code that will look for the most recent workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have a macro that opens specific workbooks but I wanted to know
if there was a way to tell the macro to open the most recent workbook. I
have several workbooks that are saved as "name today's date.xls".

So it would look in this folder and find either the workbook with the most
recent date or the workbook that has been save last.

Any Suggestions would be greatly appreciated.


Thank You,


Judd
 
jbsand1001 said:
I currently have a macro that opens specific workbooks but I wanted to know
if there was a way to tell the macro to open the most recent workbook. I
have several workbooks that are saved as "name today's date.xls".

So it would look in this folder and find either the workbook with the most
recent date or the workbook that has been save last.

Any Suggestions would be greatly appreciated.


Thank You,


Judd


Sub OpenMostRecent(strFolderName As String, Optional blnSearchSubFolder As
Boolean = True)
On Error GoTo Err_Handler

Dim strFileName
With Application.FileSearch
.LookIn = strFolderName
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = blnSearchSubFolder
If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
strFileName = .FoundFiles(1)
Application.Workbooks.Open strFileName
End If
End With
Exit_Sub:
Exit Sub
Err_Handler:
Resume Exit_Sub
End Sub
 
jbsand1001 said:
I currently have a macro that opens specific workbooks but I wanted to know
if there was a way to tell the macro to open the most recent workbook. I
have several workbooks that are saved as "name today's date.xls".

So it would look in this folder and find either the workbook with the most
recent date or the workbook that has been save last.

Any Suggestions would be greatly appreciated.


Thank You,


Judd

Sub OpenMostRecent(strFolderName As String, Optional blnSearchSubFolder As
Boolean = True)
On Error GoTo Err_Handler

Dim strFileName
With Application.FileSearch
.LookIn = strFolderName
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = blnSearchSubFolder
If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
strFileName = .FoundFiles(1)
Application.Workbooks.Open strFileName
End If
End With
Exit_Sub:
Exit Sub
Err_Handler:
Resume Exit_Sub
End Sub
 
Islands,
Thanks for this information. I am a little confused on how I tell
this syntax where to look. My idea was "p:\macros\", but that does not see
to work. I am trying to direct it to go to a certain directory then specific
file.


Thanks,

Judd
 
Back
Top