Jump to content
rdaneelol

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

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

 

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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

Share this post


Link to post
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 2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-03-21 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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 2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-03-21 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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...

Share this post


Link to post
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...

Share this post


Link to post
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.

Share this post


Link to post
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...

Share this post


Link to post
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

  • Similar Content

    • By Skysnake
      Hi guys
      AutoIt content - Microsoft thinks any AutoIt content is unwanted.
      What it does is simply delete any EXEs. No option of do you want to continue...
      The March 2020 updated has substantially changed the Defender Virus Protection logic and layout.
      I found three pages relevant to the help, and lots of outdated content. Is there a quick and easy way to set folder exclusions? Any help is appreciated and simply using google is not good enough, as this is a new problem, caused by the update.
      Any ideas please?
      ____________________________
      ANSWER
      Found it.
      Accessing the menu through the Control Panel does not work
      Click the Defender shield in the icon tray.
      Scroll down to Virus & threat protection settings click Manage Settings Scroll down to Exclusions click Add or remove exclusions then click the + for Add an exclusion, and select File / Folder / File type / Process 
      Select Folder and browse to select. 😥
       
      Skysnake
    • By Zaoka
      Hi guys,
      i have simple report in PowerPivot that shows Orders (Values) by Regions (Row) and Weeks (Columns). In Filter field is WeekDAYS (Monday,Tuesday,Wednesday,Thursday etc )
      how to filter WeekDAYS Filed on WEEKDAYYesterday with autoit ?
      my junky try
      #include <Date.au3> #include <Excel.au3> Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1) Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb") Sleep (5000) $oExcel.ActiveWorkbook.RefreshAll Sleep (5000) $oExcel.Application.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)  
      Error result
      $oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) $oExcel.Application^ ERROR  
    • By jantograaf
      Hi all,
      I'm trying to automate the install of a specific networking device. All goes well, I can install the driver on both Windows 7 & 10, rescan for hardware, set the IP-settings and all, but there's one issue. In Windows 10, I can disable all connection protocols using Powershell, but Windows 7 does not offer this Powershell-Cmdlet. So I programmed a way (two years ago) to let AutoIt open the network-adapter properties and then deselect all checkboxes except for the TCP/IP-V4. I recovered this snippet somewhere in my old files and tried to reintegrate this into my latest script, but it doesn't seem to work. On Windows 10 I get a blank screen for a few seconds and that's it, on Windows 7 it makes explorer crash. 
      I think it's quite safe to test this on your own system as long as you're not using IPv6 to connect to the internet and as long as you return to check all the boxes again (so you don't break your connectivity).
      If  anyone has any idea, I'd be glad to hear from you!
      Thanks in advance and stay safe in these Corona-ridden times 🙂
      Kind regards,
      Jan
       

      There are three functions involved:
      DisableAllProtocols($AdapterName)
      This one is the main function that 'should' uncheck all the checkboxes in the adapter's Properties-window.
      Func DisableAllProtocols($AdapterName) OpenNetConnToAdapter($AdapterName) ;Find the IDs of all relevant controls Local $Handle_Window_Properties = WinWaitActive($Adaptername & " Properties","") Local $Handle_Listview_Protocols = ControlGetHandle($Adaptername & " Properties","","[CLASSNN:SysListView321]") Local $Handle_OK_Button = ControlGetHandle($Adaptername & " Properties","","[CLASSNN:Button6]") Local $List_CMN_ID = ControlListView($Adaptername & " Properties","","[CLASSNN:SysListView321]","FindItem","Client for Microsoft Networks") Local $List_FPSMN_ID = ControlListView($Adaptername & " Properties","","[CLASSNN:SysListView321]","FindItem","File and Printer Sharing for Microsoft Networks") Local $List_QOS_ID = ControlListView($Adaptername & " Properties","","[CLASSNN:SysListView321]","FindItem","QoS Packet Scheduler") Local $List_MNAMP_ID = ControlListView($Adaptername & " Properties","","[CLASSNN:SysListView321]","FindItem","Microsoft Network Adapter Multiplexor Protocol") Local $List_MLLDPPD_ID = ControlListView($Adaptername & " Properties","","[CLASSNN:SysListView321]","FindItem","Microsoft LLDP Protocol Driver") Local $List_IPV6_ID = ControlListView($Adaptername & " Properties","","[CLASSNN:SysListView321]","FindItem","Internet Protocol Version 6 (TCP/IPv6)") Local $List_LLTDR_ID = ControlListView($Adaptername & " Properties","","[CLASSNN:SysListView321]","FindItem","Link-Layer Topology Discovery Responder") Local $List_LLTDMIOD_ID = ControlListView($Adaptername & " Properties","","[CLASSNN:SysListView321]","FindItem","Link-Layer Topology Discovery Mapper I/O Driver") ; Disable All Protocols Except IPV4 SetCheckboxState($Handle_Listview_Protocols,$List_CMN_ID,3) SetCheckboxState($Handle_Listview_Protocols,$List_FPSMN_ID,3) SetCheckboxState($Handle_Listview_Protocols,$List_QOS_ID,3) SetCheckboxState($Handle_Listview_Protocols,$List_MNAMP_ID,3) SetCheckBoxState($Handle_Listview_Protocols,$List_MLLDPPD_ID,3) SetCheckboxState($Handle_Listview_Protocols,$List_IPV6_ID,3) SetCheckboxState($Handle_Listview_Protocols,$List_LLTDR_ID,3) SetCheckboxState($Handle_Listview_Protocols,$List_LLTDMIOD_ID,3) ;Click OK ControlClick($Handle_Window_Properties,"","Button6") Sleep(50) WinClose("Network Connections") EndFunc OpenNetConnToAdapter($AdapterName)
      This function opens the network adapter's Properties-page
      Func OpenNetConnToAdapter($AdapterName) ShellExecute("control.exe","ncpa.cpl",@WindowsDir,"",@SW_SHOW) WinWait("Network Connections","") WinActivate("Network Connections","") Local $Handle_Window_NetworkConnections = WinWaitActive("Network Connections","") Send("{F5}") Sleep(250) Local $AdapterNameArray = StringSplit($AdapterName,"") For $i = 1 To $AdapterNameArray[0] Step 1 Send($AdapterNameArray[$i]) Sleep(10) Next Sleep(50) Send("{APPSKEY}") Sleep(50) Send("{R}") Sleep(500) Return EndFunc SetCheckboxState($Handle,$checkbox_id,$wantedstate)
      This function changes the state of a checkbox
      Func SetCheckboxState($handle,$checkbox_id,$wantedstate) ;$handle : SysListView32-handle ;$checkbox_id : The ID of the checkbox you want to control ;$wantedstate : The wanted status (2 for checked, 3 for unchecked) Local $currentstate = _GUICtrlListView_GetItemStateImage($handle,$checkbox_id) If $currentstate = $wantedstate Then Return Else _GUICtrlListView_SetItemSelected($handle,$checkbox_id,True,True) Send("{Space}") Sleep(50) Return EndIf EndFunc  
    • By VinMe
      $aTdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $LastRow) $aTDSLOC = _Excel_RangeRead($oWorkbook1, Default, "H1:H" & $LastRow) MsgBox(0, "TAKSY OUTPUT LAST ROW", $LastRow) _ArrayTrim($aTDSLOC, 1, 1) _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, $aTDSLOC, "H2") $oWorkbook1.Sheets("DS_List").Activate $mLastRow = $oWorkbook1.ActiveSheet.UsedRange.Rows.Count $aMdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $mLastRow) MsgBox(0, "MASTER OUTPUT LAST ROW", $mLastRow) _Excel_RangeReplace($oWorkbook1, Default, "F2:F" & $mLastRow, "N/A", "n/a") ;check the requiremetn $aMLOC = _Excel_RangeRead($oWorkbook1, Default, "F1:F" & $mLastRow) ;LOC NUMBER NEEDS TO BE REPLACED WITH.. _ArrayDisplay($aTDSLOC) _ArrayDisplay($aMLOC) _ArrayDisplay($aMdslist) ;DATA COMPARISION BETWEEN TAKSY LIST WITH LOC FROM SAP TO MASTER LIST WITH LOC. $oWorkbook1.Sheets("OUTPUT").Activate For $i = 1 To $LastRow - 1 For $j = 1 To $mLastRow - 1 If $aTdslist[$i] == $aMdslist[$j] And $aTDSLOC[$i] <> $aMLOC[$j] Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 3 EndIf If $aTdslist[$i] == $aMdslist[$j] And $aMLOC[$j] == "n/a" And _Excel_RangeRead($oWorkbook1, Default, "I" & ($i + 2)) == "NO KIT" Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 2 _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, "*", "H" & ($i + 2)) EndIf Next Next MsgBox($MB_SYSTEMMODAL, "Status", "Loc extraction, comparision and consolidation is Done", 2) DEAR TEAM, I am facing issue w.r.t subscript errors kindly help me in solving...
      456.xlsx
    • By VinMe
      $oWorkbook = _Excel_BookAttach($sWorkbook) $oWorkbook.sheets("Sheet1").activate Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "") _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, $sMSN) can anyone help me here, excel filer is not working with the above code.. attached excel
      input: 12
      Thank you in advance!!
      to check.xlsx
×
×
  • Create New...