_Excel_Close pends until program exit only in Win10, but works fine in Win7

[rdaneelol]

I have a strange behavior in an Autoit program.  

The program works perfectly in the original environment I created the program for - for Windows 7 and Office  2010.

My workplace is migrating to Windows 10 with Office 2016.  When I run this program in that new environment,  the code actually executes without any errors, however, the excel process which was opened to read a spreadsheet/workbook does not close while the program is running.  If you exit the program, the excel process ends at that point...

I could ignore this behavior - one little excel process hanging out there is not going to kill anything, however - it just bugs me !

While troubleshooting the issue, I placed a number of error checks after the excel close - and the close actually reports that it is successful. 

I tried a few varieties of closing the excel process, and none of the methods tried seemed to actually work as well at the one in my code - the only downside being that it has to wait until the program finishes to actually close.

Any ideas on why an excel close would hang until program exit ?

 

Local $oExcel = _Excel_Open(False, False, False, False, True)

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sUserRoleMenuXLS, False, true )

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening workbook '" & $sUserRoleMenuXLS & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel, False, False)
 Exit
EndIf

;  this section will find the user id in the first 3 columns of the user/menu spreadsheet, and if it finds it - returns the value stored in the cell 2 locations to the right...
With $oExcel.ActiveSheet.Range("A:C").Find (@UserName)
    $Match = .Find (@UserName)
    If (Not IsObj($Match)) or (stringlen($Match.Offset(0, 2).value) = 0)    Then
        MsgBox($MB_SYSTEMMODAL, 'UIPLauncher Error', 'No Menu assigned for user - Contact Support.') ; Display a warning if the script isn't compiled or the file doesn't exist.
        ;_Excel_BookClose($oWorkbook, False)
        _Excel_Close($oExcel, false, false)
        Exit
    Else
        Local  $cResult = $Match.Offset(0, 2).value
    EndIf
EndWith
$oWorkbook.Saved = True
_Excel_BookClose($oWorkbook, False)

_Excel_Close($oExcel, false, false) ;close does not seem to work on windows 10 and office 2016

 

[Danp2]

Not an Excel expert, but seems to me that your code as written won't run. Shouldn't this --

With $oExcel.ActiveSheet.Range("A:C").Find (@UserName)
    $Match = .Find (@UserName)

be this instead --

With $oExcel.ActiveSheet.Range("A:C")
    $Match = .Find (@UserName)

 

[rdaneelol]

My programming technique of "throwing it at the wall" and "see if it sticks" works much of the time - strangely enough the code does actually work...

Thanks for spotting that - I'll clean it up and see if it has any impact...

----

Update - the cleaned up code - removing the extra find works fine - but did not impact the other issue... 

Interesting though how forgiving autoit is....

Edited February 24, 2020 by rdaneelol

[Nine]

As a workaround you could WinClose excel instead of _Excel_Close until someone finds a solution.  I don't have Office 2016 so I cannot help you.  I tested my old excel version with win 10 and it is working fine...

[Danp2]

Excel 2016 closes correctly for me when I ran the code. You are calling _Excel_Close in three different sections of code. Suggest that you add some debugging code to determine which of these is not actually shutting down Excel.

[rdaneelol]

The excel spreadsheet is a read only reference used by the application to assign a user a specific behavior - so if the user is not found in the spreadsheet - the program exits with error messages to "contact your sysadmin"...

I did add debugging code - the weird thing is that there are no errors being generated - the final bookclose and close both execute with no error - it's just that the program somehow is itself holding the file open (but only in the new environment).  This section of code just sets up the program for its user - and it runs perfectly fine all the rest of the way through.

Another new variable in our environment is Avecto Defendpoint - however, due to testing issues - my program is whitelisted for that program, so in theory - it should have no effect.  I am running procmon and process explorer to try and get some hints.  It looks like the excel session is initiated by windows automation - and attached to that system process.  To make sure it was not avecto, I ran the program on a machine which was not supposed to have it-  and it failed as well (or behaved the same - where the excel did not end until the autoit program was exited/closed).

I'll give WinClose a try and see if that works any differently...

One other thought - the spreadsheet is on our network and not on the local pc.  I'll try an experiment to temporarily move the spreadsheet to the local machine and see if it acts differently. 

 

Edited February 24, 2020 by rdaneelol

[seadoggie01]

Windows 10 and Excel 2016 here... _Excel_Close works for me, but you aren't forcing it to close... Have you tried _Excel_Close($oExcel, False, True)?

[rdaneelol]

Since this is being used by a group of heavy excel users - If I run that type of close - it will shut down all excel sessions going on, as opposed to only the one that was created by the program.  I will have a bunch of angry accountants ...  I will try that though as an experiment - to see if that one pends as well...

 

Interesting - I tried the force close on both the avecto and non-avecto machine - and it acts the same as my original setup - the excel process does not close until the program exits.   There is some thread somewhere that is keeping the program locked open until the exit.  If this was java - I'd try a garbage collect to force a cleanup of memory... 

Edited February 24, 2020 by rdaneelol

[seadoggie01]

Nope, this just calls $oExcel.Quit (aka Application.Quit in VBA) on a single instance of an Application, and since you are creating a new instance of an application in _Excel_Open anyways, this isn't an issue at all.

[rdaneelol]

Thanks for the idea - though it did not change the behavior...  I'm getting my security personnel involved to see if they created some funky security in our new environment...

[Nine]

Try this instead of _Excel_Close :

WinClose (HWnd($oExcel.hWnd))

 

[rdaneelol]

I found one thing that worked - I don't know what is so different about it... (except that it is a global kill) 

Instead of _excel_close, I used the lines below, and it worked.

If ProcessExists("Excel.exe") Then
    ProcessClose("Excel.exe")
EndIf

I put a blanket kill on excel - and then it works.  I tried setting the object to zero - to kill the process - that worked the same as the close...

Now I will just need to identify the specific process I started (hopefully I can do this), so I can kill it with this.

 

Any ideas on why this one would work and the other would not ? 

 

 

I tried the code below afterwards just to see if there was a difference:

 WinClose (HWnd($oExcel.hWnd))

and it behaved the same as the excel_close. It pended until the program exited.

 

Edited February 24, 2020 by rdaneelol

[water]

Try

_Excel_Open(True, True, True, True, True)

to check if you get any errors from your Excel workbook.
Add a debugging line after _Excel_BookClose checking @error and @extended to make sure the workbook was properly closed.

[water]

Back in 2015 another user had a similar problem - it was caused by misbehaving Excel addon.
Details can be found here:

Do you run any addons? If yes, could you please disable them one by one and see what happens?

[rdaneelol]

That was interesting - The spreadsheet opened in the foreground as expected - I reviewed the information on it - checking for any compatibility or other issues - nothing obvious stood out.  I checked for errors on each step - and no errors were generated.   And same as before - the excel process kept running - that is until the main program was exited...

The visibility let me confirm a couple of things

the _excel_book_close closed just the workbook

the _excel_close closed the visible excel window - acting like it was not there anymore 

however the excel process was still running in the background - and only stopped when the program exited...

[rdaneelol]

I'll check the add-ins.   That would not surprise me...

I was able to disable the add-ins.  I was not able to remove some COM add-ins -  I got a message while trying to remove them.  "The add-in is installed for all users on this computer and can only be connected or disconnected by an administrator"

With the add-ins disabled or removed, the excel close still is hanging...

I read through the prior issue - there is one thing similar - I am running 32 bit Office on a 64-bit Windows 10 OS...

I tried the code snippet below instead of close - but the process behaved the same.   

 $oExcel.Quit()
 $oExcel = 0
 $oWorkbook = 0

I'm reading the full prior message - some of the behaviors are exact...

[Nine]

Do you have a corporate image install instead of individual install.  It can make sense when you are working in corporation with a limited IT staff availability.  If so, you are lacking some essential elements and I have seen that problem so many times.

[rdaneelol]

Yes to corporate image...  Our company is getting a primary version of win 10 ready for rollout.   I was just validating my AutoIt program when I ran into the issue.

I used a solution in the earlier post - which picks up the last Excel session started and kills it.  It's not perfect, but it seems to work fine.  

What do you mean by lacking essential elements ? I know it is stripped down a little, and global policy is tightly controlled...