Making macros Wait

[aswini.ks]

Hi

I am trying to write a macro that will automate a series of similar
tasks. In each task I have to wait for a report to finish calculating
before saving the output file and then going on to the next task.

The reports are processed using the Jet Reports add-in and I am facing
problems in making the macro wait for about 10 minutes till the report
processing is completed and the output file saved. I have tried the
following without success:

(1)

For(from report 1 to 5)
---
----
Application.OnTime Now + TimeValue("00:10:00"), SrcFlPath + DataFlName
+ "!Module1.SaveFile"
Next report

When Excel encounters this statement, I think it starts a timer but the
control goes to the next statement in my loop and begins to process the
next report. However, the next task should not begin until the earlier
task is completed.

(2) So the next method I tried was to make the macro "Sleep". Although
this makes the macro wait for 10 minutes before saving and proceeding
to the next task, it interferes with the report processing! As a result
the output files saved don't have the processed results.

For(from report 1 to 5)
---
----
'Setting the Timer 10 minutes in seconds
Timer = 600
Do While Timer > 0
' Macro sleeps for 1 second
Sleep 1000
Timer = Timer - 1
Loop
SaveFile()
Next report

Any tips to solve this problem? Thanks in advance for your help.

Aswini

 

[Guest]

This might help:

Try making a seperate module named APIFunctions then input the folloing code:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This Module contains Private API Declarations and Public User functions.
The Public User
' Functions are designed for user access to functionality provided by API
Functions.
' These functions are designed to simplify the use of API functions.

' Procedure will delay execution of calling procedure for x number of
seconds.
Public Sub wait(X As Long)
Dim loopnum As Integer
loopnum = 0

Do While loopnum < X
DoEvents
loopnum = loopnum + 1
Sleep 1000
Loop

End Sub


using this code you can then use the "wait x" with "x" being how many
seconds you want
example: wait 600

This will make vb wait 10 minutes for the nextportion of the code.

Good luck

 

[aswini.ks]

ewagz,

Could you please explain how you declare Wait as an API function?

Thanks once again.
Aswini

 

[Chip Pearson]

There is no Wait API function. Perhaps you are thinking of the
Wait method of the Application object. E.g.,

Application.Wait (Now + TimeSerial(0, 0, 5))

There is a Sleep API function:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub AAA()
Sleep 5000& ' 5 seconds
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com