Jump to content

_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

 

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
Link to post
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 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
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
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

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
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
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

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
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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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

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

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.

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

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • By SkysLastChance
      I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. 
      My goal is to auto fill some formulas that are next to a pivot table in columns A-C. 
      _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. 
      Hoping someone can point me in the right direction. 
    • By WilliamasKumeliukas
      Hello everyone,
      I started this project alone in May 2020 as project in my spare time at work, I'm working for a IT company that started opening their services to residential customers few months ago and now my position in the company kind of drifted in the doom and gloom world of repetitive tasks like: Reinstallation + Configuration of Windows 10.
      The procedure is very repetitive and I started feeling like being a robot which is the main reason I started this project.
       
       
      ==============================FAQ==================================
      1. Q: Do you want this project to be accomplished with the usage of AutoIt ONLY or 3rd party tools / Scripts (BATCH / POWERSHELL / VB) ? A: No, if I cannot find a way using AutoIt to accomplish a task I will move to my Plan B which consist of automating an 3rd party tool to accomplish the affected task until a solution is found. 2. Q: What do I get from helping/collaborating in this project? A: I will personally take the responsibility to mention you in the credits of this project. 3. Q: If I have more questions, can I ask? A: Certainly! feel free to ask any questions related to this project! 4. Q: What is the main goal of this project? A: Automating Windows 10 configuration without user interaction needed (as much as possible) ______________________________________________________________________________________________________________________________
      Current progression of the project (more will be added in future)
      « Blue = Info || Yellow = Unfinished/Untested || Purple = Could be better || Green = Done ||Red = Not Yet Started »
      ***Very early Stage ***
      Connect Network Attached Storage(NAS) (Work but missing configuration in GUI - AutoIt only)
      Download & Install up to 600+ softwares (Tested & Working - using 3rd party tool + 50/50 Powershell/AutoIt)
       Auto prediction of Apps name of text typed inside input (Tested & Working - AutoIt Only)
      Change OEM Informations (Tested & Working -  AutoIt)
      Disable hibernation (Tested & Working - AutoIt only)
      Change Computer Name (Work but require testing - AutoIt only) 
      Show Computer Information and Smart status on GUI (Tested & Working - AutoIt Only)
      Change .pdf / .pdfxml from Edge to Adobe Reader DC (Tested & Working - using 3rd party tool)
      Change Edge to Google Chrome as Default Browser (Tested & Working - using 3rd party tool)
      Windows Updater (Seems to work but require further testing - AutoIt only)
      Install Office 365 / 2013 + Activation (To Do)
      Add L2TP VPN Configuration for Windows Built-in VPN (To Do)
      Save / Load tasks configuration profile in (.ini file) to avoid repeating same configuration twice (In progress - AutoIt Only)
      (EXPERIMENTAL) Install Apps from Microsoft Store with UIAutomation UDF made by @junkew(Work if you know what your doing)
         P.S: Installing Apps from Microsoft Store will require usage of  UIA spy tool made by @LarsJ which you can download & learn how to use it on UIA Spy Tool thread.
      ***  If this project interest you, Reply here This will greatly help me to see if you'd like this project to become real  ***
      ______________________________________________________________________________________________________________________________
      Best Regards,
      ~WilliamasKumeliukas
    • By Rskm
      Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel.  Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on.  If i read the notepad and paste it line by line, it is taking lot of time.  Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel.  The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time.  The expected excel format is attached here.  any help is appreciated.  thanks
      Tmp.xls
×
×
  • Create New...