Sign in to follow this  
Followers 0
cag8f

_ExcelCloseBook problems

19 posts in this topic

This is a repost of a problem I recently had. I was using Application.Quit as a workaround, but that would not save the file. Now I actually need to modify and save my spreadsheet, so I'm revisiting this problem. Here it is:

_ExcelCloseBook() has been working just fine for me for a few weeks now; it would close the Excel file I had previously opened. But all of a sudden it stopped working as expected. Here is my test code (taken from the help file):

#include <Excel.au3>
$oExcel = _ExcelBookNew() ; Example 1 - Create a Microsoft Excel window

MsgBox ( 0, "", _ExcelBookClose($oExcel))

Exit

Excel is opened and a new file is created. However upon script completion Excel stays open. Furthermore I cannot click anywhere in the window, even to close it. I can't even move the window (I can resize it though). I can only close it by killing it in task manager. Also, the message box returns 0, indicating that the file close failed. No other instances of Excel are open. Probably something stupid, but any thoughts?

Share this post


Link to post
Share on other sites



Theoretically it should work.

What error are you getting?

#include <Excel.au3>
$oExcel = _ExcelBookNew() ; Example 1 - Create a Microsoft Excel window
_ExcelBookClose($oExcel)
MsgBox(0, "", @error)
Exit

SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

That script returns '1' The Excel window stays open, and I cannot click anywhere inside the window, nor can I move it (but I can resize it).

Edit: And the file is not opened 'read only.'

Edited by cag8f

Share this post


Link to post
Share on other sites

From description, @error = 1 means: "Specified object does not exist" - that is weird because the object exists obviously.

What is your Operating System? What Office version?

On my Win7 and Office 2007 the above script worked very well.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

I know, curious. I'm on Windows 7 64 bit Pro and Office 2007.

Share this post


Link to post
Share on other sites

Well, did you try restarting your computer?

Other that that I cannot think of a reason for this to fail.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

Yes I definitely have restarted many times. As I said, this was working fine for weeks, and all of a sudden started demonstrating this problem. The ONLY think I can think of, and I'm grasping here, is that this problem *may have* (can't remember exactly) coincided with some network issues I had the same day. I had to do a factory reset on my wireless router. But I really can't see how that would be related at all; I'm not accessing the file over the network.

Any other ideas, or has anyone ever seen this behavior?

Share this post


Link to post
Share on other sites

Any other ideas, or has anyone ever seen this behavior?

Do you get the same result if you open and close Excel without AutoIt?

Share this post


Link to post
Share on other sites

Not at all. I can open, edit, save, and close it manually without a problem.

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Maybe you got hit by this

When using a 32bit program (Office in this case) the AutoIt script has to be compiled as 32bit.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Aha, that worked. I took the simple test script and compiled it. The default setting was for 64 bit, so I changed it to x86. It created an exe file, which I ran, and it opened and closed Excel as expected. Thanks so much.

Now, as a noob, I am used to merely hitting F5, "go," to run my scripts. Does this mean I will have to "compile" (requiring another step to open the .exe) instead of "go" every script? Or can I somehow change my "go" settings to "go" in 32 bit mode? Or perhaps I could incorporate the .exe open into the script itself, i.e. a line to open said .exe...

Share this post


Link to post
Share on other sites

Put

#AutoIt3Wrapper_UseX64=n
at the top of your script and you're done.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Splenda, it worked. I don't know how I would have solved this without the forum. Thanks!

Share this post


Link to post
Share on other sites

Glad to be of service :x


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Heck, all the way I thought we were talking about a script, not an executable.

When running the script from SciTE it should work because chosing between 32 or 64 bits is only done previous to compilation. I never had such problems at any time but I guess my Win7 is not the 64 bit edition so I can't tell if/how this behaviour is changed.

Anyway, glad you got your problem answered - I was just about to give up because I ran out of solutions.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

I am/was talking about a script. The compile to executable only came into play as a solution. The final solution to make the script work was the line:

#AutoIt3Wrapper_UseX64=n

Thanks for all help.

Share this post


Link to post
Share on other sites

I didnt quite get your issue ?

Why not use .Save to save your changes and then .Quit to close ?

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

The point is that if you're running an 64-bit OS and use an 32-bit application (in this case Office) and run/compile a script with #AutoIt3Wrapper_UseX64=y then the line

Local $sObjName = ObjName($oExcel)
returns nothing and therefore the function doesn't work as expected (doesn't save your workbook and doesn't quit - you always get @error = 1).

Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)

    Local $sObjName = ObjName($oExcel)   ; <== Returns nothing

    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0

    ; Save the users specified settings
    Local $fDisplayAlerts = $oExcel.Application.DisplayAlerts
    Local $fScreenUpdating = $oExcel.Application.ScreenUpdating
    ; Make necessary changes
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts

    Switch $sObjName
        Case "_Workbook"
            If $fSave Then $oExcel.Save
            ; Check if multiple workbooks are open
            ; Do not close application if there are
            If $oExcel.Application.Workbooks.Count > 1 Then
                $oExcel.Close
                ; Restore the users specified settings
                $oExcel.Application.DisplayAlerts = $fDisplayAlerts
                $oExcel.Application.ScreenUpdating = $fScreenUpdating
            Else
                $oExcel.Application.Quit
            EndIf
        Case "_Application"
            If $fSave Then $oExcel.ActiveWorkBook.Save
            $oExcel.Quit
        Case Else
            Return SetError(1, 0, 0)
    EndSwitch

    Return 1
EndFunc   ;==>_ExcelBookClose
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Right. Juvigy, I did not try to save+quit because _ExcelBookClose should do that. I was trying to figure out why it was not doing it, and water explained why.

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
Sign in to follow this  
Followers 0