Is there are a VBA pause function/statement?

Topic

New Reply

HI ya

I need my code to pause for 5 seconds.
I remember when one first becomes a developer they get you to write a simple bit of code that pauses & does a count down for 5 10 seconds.

Is there a VBA function or syntax to make application/code pause for a certain amount of time?
TIA Diana

Reply | Quote

Replies

Diana

The following line of code should cause a pause for 5 seconds. Yoy can alter the duration by changing the value passed to TimeValue

	Application.Wait Now() + TimeValue("00:00:05")

Andrew C

Reply | Quote

Hmm, looks like that one is available in Excel but not in Word; darn shame….

Reply | Quote

Yes, sorry I did not realise it was unavailable in Word, a strange omission.

Andrew

Reply | Quote

Hi Andrew,

No need for you to be sorry, you didn’t build the object model!

Seriously, as Word-centric as my worldview is, I appreciate finding out about these things outside my purview.

It would probably be interesting to try to put together a list of all of the functions that are available in one application, but not others. My favorite Word-only example is the Tasks method.

The reasons for why these things ended up being available in one app, and not another, would probably make for some interesting stories, could we but know them.

Gary

Reply | Quote

** Geoff W Long line in “pre” tags split ***

Another one, or two:

You can get the GUID for Access visd VBA, but you cannot get the Product ID via Access, so you can use Word to get the Product ID via Automation from Access, e.g.:

Option Compare Database
Option Explicit

Private Sub GetPID()
    Dim strProductID As String
    Dim strAssignedUserName As String
    Dim strAssignedOrganization As String
    Dim strGUID As String
    Dim wdApp As Word.Application
    strGUID = Application.ProductCode
    Set wdApp = New Word.Application
    With wdApp
        With .Dialogs(wdDialogHelpAbout)
            strProductID = RTrim$(.APPSERIALNUMBER)
            strAssignedUserName = RTrim$(.APPUSERNAME)
            strAssignedOrganization = RTrim$(.APPORGANIZATION)
        End With
    End With
    MsgBox strGUID & vbCr & strProductID & vbCr & strAssignedUserName & _
      vbCr & strAssignedOrganization
    wdApp.Quit
    Set wdApp = Nothing
End Sub

I’ve seen folkes use Excel to sort an array, when they could just as well have used WordBasic.SortArray.

Etc.

Reply | Quote

I suspect that the programmers who worked on Word were all on the 3rd floor of the Microsoft Building, the ones who worked on Excel were on the 4th floor and the Access team were all on the 9th floor.

Now ask me where the cafeteria is? Not in the same building and thus the people from these floors never intermingled and thus we have all these issues in what is supposedly a suite of applications that should work together.

Go figure…

Wassim

Reply | Quote

And even if all the Word programmers were on the 3rd floor, they must have been on different wings, for example:

Suppose you need to act upon the first Shape in a new document based on a template, and scale its height. To do so, you need to use the ScaleHeight method, for example:

ActiveDocument.Shapes(1).ScaleHeight 1.75, True

Now suppose for reasons of formatting, you’ve (manually) changed the Shape in the template to an InlineShape, and now you need to scale the height of an InlineShape instead. So of course, you would use the ScaleHeight property, for example:

ActiveDocument.InlineShapes(1).ScaleHeight = 150

Obvious, of course….

Gary

Reply | Quote

Based on what the above have offered:

WaitUntil = Now() + TimeValue("00:00:05")
Do While Now < WaitUntil
Loop

seems to work in both.

Reply | Quote

> Do While Now < WaitUntil

Thank you. Just what the doctor ordered to allow my little DOS batch file enough time to glue three files together using the DOS COPY command.

Reply | Quote

Take a look at the OnTime method. You can use it to schedule another macro to run at some time in the future. At the place where you need to pause, just put an OnTime to schedule the remaining code, in another macro, to start in 5 seconds.

Reply | Quote

I’ve used this code but unfortunately it doesnt satisfy my requirements.
When I use these statements it stops the application & therefore impacts on what I’m doing.

what I want to do is a loop
check if a file is free ie completed its execution
if not free
do something else for 5 seconds &
then check if a file is free

What I need is some timer code or counter.
Does anyone have code that does this?

thanks in advance Diana

Reply | Quote

Diana,

I copied thr following code from the Help (Timer) and it seesm to work on Word ok.

Dim PauseTime, Start, Finish, TotalTime
If (MsgBox(“Press Yes to pause for 5 seconds”, 4)) = vbYes Then
PauseTime = 5 ‘ Set duration.
Start = Timer ‘ Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish – Start ' Calculate total time.
MsgBox "Paused for " & TotalTime & " seconds"
Else
End
End If

Can you adapt it to do what you want ?

Andrew C

Reply | Quote

HI Andrew

I’ve tried code – but it doesnt resolve what I’m attempting to do.

What I’m doing is sending the current file to printer driver (this is how we create pdf files)
The file spools to printer
when I use the timer code – it actually pauses & stops the file being sent to the printer
& then when timer ends – then the rest of the file is sent to printer.
The problem is I attempt to delete or kill file but it is still open & being used by app. & I get a run time error message – file sharing access.

What I want to do is send file tio printer – when completed spooling – this means the file is free & I can then delete the file using ‘kill’ statement.

I thought a way to solve – while file is being sent to printer – do something else for a number of seconds
– then check if file is free
– if file still not free –
loop until file is free.
Once file free – delete file.

Do you have any ideas on how I can do this? thanks Diana

Reply | Quote

I think that the only way you are going to be able to get this to work is to use the OnTime method to schedule a second macro to run at the desired time interval in the future and then exit from the current macro. The second macro can then pick up doing what you want to do after the pause.

Reply | Quote

Diana,

Did you ever get this resolved? I have an idea if you are still looking…

Reply | Quote

Kevin – no I didnt get this resolved (see post from me to Andrew)
Whats your idea?…..Please let me know – I’ll have a go to resolve – regards Diana

Andrew
I’ve tried code – but it doesnt resolve what I’m attempting to do.

What I’m doing is sending the current file to printer driver (this is how we create pdf files)
The file spools to printer
when I use the timer code – it actually pauses & stops the file being sent to the printer –
therefores pauses the file being sent/spooled to printer.
& then when timer ends – then the rest of the file is released & sent to printer.
The problem is I attempt to delete or kill file but it is still open & being used by app. & I get a run time error message – file sharing access.

What I want to do is send file tio printer – when completed spooling – this means the file is free & I can then delete the file using ‘kill’ statement.

I thought a way to solve – while file is being sent to printer – do something else for a number of seconds
– then check if file is free
– if file still not free –
loop until file is free.
Once file free – delete file.

Do you have any ideas on how I can do this? thanks Diana

Reply | Quote

Are you running a different program and waiting for it to finish its execution? One way to handle this is as follows:

{In the Declarations}
Private Declare Sub Sleep Lib “kernel32” (ByVal nMilliseconds As Long)

{In the Sub}
…
batTaskID = Shell(“c:JFS_FDIR.bat”, vbHide) ‘run batch file
While Tasks.Exists(“JFS_FDIR”) ‘monitor for the window title
Sleep (1000)
nSleepCounter = nSleepCounter + 1
StatusBar = “Word is waiting…” & nSleepCounter ‘show user, not frozen
Application.ScreenRefresh
Wend
Tasks(“Finished – JFS_FDIR”).Close ‘close the Finished DOS window
…

However, the above batch file now runs so fast on my new PC that this code doesn’t work. I inverted the condition:

…
batTaskID = Shell(“c:JFS_FDIR.bat”, vbMinimizedNoFocus) ‘run batch file
Do Until Tasks.Exists(“Finished – JFS_FDIR”) ‘monitor for the window title
Sleep (1000)
nSleepCounter = nSleepCounter + 1
StatusBar = “Word is waiting…” & nSleepCounter ‘show user, not frozen
Application.ScreenRefresh
Loop
Tasks(“Finished – JFS_FDIR”).Close ‘close the Finished DOS window
…

Hope this helps.

In another thread, SammyB posted this MSKB reference which offers a completely different approach. The code is very, very ugly, but it seems to run as written.

Reply | Quote

Diana: If I understand what you want to do, JSCher2000’s solution will not work because you are waiting for the print spooler to finish processing your print file, not for a task that you have started to finish.

I have sent you two messages, one on 22 Jun and one on 12 Jul, telling you how to do this. Have you tried it? It does work. I haven’t gotten any reply saying that you didn’t understand or that it did not work. If you want some help implementing it, I will need to see your code to be able to tell how to fit it in.

Reply | Quote

I was going to boil it all down for you, but I’ve not the time now. But
this article might be just what you need.

Reply | Quote

hyperlink added

Hi Kevin

I’ve had a look at the article you pointed me to –
I will try it & see if it resovles my issue.

Sub Form_Click()
Dim retval As Long
retval = ExecCmd(“notepad.exe”)
MsgBox “Process Finished, Exit Code ” & retval
End Sub

However in the Sub Form_Click()

the line
retval = ExecCmd(“notepad.exe”)

notepad.exe is used as the sample to test if the application has completed.
How would I write the line of code to test for a printer name or printer job is completed?

I’ve found the following microsoft article

Q160129 – HOWTO: Get the Status of a Printer and a Print Job

However the sample code isnt VBA & I dont know what it is or how to user it?
any ideas? Diana

Reply | Quote

Diana,

Let’s try to bag this puppy once and for all. But first, a bit of clarification. So bear with me. I’ll help you, but I need some more info.

Once you send the job to the printer, what does your program do? Does it have a UI (or toolbar or just menu items) the user clicks to perform other actions such as open documents, check stock quotes and view the break-room soda machine via webcam?

See, you’ll poll the printer for the job completion based on either an appplication level event (or timer) or a loop that does not exit until the job is finished.

I take it from previous posts that the loop is not satistactory because your program will just sit there tying up the app. You want to start the job and let the user do other things, right?

So, you need to determine what action(s) taken by the user will invoke the polling function to test for the job completion, or, as others have suggested, start a timer that will do the checking at some later time. That’s not a bad idea. Two minutes after the timer starts, the polling function is invoked and checks for job complete; if done, the file is deleted. If not, it sets another timer to check again in 2 minutes (or whenever).

With an application level event, you can invoke the polling function when a user opens a document, creates a new document, changes a document or when the user clicks on menu, toolbar or UI buttons. You decide which works best and post back. I’ve got the polling/deleting code ready. Do you want it called via a timer or user invoked events?

Reply | Quote

Some real-live code attached. Hope that wraps it up.

Change the lines below:

If aJob.pDocument Like "*" & ActiveDocument.name Then

Reply | Quote

Kevin

I tested the code attached – the sub pollPrinter was exactly what I was looking for (yeah!!!!) – thank you.

The line of code
DoEvents

when I use this line – it seems to take forever to return back to the code (& plus I dont really understand what it does).

I test your code below that calls SubPollPrinter &
also
write some code that calls the sub PollPrinter.
perhaps
loop until PrintJobCompleted
get system time
wait 5/10 seconds
then call subPollPrinter.

Will let you know how I go! – cheers Diana

Reply | Quote

Good to hear you’ve got resolution. I rather enjoyed pulling that routine together and hope to find a use for it myself. I think it gives real meaning to a progress indicator. I believe you can even poll the print job for current page printing (using JOB_INFO_2 instead of JOB_INFO_1). Woop dee do!

Do Events gives the Operating System the opportunity to give clock cycles to other (non-Word) tasks. Since the print job is managed by Windows, not the app, I thought it best to allow Windows time to process the print job. Make sure you set Word to allow background printing. That might speed things up. Heck, If you get better performance without Do Events, remove it. My theory may be flawed.

Reply | Quote

Hi kevin – this issue is now resolved.
The printer spooling code was excellent.
however I still had to write a timer code to call the printer spooling function.
whatever method I used eg OnTime, DoEvents,Pause,check every few seconds
the result was the application actually paused therefore the spooling paused as well
OK if I stepped through code – however if I ran the code would pause application & spooling & seem to be in an eternal loop.

My solution to this housekeeping issue is the following
in C:temp diretory create another temp folder
eg C:TempConvert
Make the Convert directory hidden – save the word file in this directory
& the pdf file in C:temp.

This resolves
as the standard operating environment – users have the windows view set to hide file types therefore wont see this ‘2nd temp’ directory.
– theres no danger of users drilling down to C:tempconvert & accidentally emailing the word file as
users cannot see this directory.
& most users being IT illerate wont know how to enable this feature.
also the login script cleans out the C:temp directory each time user logs on/off.

resolved – WHOA!!!!!!!!!!!!!!!!!!!!!!!!!
thanks all for all your help!!!!!
regards Diana

Reply | Quote

It’s always a thrill, ain’t it, when we get something to work? And it certainly was a team effort on this one.

Reply | Quote

[indent]

---

I was going to boil it all down for you, but I’ve not the time now. But this article might be just what you need.
http://support.microsoft.com/default.aspx?…&NoWebContent=1

---

[/indent]

Kevin,

I dug up this reference recently… This code works GREAT to wait for an external application to complete its operation. The only thing left that I need is to somehow grab the errorlevel of a batch file that I am running.

I considered dumping the results to a log file and review the results; the problem is that there will potentially be multiple people running this tool simultaneously, so I am not guaranteed exclusive access to any given log file(s).

Do you or anyone else know of a way to not only wait for the completion of a DOS program (batch file, etc), but also grab its errorlevel upon completion?

thanks,
..dane

Reply | Quote

Here’s an alternate way to check to be sure a process is finished, and could provide a means to lock the log file you’re considering using. This is written about Perl, but the general strategy is certainly applicable to any other language.

http://www.tpj.com/documents/s=7178/sam0206j/%5B/url%5D

The code you have to read/write to the logfile could first check to be sure no other process is accessing it.

Reply | Quote

Thanks for the reply;

something like that can get very cumbersome in a DOS batch file, much less if something is screwy and the user executing the batch file may not even have rights to the designated log file.

I’m looking for a cleaner solution. It shouldn’t be difficult to grab the ERRORLEVEL of the terminated batch file, should it? Just write a simple batch file that executes “SET ERRORLEVEL=1” and try to grab it with any of the suggestions online to grab errorcodes. All I ever get is an exit code of zero (0)..

Maybe there is a difference between the “errorlevel” of a program’s termination and the “exit code” of a process, and therein may lie the solution?

Thanks,
..dane

Reply | Quote

Thanks for the reply;

something like that can get very cumbersome in a DOS batch file, much less if something is screwy and the user executing the batch file may not even have rights to the designated log file.

I’m looking for a cleaner solution. It shouldn’t be difficult to grab the ERRORLEVEL of the terminated batch file, should it? Just write a simple batch file that executes “SET ERRORLEVEL=1” and try to grab it with any of the suggestions online to grab errorcodes. All I ever get is an exit code of zero (0)..

Maybe there is a difference between the “errorlevel” of a program’s termination and the “exit code” of a process, and therein may lie the solution?

Thanks,
..dane

Reply | Quote

Edited to put before the link and after the link to make it active – see Help 19 – Mod
All,

I’ve finally gotten it resolved.. it took a little Google-ing, and several variations on the “wait for external process to terminate” mechanisms. I detail my attempts below for those interested (hopefully to also be more easily found when searching through the archives)

I originally wanted to wait for a process to complete, and found ExecCmd(). Next I knew that I needed to also grab the errorlevel (exit code) that the command returns when it terminates, and ExecCmd() didn’t seem to do the job. I looked around various forums, and found ExecCmd2() and ExecCmd3() (note I’ve changed their function names, I think they were all called “ExecCmd” when I found them). Also note that I changed them slightly from their original form to actually return the return value / exit code of the process.

I have attached all three in a zip file for anyone’s investigation/usage.

ExecCmd1() while correctly functioning to wait for the process to end, I have yet to see it return any codes other than “1”.

It seems that ExecCmd2() doesn’t play well with others (my executed batch file actually would hang, and other windows seemed to be sluggish as if ExecCmd2() was hogging resources), so I ruled that one out almost immediately.

ExecCmd3() seems to do everything I need it to do, and remarkably, the code to do it is much more compact than the other two! It waits for the process to end, it does seem to play well with other Windows applications, processes, etc, and it DOES capture the exit code of the process!

Another lesson learned in my investigations (and after my last post), I wondered if there was some disparity between the “ERROLEVEL” of a program termination (that can be used in batch files to determine success/failure), and the “Exit Code” of a process as returned by these mechanisms. I found a good website (http://www.robvanderwoude.com/index.html%5B/url%5D, click “Batch Files” at left, then scroll down to “Errorlevels”) that gave me my answer. The “Errorlevel” of a program termination does not at all relate to the exit code of a process. There are, however, several options (detailed in his webpage), depending on what OS you’re running. At the office we’re all using Win2k or later, so using the “Exit [exitCode]” command as the last command in my batch file did what I was looking for. It terminates the CMD.EXE process (running the batch command), and sets the ExitCode of the process to desired value.

Wonderful!

Now I have a basic user interface using Excel that allows a user to add a bunch of project files to a list, and by the click of a button run this batch file on them (processing the project files [not MS Project, BTW]), and uses the exit code of the process to determine (and show) whether or not each project’s processing job completed successfully or not.

Hopefully this can help others perform tasks with external processes more easily, also.
..dane

Reply | Quote

Edited to put before the link and after the link to make it active – see Help 19 – Mod
All,

I’ve finally gotten it resolved.. it took a little Google-ing, and several variations on the “wait for external process to terminate” mechanisms. I detail my attempts below for those interested (hopefully to also be more easily found when searching through the archives)

I originally wanted to wait for a process to complete, and found ExecCmd(). Next I knew that I needed to also grab the errorlevel (exit code) that the command returns when it terminates, and ExecCmd() didn’t seem to do the job. I looked around various forums, and found ExecCmd2() and ExecCmd3() (note I’ve changed their function names, I think they were all called “ExecCmd” when I found them). Also note that I changed them slightly from their original form to actually return the return value / exit code of the process.

I have attached all three in a zip file for anyone’s investigation/usage.

ExecCmd1() while correctly functioning to wait for the process to end, I have yet to see it return any codes other than “1”.

It seems that ExecCmd2() doesn’t play well with others (my executed batch file actually would hang, and other windows seemed to be sluggish as if ExecCmd2() was hogging resources), so I ruled that one out almost immediately.

ExecCmd3() seems to do everything I need it to do, and remarkably, the code to do it is much more compact than the other two! It waits for the process to end, it does seem to play well with other Windows applications, processes, etc, and it DOES capture the exit code of the process!

Another lesson learned in my investigations (and after my last post), I wondered if there was some disparity between the “ERROLEVEL” of a program termination (that can be used in batch files to determine success/failure), and the “Exit Code” of a process as returned by these mechanisms. I found a good website (http://www.robvanderwoude.com/index.html%5B/url%5D, click “Batch Files” at left, then scroll down to “Errorlevels”) that gave me my answer. The “Errorlevel” of a program termination does not at all relate to the exit code of a process. There are, however, several options (detailed in his webpage), depending on what OS you’re running. At the office we’re all using Win2k or later, so using the “Exit [exitCode]” command as the last command in my batch file did what I was looking for. It terminates the CMD.EXE process (running the batch command), and sets the ExitCode of the process to desired value.

Wonderful!

Now I have a basic user interface using Excel that allows a user to add a bunch of project files to a list, and by the click of a button run this batch file on them (processing the project files [not MS Project, BTW]), and uses the exit code of the process to determine (and show) whether or not each project’s processing job completed successfully or not.

Hopefully this can help others perform tasks with external processes more easily, also.
..dane

Reply | Quote

Here’s an alternate way to check to be sure a process is finished, and could provide a means to lock the log file you’re considering using. This is written about Perl, but the general strategy is certainly applicable to any other language.

http://www.tpj.com/documents/s=7178/sam0206j/%5B/url%5D

The code you have to read/write to the logfile could first check to be sure no other process is accessing it.

Reply | Quote

[indent]

---

I was going to boil it all down for you, but I’ve not the time now. But this article might be just what you need.
http://support.microsoft.com/default.aspx?…&NoWebContent=1

---

[/indent]

Kevin,

I dug up this reference recently… This code works GREAT to wait for an external application to complete its operation. The only thing left that I need is to somehow grab the errorlevel of a batch file that I am running.

I considered dumping the results to a log file and review the results; the problem is that there will potentially be multiple people running this tool simultaneously, so I am not guaranteed exclusive access to any given log file(s).

Do you or anyone else know of a way to not only wait for the completion of a DOS program (batch file, etc), but also grab its errorlevel upon completion?

thanks,
..dane

Reply | Quote

Diana

Check this code and see if it helps. Sorry I did not see the thread earlier. BTW this comes from Excel but I think its generic enough to work in Word.

‘/Force Excel/VBA to wait until the Shelled process finishes.
Do Until Len(Dir(“c:matlabexedataresult_refill_2.txt”)) > 0
Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 1)
Loop

Basically if the Dir finds the the file that is created, result_refill_2.txt in the folder specified, then it will get the name of it, and thus the Len will be > 0, if not it will return an empty string, Len = 0 and thus the loop will continue after waiting for a second.

Hope this helps.

Wassim

Reply | Quote

You can also just use Application.Wait which has a few arguments.

Deb

Reply | Quote

As discovered above, Application.Wait is not available in Word.

Andrew C

Reply | Quote

HI Pete
Intially I used this code.
The issue that arose was – I discovered if an old pc or a very large file to print
eg 70 pages
what occurs is at the line sleep would pause the print job
ie if spooled to pg 35 sleep would pause application & spooling & then resume.
it was a stop start motion & the task would take far too long to complete.

So I’m implementing alternative solutions.
Thanks anyway – Diana

Reply | Quote

Hi,

I’m not sure where I picked this gem up, may have been here. I know I didn’t write it.

Sub HaveABreak(iSeconds As Integer)
'
' Macro pauses running of procedure for given number of seconds
'
Dim lPauseTime As Long
Dim lStart As Long
  lPauseTime = iSeconds
  lStart = Timer 'get current time
  Do While Timer < lStart + lPauseTime
    DoEvents
  Loop
End Sub

It can be called using the syntax:
HaveABreak(x)
where x is an integer number of seconds you want a pause. If used in a global, I believe a reference needs to be made to be able to pass the parameter. Otherwise, a series of macros could be put in a non-referenced global (which also contains the HaveABReak procedure) for different amounts of time which could then call the HaveABreak macro. i.e.

Sub HaveABreak5()
    HaveABreak(5)
End Sub

Sub HaveABreak10()
    HaveABreak(10)
End Sub

Sub HaveABreak15()
    HaveABreak(15)
End Sub

That way the statement:
Application.MacroRun MacroName:=”HaveABreak5″
could be used instead of passing the parameter directly.

For a 5 second pause, HaveABreak5 could be called or you could hard code it as

sub Take5Macro
'
' Macro pauses running of procedure for given number of seconds
'
Dim lPauseTime As Long
Dim lStart As Long
  lPauseTime = 5
  lStart = Timer 'get current time
  Do While Timer < lStart + lPauseTime
    DoEvents
  Loop
End Sub

Hope this helps.

Reply | Quote