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

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

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-06-17 - Version 1.5.0.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
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-06-17 - Version 1.5.0.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By kingjacob90
      Hi
      So I am trying to click the green button, this button is not always in the same place. So fare I am trying to click it by finding the color but there is also something else with the same color on the screen (circled in yellow) that is causing issues. Is there a way to use the Title and Class of the window (can't be just the window as there are more than one with the same name).
      How does AutoIt Info get this information?

    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
    • By Zaoka
      Hi guys
      Need little help with filtering.
      I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA :
       
      #include <Excel.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb") $oWorkbook.PivotTables("PivotTable1").PivotFields( _ "[Report 2].[Week].[Week]").VisibleItemsList = Array( _ "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _ "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _ "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]") But get error
      error: Array(): undefined function. Not sure how to resolve this.
    • By Rajat231
      I am trying this code to create multiple workbooks eachone  shall be copy of one worksheet from a workbook having multiple sheets ( keeing the name same)
      SavingWorksheets.au3
×
×
  • Create New...