How to hold Macro running?

[Eric]

Refer to the post in Worksheet

Does anyone have any suggestions on how to hold Macro running until specific
file being updated?

When I run a Macro, in the middle of the process, I would like to hold Macro
running until the periods between the last updated time for specific file and
the current time is less than a hour.
For example, a Macro is running under the Eric.xls

Sub temp()
Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\A.xls", UpdateLinks:=3
Workbooks("A.xls").Close savechanges:=True

Before processing the next step, I would like to check the last updated time
for specific files - Mary.xls with the current time. If the difference
between the last updated time for Mary.xls and the current time is less than
1 hour, then process the next coding, else wait until the difference periods
is less than 1 hour.

Workbooks.Open Filename:="C:\B.xls", UpdateLinks:=3
Workbooks("B.xls").Close savechanges:=True

End Sub

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Bill Renaud]

Eric wrote:
<<When I run a Macro, in the middle of the process, I would like to hold
Macro running until the periods between the last updated time for specific
file and the current time is less than a hour.>>

This will result in an endless loop that runs constantly, since the current
time will always be less than one hour since the last update, if you just
ran your code!!!
Are you sure you meant instead to wait until 1 hour has passed, then update
the file?

 

[mark]

I think he said what he meant.

he said:

if now() - timestamp(mary.xls) < 1 hour then

run my code

else

wait until mary.xls is less than an hour old
run my code

end if

either way, he wants to run the code, so it can be changed to:


Do While now() - timestamp(mary.xls) > 1 hour

Loop

Run my code

You might want to put something into your loop to give an option to get out,
so that it isn't an endless loop... or you might not, depending upon what YOU
want.

You can get the timestamp of mary.xls using the FileSystemObject

Here's a sample:

Sub sbTest()

Const cnFile = "c:\test.xls"

Dim myFileTime As Date
Dim i As Long


myFileTime = fnFileDate(cnFile)
MsgBox Now() - myFileTime
Do While Now() - myFileTime > (1 / 24)

i = i + 1
If i > 10000 Then

Exit Do

End If
Loop

MsgBox "I can go!"


End Sub

Function fnFileDate(filename) As Date

'dimension variables

Dim fs As Object
Dim f As Object


'assign variables

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfile(filename)
fnFileDate = f.DateLastModified

End Function



You'll want to take that Exit Do out and work it up a little... I just put
that in three to get myself out of the loop on the test, because my test.xls
file is 65 days old and I got into the loop!

 

[Bill Renaud]

OK, so Eric's program is waiting for another process to update the file,
before it updates everything. I guess I missed that concept!

You might want to put some sort of 5 minute wait inside the loop, so that
your routine is not checking the file constantly! This might tie up the
file server so much that the other process would have a difficult time
making its update, which your routine is waiting on.