Results 1 to 23 of 23

Thread: Why Does API:Sleep Work in this Case??

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Why Does API:Sleep Work in this Case??

    I run Excel from my VB6 Application using "CreateObject"
    If I close Excel directly (Not from the VB6 App) and then
    try to Start Excel from my VB6 app I get "Application Defined Error"
    even though I execute oXL.Quit and set All object references "= Nothing"
    prior to restarting Excel.

    HOWEVER, if I allow the VB6 App to Sleep using API:Sleep AFTER
    oXL.Quit and setting All Object references "= Nothing"
    Excel again works as designed with NO errors.

  2. #2
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Why Does API:Sleep Work in this Case??

    My guess would be that while the app is sleeping it frees up resources basically pausing everything while it is sleeping. Whereas, if you quit Excel externally your program with still think it was using it but there would be nothing there to use.
    Last edited by Nightwalker83; Dec 21st, 2013 at 07:44 PM.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Re: Why Does API:Sleep Work in this Case??

    Thanks for input.
    When I quit Excel (externally -- not from VB) Excel is still running under Windows.
    which in and of itself seems odd, as closing Excel (X), you would think that it would
    close and only leave a hanging object variable in the VB program.

    Re: Sleep (my take)
    As I understand the documentation, Sleep "halts" the thread from which Sleep is executed.
    Since VB only has 1 thread, one would think the VB App would come to a halt.
    Since oXL.Quit was executed prior to Sleep being called, and since Excel in on another thread (???),
    the OS is allocating all resources to closing Excel instead of splitting those resources between
    the VB App and Excel. Why however, without Sleep an Automation Error occurs is still a ???
    since you would think the OS would allocate resources between the two Apps (VB and Excel)
    just like it does when you run two Apps separately. Maybe running from VB there is some
    behind the scene threading occurring??? -- or COM (which I'm not great at) -- is involved.

  4. #4
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Why Does API:Sleep Work in this Case??

    Quote Originally Posted by dw85745 View Post
    Maybe running from VB there is some
    behind the scene threading occurring??? -- or COM (which I'm not great at) -- is involved.
    Although, would it act the same if the project were compiled instead of run from the IDE?
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Why Does API:Sleep Work in this Case??

    Quote Originally Posted by dw85745 View Post
    Thanks for input.
    When I quit Excel (externally -- not from VB) Excel is still running under Windows.
    which in and of itself seems odd, as closing Excel (X), you would think that it would
    close and only leave a hanging object variable in the VB program.
    It isn't entirely clear what you mean... are you saying that when you close Excel manually, it is still listed in Task Manager (but only if your code ran)?

    If you only use Excel from your program, is it still listed in Task Manager after your program has finished?

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Re: Why Does API:Sleep Work in this Case??

    The following applies whether running from the IDE or as an EXE.

    are you saying that when you close Excel manually, it is still listed in Task Manager
    Yes; Without API:Sleep in the VB program; With API:Sleep >> No.

    If you only use Excel from your program, is it still listed in Task Manager after your program has finished?
    No

    This is the code I'm talking about:
    Code:
       'Allow User to Execute multiple times
       'w/o generating more workbooks 
       If gblnExcelActive Then
          Call MExcel.Excel_WrapUp(WRAPUP_NoSave, "")      
          Sleep 500       'Stops Automation Error - Why??
       End If
    Last edited by dw85745; Dec 22nd, 2013 at 06:43 PM.

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Why Does API:Sleep Work in this Case??

    Unfortunately I'm a bit lost, and can't think of anything that would cause that.

  8. #8
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Why Does API:Sleep Work in this Case??

    Maybe the sleep method is give whatever is being called time to rest between each call so that it doesn't become overloaded.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Re: Why Does API:Sleep Work in this Case??

    Thanks for taking a look see. Happy Holidays to All -- Hope I'm not late since you're both (Si_the_Geek and Nightwalker83) ahead of me.

    Only "logical ??" explanation I can think of is that Excel is still in the process of closing
    when VB hits API:Sleep and consequently Sleep causes the OS to give control back to Excel
    and this allows it to finish closing --- where without Sleep, control rests with VB and Excel is cut short and left hanging.

  10. #10
    Frenzied Member
    Join Date
    Dec 2008
    Location
    Melbourne Australia
    Posts
    1,487

    Re: Why Does API:Sleep Work in this Case??

    Use DoEvents

  11. #11
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Why Does API:Sleep Work in this Case??

    Quote Originally Posted by Bobbles View Post
    Use DoEvents
    Although, that would clog your program while it executes all the system commands before continuing with those of your program.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  12. #12
    Frenzied Member
    Join Date
    Dec 2008
    Location
    Melbourne Australia
    Posts
    1,487

    Re: Why Does API:Sleep Work in this Case??

    Quote Originally Posted by Nightwalker83 View Post
    Although, that would clog your program while it executes all the system commands before continuing with those of your program.
    One should not sprinkle them everywhere, and also be careful not to place them within a loop.

    I have slept with that dog on quite a few occasions.
    It has never bitten me.

  13. #13

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Re: Why Does API:Sleep Work in this Case??

    Bobbles:

    DoEvents is already being used.

    Code:
    Public Sub Excel_WrapUp(SaveOption As enmWrapUp, strPath As String)
    
       #If kDEBUGON Then
           Debug.Print "Begin Excel_WrapUp"
       #End If
    
       On Error GoTo Error_Excel_WrapUp
       
       '-----------------
       
       Dim iAnswer As Integer
       Dim sWBName As String
       Dim strPrompt As String
    
       '*******
       'STARTUP
       '*******
       
       '*****
       'MAIN
       '*****
       'Enum Workbook Collection this App
       'and Close ALL Active Workbooks
       'This is preferable to just closing the ActiveWorkbbook
       'in case the use created more than one Workbook under
       'an Application
       'For this App I've defaulted to closing with "NoSave"
       For Each oxlWB In oxlApp.Workbooks
    
          'Get Workbook Name
          sWBName = oxlWB.Name
    
          'Check if User Closed Excel other than from this App
          'If so, object reference to ActiveWork is lost
          If Not oxlWB Is Nothing Then
          
             strPrompt = "Closing Without Saving"
             iAnswer = MsgBox(strPrompt, vbInformation, "Workbook: " & sWBName)
             If iAnswer = vbOK Then
                
                'ActiveWorkbook object reference valid, so close it
                Select Case SaveOption
                
                   Case WRAPUP_NoSave
                      'close active workbook without saving any changes
                      oxlWB.Close False
                
                   Case WRAPUP_Save
                      'close active workbook and save any changes
                      oxlWB.Close True
             
                   Case WRAPUP_UserSave
                      ' close active workbook and lets user decide if
                      ' changes are to be saved or not
                      oxlWB.Close
                   
                   Case WRAPUP_SavePath
                      oxlWB.Close False, strPath      '  e.g.  "c:\book1.xls"
                   
                End Select
                
             End If
          End If
       
       Next oxlWB
       
    
       
       'If User Closes Excel other than from this App
       'Excel is still running in the background, hence
       'oxlApp object reference is still valid
       'Note:  Don't forget to do this or you'll not be able to open
       'book1.xls again, untill you restart you pc.
       oxlApp.Quit
    
       '*******
       'WRAPUP
       '*******
       Set oxlWS = Nothing
       Set oxlWB = Nothing
       Set oxlApp = Nothing
       gblnExcelActive = False
       
       DoEvents
      
       #If kDEBUGON Then
           Debug.Print "End Excel_WrapUp"
       #End If
    
       Exit Sub
    
    Error_Excel_WrapUp:
    
       With TError
          .Type = ERR_CRITICAL
          .Src = mstrModule & "Excel_WrapUp"
          .Action = MsgAndLog
       End With
       
       Call DoError
       
       oxlApp.Quit
       Set oxlWS = Nothing
       Set oxlWB = Nothing
       Set oxlApp = Nothing
    
    End Sub
    Last edited by dw85745; Dec 25th, 2013 at 05:32 PM.

  14. #14
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Why Does API:Sleep Work in this Case??

    @dw85745,

    Where does the code from post #6 fit into that code (post #13)?
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Why Does API:Sleep Work in this Case??

    Well I see some things in Excel_WrapUp that are likely to cause issues, but I'm not sure if they would cause the current issue or not (because it has been many years since I made those mistakes).

    The order of closing things matters, and you are currently partly closing things in the right order, but are leaving the setting of items to Nothing until after everything else... you should be doing it as each item is closed, and before the parent item is closed (for example: set the worksheet to nothing before starting to close the workbook, and set the workbook to nothing before starting to close the application).

  16. #16

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Re: Why Does API:Sleep Work in this Case??

    Nightwalker83
    Where does the code from post #6 fit into that code (post #13)?
    Post #6 code calls Post #13 code. Post#6 code resides in a commandbutton so that Excel can be called multiple times.

    si_the_geek

    you should be doing it as each item is closed
    Always set my objvariables to Nothing during the final wrapup unless constraints dictate otherwise.
    Will give it a go in this instance -- BUT can't see where it will make a difference..
    You have a point where multiple worksheets in workbook (in this case ONLY one worksheet in workbook).
    Last edited by dw85745; Dec 26th, 2013 at 03:07 PM.

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Why Does API:Sleep Work in this Case??

    I meant within the wrapup code, because I have no idea what else is happening within your program.

    It does not matter how many sheets or books are involved, it is about letting Excel know (by decrementing hidden counters) that it is safe to close things - and as things stand, you are trying to close the workbook before telling Excel that it is safe to let the sheet be fully released, which could well be causing the problem (it might be forcing the workbook to stay open, because it is expecting you to re-use the sheet).

  18. #18
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Why Does API:Sleep Work in this Case??

    @ dw85745,

    Is it possible for us forum members to test the code as is (that is the code you have posted) and create a demo to see if you can come up with a solution or does that code require other bits of code to work?
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  19. #19

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Re: Why Does API:Sleep Work in this Case??

    si_the_geek Point Taken on closing Worksheet

    ======================
    Nightwalker83

    Here's the startup code I'm using.

    Code:
    Public Sub Excel_StartUp(blnLateBound As Boolean)
    
    'Start Excel and Give User Choice whether to Show It!
    'Processes Faster if NOT Visible
    ', blnVisible As Boolean
    
    '////////////////////////////////////////
    'The only difference between starting up Early Bound versus Late Bound
    'is how Excel is declared.
    
    'For Early Bound
    '1)    Set reference to 'Microsoft Excel 8.0 Object Library' in
    '        the Project|References dialog (or Tools|References for VB4 or VBA).
    '2)    To declare an object as early bound, declare it by its name
    '       (normally the scope of the declaration will be Public or Private)
    
    '          e.g.    Public oxlApp As Excel.Application
    
    'For Late Bound
    '1)   No reference is made in VB Project References
    '2)   The object variable is declared as Object
    '         e.g.               Public oxlApp As Object
    
    'The only reason the parameter blnLateBound is passed is to remind the coder
    'how Excel was declared  -  It serves no other purpose
    
    '//////////////////////////////
    
       #If kDEBUGON Then
           Debug.Print "Begin Excel_StartUp"
       #End If
    
       On Error GoTo Error_Excel_StartUp
       
       '-----------------
       
       '*******
       'STARTUP
       '*******
       gblnExcelActive = False
       
       '*****
       'MAIN
       '*****
       'Check if there is a currently running instance our our desired Office app
       'If unsuccessful,  execute error handler
       Set oxlApp = GetObject(, "Excel.Application")
      
       'We returned from the error handler,
       'so, create a new instance of the application object
       If TypeName(oxlApp) = "Nothing" Then
    
          Set oxlApp = CreateObject("Excel.Application")
          
       End If
    
       '*******
       'WRAPUP
       '*******
      gblnExcelActive = True
      
       #If kDEBUGON Then
           Debug.Print "End Excel_StartUp"
       #End If
    
       Exit Sub
    
    Error_Excel_StartUp:
    
       Select Case Err.Number
       
           Case 429        'Cant create object"
             'Return to the next line of code to create a new instance of the app object
             'Its possible that this could be executed twice if the CreateObject line fails
             'In which case that would mean the Office app is not installed.
             Resume Next
                
           Case Else
           
                With TError
                   .Type = ERR_CRITICAL
                   .Src = mstrModule & "Excel_StartUp"
                   .Action = MsgAndLog
                End With
                
                Call DoError
    
       End Select
    
    End Sub
    Putting a command button and calling the StartUp code ahead of the code in Post #6 should do it other than
    declaring the Excel object variables.

    Execute the Command Button once to bring up Excel, close Excel using the X, and then execute the Command Button again.
    Do this with and without Sleep and see what you get.

  20. #20
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Why Does API:Sleep Work in this Case??

    You forgot to post the type for enmWrapUp.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  21. #21

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Re: Why Does API:Sleep Work in this Case??

    Sorry about that. Here's all the declarations

    Code:
    '******
    'API
    '******
        'Terminate Excel
       Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    '    Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Int32, ByVal wMsg As Int32, ByVal wParam As Int32, ByVal lParam As Int32) As Int32  '<<Net
    
       Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    
    
    '*******
    'Objects -- Global
    '*******
       'Start Binding Early
       Public oxlApp As Excel.Application
       Public oxlWB As Excel.Workbook
       Public oxlWS As Excel.Worksheet
     
    '   Start Binding Late
    '   Public oxlApp As Object
    '   Public oxlWB As Object
    '   Public oxlWS As Object
     
    '************
    'Enumerations
    '************
       'WrapUp
       Public Enum enmWrapUp
          WRAPUP_NoSave = 0
          WRAPUP_Save = 1
          WRAPUP_UserSave = 2
          WRAPUP_SavePath = 3
       End Enum
       
    '************
    'Variables
    '************
        Public gblnExcelActive As Boolean
    
    
    '******
    'Errors
    '******
       Private Const mstrModule As String = "MExcel "

  22. #22
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Why Does API:Sleep Work in this Case??

    As soon as I comment out "Sleep 500" I receive "Variable not defined" TError when I run the project. If I leave "Sleep 500" in the code the project does not do anything.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  23. #23

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Re: Why Does API:Sleep Work in this Case??

    The TError is a Type I use and part of a General Error Routine.
    Anything related such as "With TError" until "End With" and also DoError can be Remmed.

    I'll make up a test project and post it. Probably the easiest.
    Last edited by dw85745; Dec 27th, 2013 at 12:24 PM.

Posting Permissions

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



Click Here to Expand Forum to Full Width