Jump to content

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


 Share

Recommended Posts

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

 

Link to comment
Share on other sites

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 by rdaneelol
Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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 by rdaneelol
Link to comment
Share on other sites

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)?

All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager
InputBoxes - Simple Input boxes for various variable types

Link to comment
Share on other sites

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 by rdaneelol
Link to comment
Share on other sites

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. ;)

All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager
InputBoxes - Simple Input boxes for various variable types

Link to comment
Share on other sites

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 by rdaneelol
Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...