Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    athens
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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__

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    athens
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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__

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    athens
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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__

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    athens
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    KC, MO
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  9. #9
    New Member
    Join Date
    Apr 2002
    Location
    athens
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

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

    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •