Macro is too fast!

[ErikZ]

I have my Macro call a program outside of access that parses the data file. It turns the data file into something Access can easily import. However, once that application ends and Access tries to import the files, I get a "File not found." error. I've determined that it's a timing issue; Access is trying to open the files before Windows realizes the file exists.

How can I get the Macro to pause for 5 seconds? Right now I have it open up a MsgBox but it's not a very good solution.

 

[ErikZ]

Well. Ok. If there's no way to do this in a macro, how about I convert all of the macro into code?

So how do I get a 5 second pause in VBA code?

 

[hsitz]

Here's a way to do it in straight VB. Doing it in Access VBA should be very similar, except that you would use your form's built in Timer Interval property and On Timer events, found at the bottom of the events property tab.

http://msdn.microsoft.com/vbasic/technical/99pasttips.asp#2-5-99

 

[hsitz]

Or, even better, here's what I found in the Access 2000 help for the timer function, a routine to wait five seconds! -- Herb

--------------------------------
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 &quot;Paused for &quot; & TotalTime & &quot; seconds&quot;
Else
End
End If

-------------------------------------

 

[ErikZ]

Thanks!

Hmm, I've used the code above as a guide to add a timer pause.

Right now my VBA opens a DOS window and runs a DOS batch file. But as soon as that window is open, the VBA code goes to the next step. Any idea on how to get it to wait for the DOS window to close like the Macro does?

 

[TudorSmith]

Also in code here's a wee function:

Function WaitFor(psngSeconds As Single)
Dim sngStart As Single
sngStart = Timer
Do While Timer - sngStart < psngSeconds
DoEvents
If Timer < sngStart Then Exit Do
Loop
End Function

You can then call this procedure anywhere in a module with WaitFor X where x= no. of seconds you want to wait! I stored the WaitFor routine in a basGlobals module.

I wonder...you may then be able to run the function from a macro by selecting RunCode and inserting waitfor(X) in the comments field....but I don't use macro's so that's just speculation. The Code works fine though

Birklea

 

[hsitz]

ErikZ -- I don't know for sure, but one option to get Access to wait for the DOS window might be to put the code shelling to DOS in an Access form of dialog type.

Access suspends execution in a module opening a dialog form until the dialog form is closed. So, if you made the DOS shell the only thing happending in the dialog form, and have a command button that says something like &quot;Continue&quot; on the dialog form (which would have the code to close the form and return to main form) you could get something like what you want. Not perfect, but I think it would work. -- Herb

 

[ErikZ]

Hmm. What you wrote sounds like it might work Herb. But since I had all the code for birklea's idea ready, I went with that.

It works now, and it goes like this.

Command button runs a macro, the macro has a bunch of basic commands in it, then it runs the DOS batch file. When that window closes it runs the code that forces it to wait for five seconds. More generic macro statements and a &quot;Task Complete!&quot; window pops up.

And here's the timing code, for those who are curious:

Function MacImport()
On Error GoTo MacImport_Err
Dim PauseTime, Start

' Macro is too fast and needs something to pause.

PauseTime = 15 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop

Yeah, it's basic. Just the way I like it.

 

[MichaelRed]

On the &quot;FAR Side&quot; of the process I find the aberant thought of just doing the import conversion directly in vb / vba/ access. It at least avoids the timing issue. May even allow you to actually understand some (more) of your program.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[jfischer]

Here's a link to show how to wait on a shelled process.

http://www.mvps.org/access/api/api0004.htm

 

[ErikZ]

Yeah, it would be more elegant if the conversion were in Access. However, I'm making 8$ an hour here, and they only let me work on this project 2 hours a day. After my regular 8 hour day, so much for a College education.

They don't have any programmers, and they have no interest in hiring me after my job is up. I'm trying to be professional in this by making a system that they have a chance of modifying to their own use.

Programming it in Access will not give them that option, and I don't have the time to do it.