-
Dec 20th, 2013, 11:00 AM
#1
Thread Starter
PowerPoster
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.
-
Dec 21st, 2013, 07:39 PM
#2
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
-
Dec 21st, 2013, 08:33 PM
#3
Thread Starter
PowerPoster
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.
-
Dec 21st, 2013, 08:53 PM
#4
Re: Why Does API:Sleep Work in this Case??
Originally Posted by dw85745
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
-
Dec 22nd, 2013, 06:22 AM
#5
Re: Why Does API:Sleep Work in this Case??
Originally Posted by dw85745
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?
-
Dec 22nd, 2013, 06:39 PM
#6
Thread Starter
PowerPoster
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.
-
Dec 24th, 2013, 07:56 AM
#7
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.
-
Dec 24th, 2013, 08:25 AM
#8
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
-
Dec 24th, 2013, 01:18 PM
#9
Thread Starter
PowerPoster
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.
-
Dec 25th, 2013, 02:33 AM
#10
Re: Why Does API:Sleep Work in this Case??
-
Dec 25th, 2013, 02:48 AM
#11
Re: Why Does API:Sleep Work in this Case??
Originally Posted by Bobbles
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
-
Dec 25th, 2013, 02:54 AM
#12
Re: Why Does API:Sleep Work in this Case??
Originally Posted by Nightwalker83
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.
-
Dec 25th, 2013, 05:28 PM
#13
Thread Starter
PowerPoster
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.
-
Dec 25th, 2013, 07:34 PM
#14
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
-
Dec 26th, 2013, 04:59 AM
#15
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).
-
Dec 26th, 2013, 03:02 PM
#16
Thread Starter
PowerPoster
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.
-
Dec 26th, 2013, 03:38 PM
#17
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).
-
Dec 26th, 2013, 05:43 PM
#18
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
-
Dec 26th, 2013, 09:31 PM
#19
Thread Starter
PowerPoster
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.
-
Dec 27th, 2013, 01:07 AM
#20
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
-
Dec 27th, 2013, 07:54 AM
#21
Thread Starter
PowerPoster
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 "
-
Dec 27th, 2013, 08:29 AM
#22
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
-
Dec 27th, 2013, 12:13 PM
#23
Thread Starter
PowerPoster
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|