Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pausing VBA Code (2003)

    In the following sub <pre>Sub Ricarica()

    Dim fso, file1, Scelta
    Dim Start As Double
    Dim FileAgg As String
    Dim fp As String
    Dim FD As FileDialog
    Dim rep1 As JRO.Replica
    Dim cnn1 As New ADODB.Connection
    Dim rst1 As ADODB.Recordset

    Set FD = Application.FileDialog(msoFileDialogFolderPicker)
    If FD.Show = False Then
    GoTo Exit_SalvaMdb
    End If
    fp = FD.SelectedItems.Item(1)
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Shell fp & "padana_be.exe", 0 </span hi>
    cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=cadana" & _
    "padana_b.mdb"
    Set rep1 = New JRO.Replica
    rep1.ActiveConnection = cnn1
    rep1.Synchronize CurrentPath & "padana_be.mdb", _
    jrSyncTypeImpExp, jrSyncModeDirect
    FileAgg = CurrentPath & "padana_be.mdb"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set file1 = fso.GetFile(FileAgg)

    If fso.FileExists(FileAgg) Then
    fso.deletefile (FileAgg)
    End If

    Set file1 = Nothing
    Set fso = Nothing
    MsgBox "Finished"
    Exit_SalvaMdb:
    End Sub</pre>



    the <span style="background-color: #FFFF00; color: #000000; font-weight: bold">highlighted</span hi> line launches a WinRar exe file which requires the user to select where to put the exploded mdb file.
    How can I pause the code right after the highlighted line so that the following lines are not executed until the WinRar window closes?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pausing VBA Code (2003)

    You can use the Windows API function WaitForSingleObject to see if a process has terminated. Take a look at the SuperShell example for WaitForSingleObject

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing VBA Code (2003)

    Your usual amazing greatness <img src=/S/king.gif border=0 alt=king width=21 height=22>
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing VBA Code (2003)

    Hans,
    I'm now trying to use the SuperShell function in the Ricarica subroutine but when I run it, the line
    <font color=red> SuperShell "padana_be.exe", fp & "", 0, SW_NORMAL, HIGH_PRIORITY_CLASS </font color=red>
    doesn't seem to be launching WinRar(no error messages pop up) while it does if I retain the original line
    <font color=448800> Shell fp & "padana_be.exe", 0 </font color=448800>
    could there be something wrong with the values assigned to the function arguments?
    If you need to use WinRar and padana_be.exe, they're in the Default.zip file here

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pausing VBA Code (2003)

    I'm sorry, I can't help you with this. The CreateProcess function doesn't seem to do anything without indicating an error, but the technical details of these API functions are beyond me.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing VBA Code (2003)

    It's ok Hans, is there any other way to pause the code right after the highlighted line so that the following lines are not executed until the WinRar window closes?

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pausing VBA Code (2003)

    Does it help if you change the SuperShell call to use the full path? -
    <code>SuperShell fp & "padana_be.exe", fp & "", 0, SW_NORMAL, HIGH_PRIORITY_CLASS</code>?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing VBA Code (2003)

    Hi Rory!
    No, the code still runs without any breaks or messages but the WinRar window doesn't pop up.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pausing VBA Code (2003)

    You could use the WaitForFile function from <post:=427,398>post 427,398</post:> to wait until Padana_Be.mdb has been extracted.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing VBA Code (2003)

    I've solved it by going back to using the normal Shell function and opening a form between the Shell line and the following lines.

    Shell fp & "padana_be.exe", 0
    DoCmd.OpenForm "MAttesa"
    DoCmd.RepaintObject
    MsgBox UCase("Finito")
    DoCmd.Close
    cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=cadana" & _
    "padana_b.mdb"

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pausing VBA Code (2003)

    Just curious: why <code>MsgBox UCase("Finito")</code> and not just <code>MsgBox "FINITO"</code>?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pausing VBA Code (2003)

    Giorgio gets paid by the number of characters in his source code! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pausing VBA Code (2003)

    Hmm, then I like this version ( <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ):
    <pre> Dim alngChars, lngIndex As Long, strMsg As String
    alngChars = Array(102, 105, 110, 105, 116, 111)
    For lngIndex = LBound(alngChars) To UBound(alngChars)
    strMsg = strMsg & Chr(alngChars(lngIndex))
    Next lngIndex
    strMsg = StrConv(strMsg, vbUpperCase)
    MsgBox strMsg
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing VBA Code (2003)

    You're very observant Rory. I guess it was too much hassle to retype it capitalised. <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>
    Kidding, although there's a pinch of truth there. The sentence was longer and in lower case.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing VBA Code (2003)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>Watch out for the low bridge <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26> coming your way <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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