How do you say to excel WAIT !!!! in VBA

grovithis

New Member
Joined
Apr 28, 2002
Messages
15
I have a sub call run_bat wich works great
unfortunatly the next sub routine nust wait until the bat file is run and then only start HOW!!!!!
NO idea even what the command should be
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
grovithis

Try: DoEvents

It hands over to the operating system. Since I do not know your code, just look it up in Help for the best way to use it.

Any help?

Regards

Robb__
 
Upvote 0
looking it up but here is what it looks like

Sub Automatic()
Call Copydata
Call Run_bat
Call Copydata2
Call Clearsheets
Call makeheadings
Call MakeSales
Call Hidecol

End Sub
'********************************************

Sub Copydata()

Windows("Öïñôþóåéò áðü ÁðïèÞêåò 10çìÝñïõ.xls").Activate
Sheets("data").Select
Range("A1:BW500").Select
Selection.ClearContents
Sheets("pgm").Select
Range("A1").Select

a whole bunch of stuff
Windows("Öïñôþóåéò áðü ÁðïèÞêåò 10çìÝñïõ.xls").Activate
Sheets("pgm").Activate
Range("A1").Select

End Sub

Sub Run_bat()

Shell "o:as400test.bat"

End Sub


Sub Copydata2()

Windows("Öïñôþóåéò áðü ÁðïèÞêåò 10çìÝñïõ.xls").Activate
Sheets("data2").Select
Range("A1:AZ1000").Select
a whole bunch of stuff
 
Upvote 0
Do you want the bat file routine to finish before the next routine starts, or are you wanting the routines to run together?

Regards

Robb__
 
Upvote 0
I see - tricky because Shell function has a habit of doing that.

Try inserting DoEvents in the calling routine:

Sub Automatic()
Call Copydata
Call Run_bat
DoEvents
Call Copydata2
Call Clearsheets
Call makeheadings
Call MakeSales
Call Hidecol

End Sub

Any help?

Regards

Robb__
 
Upvote 0
unfortunatly not:(
but hey i still have some time before i have to show them to the boss so i carry on looking
shouldnt i add an end eventline somwhere ?????

Somwhere out there as the song goes
"mmm this and closing of the month seems to be frying the brain"
 
Upvote 0
Hey guys, I'm new here, so my solutions might be rather mundane and not very correct according to the way things are supposed to be programmed.

That in mind, what about a kick-out to the user to tell Excel *when* the bat file is complete.

The bat files I call on a regular basis still pop up windows that they execute in that the user can view, or at least see running or finished in the window name on the start-bar (win9x,winNT,etc).

Why don't you insert a MsgBox between your call to the bat and the next instruction:

'call your batch file
MsgBox("Click OK when the .bat file is complete.")
'call your next routine/whatever...

easy enough, so long as you don't mind the user being able to screw it all up for you :)
 
Upvote 0
Thanks for the help rob

We are unfortunatly talking about a user who is going to retire in about 2 years!!!!
i just changed her green moniter ( as400 )to a pc she cant use a mouse and ..........
You know the rest so have to automate everything or do it myself !!!! I love network upgrades

PS AS400 Really kicks as as a system but it does not handle graphics at all
 
Upvote 0
Hi grovithis, I'm AS400 user too. Please try this.
<pre>
'--Please copy this into a standard module -----------------------------------
Public Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Public Declare Function WaitForSingleObject Lib "kernel32" _
(ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Public Const SYNCHRONIZE = &H100000
Public Const INFINITE = &HFFFF

Sub Automatic()
Call Copydata
Call Run_bat
Call Copydata2
Call Clearsheets
Call makeheadings
Call MakeSales
Call Hidecol
End Sub

Sub Run_bat()
Dim lngRtn As Long
Dim lngProID As Long
Dim lngProHn As Long
'Please modify the path (I can not input here "/")
lngProID = Shell("o:as400test.bat")
lngProHn = OpenProcess(SYNCHRONIZE, True, lngProID)
lngRtn = WaitForSingleObject(lngProHn, INFINITE)
lngRtn = CloseHandle(lngProHn)
End Sub
'-------------------------------------------------------------------------------
</pre>
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top