_ExcelCloseBook problems

[cag8f]

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?

[enaiman]

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

[cag8f]

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 December 13, 2010 by cag8f

[enaiman]

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.

[cag8f]

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

[enaiman]

Well, did you try restarting your computer?

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

[cag8f]

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?

[JoHanatCent]

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

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

[cag8f]

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

[water]

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 December 13, 2010 by water

[cag8f]

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

[water]

Put

#AutoIt3Wrapper_UseX64=n

at the top of your script and you're done.

[cag8f]

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

[water]

Glad to be of service

[enaiman]

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.

[cag8f]

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.

[Juvigy]

I didnt quite get your issue ?

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

[water]

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

expandcollapse popup

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 December 14, 2010 by water

[cag8f]

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.