open file if was created/saved within 30 minutes

[dmtcrainey]

I love this board.

I think I have a solution to my issue from a previous post, but wanted someone else's expertise.

I have a macro that opens a hard codeed file we will call "C:\stuff.txt".

4 times a day "C:\stuff.txt" is exported from another program and overwrote to this location, or possibly it is now being recreated. (did that make sense?).

I want to idiot proof my macro and only run my other code if the file was created or last saved withing so many minutes. Will this code work for that?

Sub stuff()

Dim Dte As Date

Const FileSpec As String = "C:\stuff.txt"

Set Fs = CreateObject("Scripting.FileSystemObject")

Set f = Fs.GetFile(FileSpec)

' Dte = f.DateCreated
' Dte = f.DateLastAccessed
Dte = f.DateLastModified
'for more attributes, see http://msdn2.microsoft.com/en-us/lib...af(VS.85).aspx

Set f = Nothing

Set Fs = Nothing

If ((Now - Dte) * 60 * 24) > 30 Then '((Now - Dte) * 60 * 24) is difference in Minutes
'msgbox with error message
Exit Sub
else
'Run my code
End If


End Sub

 

[al_b_cnu]

Hi,

Looks good to me

I just put a msgbox to test it:

MsgBox "Now  = " & Format(Now(), "dd/mm/yy hh:mm:ss") & vbCrLf & _
       "Then = " & Format(Dte, "dd/mm/yy hh:mm:ss") & vbCrLf & _
       "Mins = " & ((Now - Dte) * 60 * 24)

 

[Sumeluar]

I've benn looking for something like that. Does anybody knows how to check several files (about 500) at the same time?

Regards!

Sumeluar

 

[al_b_cnu]

I've benn looking for something like that. Does anybody knows how to check several files (about 500) at the same time?

Regards!

Sumeluar

Hi,

Perhaps you could combine this: http://j-walk.com/ss/excel/tips/tip18.htm

with the above.