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

[grovithis]

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

[Robb__]

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__

[grovithis]

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

[Robb__]

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__

[grovithis]

I want the bat routine to finish and then only must the next routine start

[Robb__]

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__

[grovithis]

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"

[robfritts]

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

[grovithis]

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

[Colo]

Hi grovithis, I'm AS400 user too. Please try this.

'--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

'-------------------------------------------------------------------------------