Solved: FILE DATE

[oleg_v]

Hi

I have a macro that gives me an opportunity to choose a file to open.
My question is what can i add to my macro that will open the file that was last modified in particular folder.

the part of the macro:
Sub IMPORTTEXT()

oldworkbook = ThisWorkbook.Name


' Get the file pathname
Set oXL = CreateObject("Excel.Application")
f_name = oXL.GetOpenFilename("All Files (*.*),*.*", , "Select file", False)
'f_name = oXL.GetOpenFilename("Text Files (*.txt),*.txt", , "Select file", False)
If f_name = "False" Then
'user cancelled out of dialog box
Set oXL = Nothing
Exit Sub
End If
'store file pathname in the file_pathname variable
'MsgBox f_name
'ret = ActiveDocument.GetVariable("file_pathname").SetContent(f_name, False)
Set oXL = Nothing

thanks

[Bob Phillips]

You would need to use FileSystemobject, grab all the files and their modified date, and check for that latest.

[oleg_v]

Thanks
but how i put your words into a macro??

[Bob Phillips]

Dim oFSO As Object
Dim Folder As Object
Dim file As Object
Dim SavedDate As Date
Dim SavedFile As String

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\Test")

For Each file In Folder.Files

If file.Type Like "*Microsoft Office Excel*" Then

If file.DateLastModified > SavedDate Then

SavedFile = file.Path
End If
End If
Next file

MsgBox SavedFile

Set oFSO = Nothing

[oleg_v]

sorry for the stupid question but

how can i paste it into my macro?

thanks

[Bob Phillips]

As you said, paste it in.

[oleg_v]

hi
i am tying to paste it and the macro give me an error.
i wanted that macro will open the folder "c:/test" and
take from there the latest made text file .
i am doing this with my macro manualy
i can not combine between them

[mdmackillop]

Depending upon your text file, certain parameters may need to be changed. Record a macro opening your text file and modify accordingly.


Sub OpenTextFile()
Dim oFSO As Object
Dim Folder As Object
Dim file As Object
Dim SavedDate As Date
Dim SavedFile As String

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\Test")

For Each file In Folder.Files
If file.Type Like "*Text Document*" Then
If file.DateLastModified > SavedDate Then
SavedFile = file.Path
End If
End If
Next file

Workbooks.OpenText Filename:=SavedFile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False

Set oFSO = Nothing
End Sub

[ZVI]

Hi,

It looks like the updating of SavedDate has been missed in the code.
Not sure about English localization of Oleg’s PC, but the Type property of FSO file object is language dependent.
In my Russian localization for XLS files it returns "Sheet Microsoft Excel" where Sheet is in Russian.
And "Text File" in English is for TXT files.

Tweaked VBA code:

Sub OpenLastModifiedTxtFile()

' --> User Settings
Const MyFolder = "C:\Test"
Const MyMask = "*.TXT" ' should be in upper case
' <-- End of User Settings

Dim f As String, d As Date, fd As Date, x

On Error GoTo exit_
For Each x In CreateObject("Scripting.FileSystemObject").GetFolder(MyFolder).Files
If UCase(x.Name) Like MyMask Then
fd = x.DateLastModified
If d < fd Then
d = fd
f = x.Path
End If
End If
Next

exit_:

' Errors
If Err Then MsgBox "Folder not found: " & MyFolder, 48, "Warning": Exit Sub
If d = 0 Then MsgBox "File not found: " & MyMask, 48, "Warning": Exit Sub

' Happy return
MsgBox "Last modified file: " & f & vbLf & "DateTime: " & fd, 64, "Found"

' Below would be your code to do something with the found file
'Workbooks.OpenText f

End Sub
Regards,
Vladimir