Pausing in a macro
-
I have a macro in use, where one of the lines of code is to process a cmd line script (just basically zips up a dbf file) and then a couple of macro lines later, there is a line that emails that particular zip file that is created. (I did it this way to build in a delay)
It used to work just fine on my 1 GHZ celeron machine because I think the inherent slow down of things behind the scenes is god enough that the file is able to get zipped up and ready before the macro line to email it.
However, I have now changed to a XP64 machine that is blazingly fast, and it flys through everything so fast that the zip process hasn't had a chance yet to finish and have the file prepared before the line that tries to email it.
Therefore, I can either try to figure out how I used VBA in the past (I generally don't program so I'm a fair bit of a novice at it) and try to incorporate the Zip command into that piece, or I need to just build in a 2 or 3 second "wait" in the macro. And since I figure it's probably easier to build the wait in, I figured I would ask here first.
Any help would be appreciated greatly.
-
A bit of programming is (unfortunately) necessary. Neither runapp in a macro or shell in vba wait for things to finish.
However, those clever people over at the access web have got things sorted. Try http://www.mvps.org/access/api/api0004.htm to see the code you need. The directions are also quite clear.
-
That sure is a lot of code to pause for a few seconds. How about a For loop.
x=100000
For i = 1 to x
Next x
You could experiment with the value in x or you could use a Message Box since it waits for you to click OK.
MsgBox("Wait a few seconds before clicking OK")
-
Rather than a straight for loop like this I've seen
Do Until TestComplete()
DoEvents
Loop
The TestComplete function returns a boolean, i.e. true if the task has completed.
Like I say, I've seen it, but I don't ever use it. I'd much rather use the form's timer event to check for completion and take the necessary action when it happens.
It's much better as you won't be wasting processor resources spinning round and round in a tight loop when you should be leaving the processor free to do whatever you are waiting for.
Steve
-
Hi,
Above solutions are ok but this one uses a windows API to properly sleep rather than steal system resources floating around doing 10,000 times around a loop
paste the below into the top of a VBA module:
Private Declare Sub sapiSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
and then call the below function:
Function sSleep(lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Function
for example... if you want it to run for 5 seconds call the function like sSleep 5000 and that will pause the code without stealing much resources.
Cheers
Carl.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply