Return file name of last modified file

[NRK]

I am creating an Excel-based order entry system. What I need to create is a method that will look up the last 4 characters (file ID number) of the last file modified. This will be displayed on the form and will allow the user to know what ID was used last.

First, let me say that I think this could easily be designed in Access, but that my boss does not want that.

Second, I have played around with some code, but have not found anything that can look up other files (can look up file name on the file I am working on).

If anyone has any possible solutions to this question or has ideas about other possibilities for my overarching goal, I would greatly appreciate hearing from them.

Thank you, in advance.
Ben

 

[vb5prgrmr]

Not sure that this will help but...
[tt]
MsgBox Application.RecentFiles.Item(1).Name
[/tt]

Good Luck

 

[NRK]

vb5prgrmr - Thanks so much for the lead. Your code idea helped me generate ideas. Although I have gotten closer to a solution, I am still not there.

Please review the code below.

Code snippet:
Sub LastFileSaved()
Dim strLastMod As String

With Application.FileSearch
.LookIn = "L:\Research"
.SearchSubFolders = False
.FileName = "*.xls"
.MatchAllWordForms = True
If .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderDescending) > 0 Then
strLastMod = .FoundFiles(1)
ActiveSheet.Cells(6, 7) = strLastMod
Else
MsgBox "There were no files found."
End If
End With
End Sub

This code searches the specified folder for all xls files. It should, if I have done this correctly, sort them by the Last Modified field and return the most recently modified (seen by .FoundFiles(1)). Unfortunately, it returns the first file listed in the folder and doesn't seem to sort the files at all.

Ideally, I would like the code to sort through the folder and identify its contents (file_template.xls, file_101.xls) and return file_101.xls as it was the last file modified. I would then filter the results and post the "101" in a cell on the worksheet to let the user know the last used ID number.

Any input, as always, would be greatly appreciated.

Thanks and happy new year!
Ben

 

[RobBroekhuis]

Yes, very strange. Your code looks like it should work, and yet it doesn't. The files always come back in alphabetical order. Seems like there must be a FAQ out there addressing this problem...
Rob

 

[vb5prgrmr]

Well, since you know the path to where the files are located you could use the Dir function to populate a collection or a type with collections with one part of the type containing the path to the files and the other part of the type containing the results of the FileDateTime function to find what you want...
[tt]
Option Explicit
Private Type FileInfo
ThePath As New Collection
TheTime As New Collection
End Type

Private Sub Form_Load()
Dim Temp As String, F As FileInfo, I As Integer

Temp = Dir("C:\*.*&quot
Do While Temp <> &quot;&quot;
If Temp <> &quot;.&quot; And Temp <> &quot;..&quot; Then
If (GetAttr(&quot;C:\&quot; & Temp) And vbDirectory) <> vbDirectory Then
F.ThePath.Add &quot;C:\&quot; & Temp
F.TheTime.Add FileDateTime(&quot;C:\&quot; & Temp)
End If
End If
Temp = Dir
Loop

For I = 1 To F.ThePath.Count
Debug.Print F.ThePath.Item(I) & &quot;,&quot; & F.TheTime.Item(I)
Next I

End Sub
[/tt]

That should be enough to get you started.

Good Luck

 

[RobBroekhuis]

Absolutely. But I'm still curious why the FSO method doesn't work...
Rob

 

[NRK]

vb5prgrmr -
Thanks for the code snippet. Unfortunately, I cannot seem to get it to work. I initially tried modifiying it to my needs, but when that didn't work, I just cut and pasted your code into my editor and tried running that.

I keep getting a Run-Time Error '5' for the line:
If (GetAttr(&quot;C:\&quot; & Temp) And vbDirectory) <> vbDirectory Then
But, I am unsure what it is snagging on. If you or anyone else has any thoughts on this, I would greatly appreciate it.

Thank you, in advance.

 

[combo]

Perhaps this will help you to solve problem:

With pure VBA you have limited access to the registry (.../Software/VB and VBA Program Settings) via SaveSetting statement, GetSetting and GetAllSettings functions.
You can use Workbook_BeforeSave event, but in this case you need a code in each workbook. Instead it is better to use WorkbookBeforeSave Application event to write data (workbook's full path and name) into registry, but to trace all workbooks it is necessary to create class module (here with name clsApp) with code:

Public WithEvents AppEvents As Application

Private Sub AppEvents_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call subUpdLog(Wb)
End Sub

In the standard module you need:
1) a declaration:
Dim appTrace As New clsApp

2) a code executed in a procedure (during opening/installing your application):
Set appTrace.AppEvents = Application

3) a procedure updating registry while saving workbook, here named subUpdLog(wb as Workbook). You can add additional parameters to decide if saved workbook has to be recorded.

4) a procedure reading registry key.

To write in registry, use:
SaveSetting appname:=&quot;YourApp&quot;, section:=&quot;Files&quot;, Key:=&quot;LastSaved&quot;, setting:=WorkbookData

To read registry, use:
= GetSetting(appname:=&quot;YourApp&quot;, section:=&quot;Files&quot;, Key:=&quot;Lastsaved&quot

See VBA help file for details on functions.


combo

 

[vb5prgrmr]

Did a quick test on Excel2ksp2 with my code called from a command button placed on a sheet and had no problem. Don't know what else to tell you.

Only reason I can guess that the FSO (File System Object) method is not working is you is you need to add a reference to microsoft scripting runtime.

Good Luck