Help on coding please?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Does anyone have any suggestions on how to perform following tasks in macro?
I would like to check the last updated for the file ABC.xls, if the last
updated is today after 10am, then perform macro, else skip running the rest
of coding.
Does anyone have any suggestions?
Thank you in advance for any suggestions
Eric
 
Note that I am indicating the file's path be provided along with its name...

If FileDateTime("c:\FilesPath\ABC.xls") > Date + TimeSerial(10, 0, 0) Then
' The file's last modified date is later than 10am today
End If

The FileDateTime function is a built-in function in VBA which returns the
last modified date for the PathFileName passed into it as an argument.

Rick
 
Thank you very much for your suggestions

What if I would like to create a loop on monitoiring the file ABC.xls
status, anytime if the file ABC.xls is updated after 10am of today, then
perform the macro, else skip and wait.
Do you have any suggestions?
Thank you very much for any suggestions
Eric
 
You could try this. Go into the VBA editor and add a Module to your project.
Copy/Paste this code into its code window...

Sub CheckFile()
Const PathFileName As String = "c:\FilesPath\ABC.xls"
If FileDateTime(PathFileName) > Date + TimeSerial(10, 0, 0) Then
'
' Either put the code you want to run here or call
' a subroutine that has the code you want to run
'
MsgBox "Updating complete"
Else
Application.OnTime Now + TimeSerial(0, 0, 30), "CheckFile"
End If
End Sub

Now, double click the ThisWorkbook item in the Project Explorer window and
add this code to its code window...

Private Sub Workbook_Open()
CheckFile
End Sub

or, if you already have Workbook_Open event code, add the call to CheckFile
at the end of your code.

NOTES
=========
1) Change the PathFileName constant to point to the path and filename that
you want to check.

2) In the CheckFile subroutine, I have TimeSerial(0, 0, 30) being added to
the Now function. This adds 30 seconds to the value of Now. The purpose for
the entire statement this code is in is to set the timer to call CheckFile
over and over again (as long as the file has not been updated) every 30
seconds. Change the TimeSerial(0, 0, 30) values to whatever repeat time
value you want to use.

Rick
 
Back
Top