Excel helper wrapper

[BasicScience]

Even this short little piece of code leaves excel.exe running

Code: Select all

#include once "xlhelper.bas"

xlstart (1,FALSE)                   'initialize Excel, 1 sheet, not visible
xlopen(curdir & "\" & "My_Excel", FALSE)    'Open file, not visible
Print Val(xlgetvalue(1,2))


xlquit 
xlrelease

sleep

[phishguy]

Not at a computer with Excel right now. Try putting an xlsaved before the xlquit. If the version of excel or the default file format is different for the source file, it may be prompting to save the file. This of course you can't see because you are opening it invisible. Let me know if this fixes the problem. If not, I will test and get back to you when I have access to a PC with Excel.

[BasicScience]

Your suggestion of having Excel Visible helped reveal to problem. I'm trying to read an existing Excel file, not create a new one. I mistakenly thought xlstart() was required to launch the application. xlopen() had a create object call as well

Code: Select all

xlco("Excel.Application", NULL, @xlApp)

and so the xlstart() is not needed. xlstart was creating a new blank file, which remained as an active process when the FB.EXE terminated. The code below does what I need.

Code: Select all

#include once "xlhelper.bas"

xlopen(curdir & "\" & "My_Excel", FALSE)    'Open file, not visible
Print Val(xlgetvalue(1,2))

xlquit 
xlrelease

sleep

[EMRZ]

Thanks for this !

[BasicScience]

@Phishguy,

After installing MS Office 2016, excel remains an active process (shown in Task Manager) even after performing xlquit. The open workbook closes and the application window closes, but the application is active. With multiple xlOpen commands executed, this leaves a huge list of excel.exe active processes.

I found a brute force method based on the command line instruction: "taskkill /F /IM excel.exe". This works when issued from a shell command in FB, but it would cause a loss of any content from open excel file that have not been saved.

Perhaps you have find a modification to xlquit.

Code: Select all

#include once "FB_Lib\xlhelper.bas"
xlopen  (curdir & "\Test_Excel.xlsx", TRUE)   'Open file, not visible
print "completed xlopen"
print xlgetvalue(1,1)
sleep
xlquit
print "completed xlquit"
sleep
shell "taskkill /F /IM EXCEL.EXE"
print "complete kill"
sleep

[phishguy]

I don't have Excel 2016. However, you need to have an xlrelease statement. I didn't see that in your example.

[BasicScience]

I added xlrelease (just after xlquit) -> the excel.exe image is still active in tasklist.

Curiously, xlrelease before xlquit generates an error message:
application.quit
CallMethod
InvokeV
The parameter is incorrect
code: 80070057

Is this expected if xlrelease is attempted before xlquit?

[BasicScience]

If you know of a way to get the process ID for the instance of excel that was launched by xlopen(), then I could selectively use taskkill to wipe out just that image of excel.

[phishguy]

Well that bites. Unfortunately, like I said, I don't have Excel 2016. Anyways, the xlrelease releases the objects and therefore xlquit doesn't have an object to reference to. So, yes that was expected behavior.

[phishguy]

Excel 2016 may be a little buggy?
http://answers.microsoft.com/en-us/offi ... d2407d3a8e

[BasicScience]

Thanks for the reference. I agree, looks like Excel 2016 is buggy

[BasicScience]

Update. There are several references to excel remaining active in the tasklist even after executing an .application.quit command in VBA. This behavior was noted back in MS Office 2013.

The cause was identified as:
"The most common cause of the problem is a 'global' reference to the automated application. Unfortunately, under some circumstances it is possible to directly refer to an entity (property/method/object) of the automated object. This reference effectively is global to the calling application. Hence, the reference remains in place as long as the calling program is active. Consequently, the operating system will not end the automated application while the caller is active. "

See
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/


I'm not very well versed in dishelper to know which COM instructions in your code might be global. After playing around with the simple test program pasted above, however, I noticed that if VISIBLE = FALSE on the initial call to xlopen() then the problem does not occur! Curiously, if xlopen() is called with VISIBLE = TRUE and then the running application is hidden with:

Code: Select all

xlpv(xlApp, ".Visible = %b", FALSE)

then the process still remains active in the tasklist after the FB code is done.

[Zippy]

RE: lingering excel.exe processes.

I've used this code for testing:

Code: Select all

'xlhelper.bas test, multiple instances
'' Does excel.exe remain after xlquit() & xlrelease()
''  Compile as console prog.
''  Tested Win10 64bit, Excel 2013/16, FB 1.05.0 (01-31-2016), built for win32 (32bit) 
'
#include "xlhelper.bas"
'
'create 2 placeholder structures, 1 for each
'' book we are opening or creating
dim as IDispatch ptr book1,book2
'
'create 1st book
xlstart()
print "start book1 xlapp = ";xlapp
book1=xlapp 'store book1 pointer
'
sleep 2000
'
'create 2nd book
xlstart()
print "start book2 xlapp = ";xlapp
book2=xlapp 'store book2 pointer
'
sleep 2000
'
xlapp=book1 'set internal pointer to book1
xlquit()
xlrelease()
print "End book1 xlapp = ";xlapp
'
xlapp=book2 'set interal pointer to book2
xlquit()
xlrelease()
print "End book2 xlapp = ";xlapp
'
print
print "Exit in 5 seconds....."
sleep 5000

which I think was posted in this thread previously, for testing against fb 1.04.0. I was unable to confirm the problem with Excel 2013 when the issue was first broached here (and still can't replicate it with Excel 2013).

2 days ago I recompiled this code using 1.05.0, copied the executable to a Win10 system with Excel 2016 (Office 365), ran it, was left with 2 excel.exe processes after termination. I performed this test 3 times, with identical results. Zombies, there were.

TODAY I tested the same exe (compiled on separate system 2 days ago) on the same Win10 Excel 2016 system, and NO excel processes remained after termination. I recompiled this test code on the Excel 2016 system, still no zombies after program termination.

I posit that the Tuesday 7/12 M$ update (included an Office update), which installed between my testing times, may have corrected the problem. I cannot otherwise explain my experiences.

-----

RE: The link (http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/) BasicScience posted.

One of the solutions offered (#2) is to use late binding. AFAIK disphelper uses late binding.
.

[BasicScience]

@Zippy

Zombies, indeed. When I run your code with FB 1.03.0 32-bit in Win 7 Enterprise SP1 64-bit with MS Office 2016, I get two lingering excel.exe processes If, however, I force the spreadsheet to be invisible with:

xlstart(,FALSE)

then no lingering process. The effect is very buggy. Even after xlstart(,FALSE), if I make two calls to xlselect() then the process lingers... but not if I made only 1 call. Geesch.

My approach would be to somehow get the PID for the excel process, then kill it with a shell command to takskill for that specific PID

[Zippy]

@BasicScience

I just tried this again. And again have no zombies.

Making assumptions (mine, of course):
1. Your OS version probably isn't material.
2. Using fb 1.03.0 probably isn't material.

I'm still thinking (!) that this is a versioning problem, that from my perspective the problem was here one day then not the next (2nd day) after an OS+Office update. But... There appears to be only one Excel update in the 7/12 update:
https://support.microsoft.com/en-us/kb/3173835
which references:
https://support.microsoft.com/en-us/kb/3115272

The update version of Office I have is:
16.0.6366.2036

If we have the same version then - I'm lost. If the versions are the same then I can create a very short disphelper test that excludes anything that could be construed as "global" (not that I buy this global premise). Let me know.

Note that I'm ignoring the "VISIBLE = FALSE" sometimes-solution, at least until I see what version of Office you have. Too strange.
.