Closing an existing hidden excel document

[sgebbie]

I've been messing with this trying different methods for a week or so now and I'm stumped with how to get this to work correctly. I've managed to get the same feature to work correctly with word, though the commands to do the same with Excel seem a bit different. Below is the code I'm working with for this. Basically the script will pass through the Word function correctly without any errors and will hang on the Excel function. I threw in a command prompt in the Excel function to be sure the script was at least getting that far. It seems it gets to that point and then hangs.

(The WinActivate("AutoIT v3")line I threw in the Excel function as well after reading another thread on a similar issue. Script still hangs with or without that added) http://www.autoitscript.com/forum/index.php?showtopic=21069

expandcollapse popup

#include "Word.Au3"
#include "Excel.Au3"

If ProcessExists("WINWORD.EXE") Then Word()
If Not ProcessExists("WINWORD.EXE") Then Excel()
    
Func Word()
Opt("WinTitleMatchMode", 2) 
;Winword exists
$oWordApp = ObjGet("", "Word.application")
_WordQuit ($oWordApp)
While ProcessExists("WINWORD.EXE")
    WinWait("Microsoft Office Word")
    ControlClick("Microsoft Office Word", "", "[ID:6]")
    Sleep(2000)
    ControlSend("Save As", "", "", "{ENTER}")
    Sleep(2000)
WEnd
Excel()

EndFunc


Func Excel()    
Opt("WinTitleMatchMode", 2) 
;Excel exists
Run("C:\Windows\System32\CMD.EXE")

WinActivate("AutoIT v3")
$oExcel = ObjGet("", "Excel.application")
_ExcelBookClose ($oExcel)
While ProcessExists("EXCEL.EXE")
    WinWait("Microsoft Office Excel")
    ControlClick("Microsoft Office Excel", "", "[ID:6]")
    Sleep(2000)
    ControlSend("Save As", "", "", "{ENTER}")
    Sleep(2000)
WEnd
Exit

EndFunc

I have played around with lots of other commands in testing. I'm also not too sure which commands will or will not work with hidden windows. I read in another thread that using WinClose() would work, although when testing I could only get it to work if I made the Excel window active first.

Any help with this would be much appreciated.

[sgebbie]

Would someone please be able to offer some assistance with this? I'm not sure what I'm doing wrong here.

[Juvigy]

Run your script again and wait for it to :hang: , then start task manager and see if there is a Excel process.

[sgebbie]

I checked and there is definitely an EXCEL.EXE process running. I have several Excel windows open and minimized for testing and the process is there as well. I was reading something a little bit ago in another thread about using an

#include <ExcelCOM_UDF.au3> and not needing to define the _ExcelBookSaveAs() I'm not using that particular command but would this work the same for not needing to define _ExcelBookClose()?

I tried adding that to the beginning of my script but am getting an error opening the file? I'm not sure if I'm using that correctly. I'm only a little bit familiar with the #include commands but haven't seen it used like that yet. The only ones I've used or seen used are the ones that I'm using now #include "Word.au3" and #include "Excel.au3"

[Juvigy]

Then your script works :

While ProcessExists("EXCEL.EXE")

this line is always true ... change it or put some exit logic in that loop

[sgebbie]

Then your script works :

While ProcessExists("EXCEL.EXE")

this line is always true ... change it or put some exit logic in that loop

Why does that work with my code for Word but not Excel? It's set up the same way and works fine in the Word section of the code. The While should only be true until the other functions close all of the currently open Excel windows but the close part never starts to happen with Excel for some reason.

[sgebbie]

Thanks anyway for the suggestion. I suppose I will have at it again...joy

[Juvigy]

This is your loop :

While ProcessExists("EXCEL.EXE")
    WinWait("Microsoft Office Excel")
    ControlClick("Microsoft Office Excel", "", "[ID:6]")
    Sleep(2000)
    ControlSend("Save As", "", "", "{ENTER}")
    Sleep(2000)
WEnd

I dont see anywhere inside it closing Excel. When your program enters the loop and there is an excel process the loop will never end.

You need to put some exit logic inside the loop like changing:

ControlClick("Microsoft Office Excel", "", "[ID:6]")

to

If ControlClick("Microsoft Office Excel", "", "[ID:6]")<>1 then ExitLoop

That way if your ControlClick fails it will exit the loop and not loop indefinitely

[sgebbie]

This is your loop :

While ProcessExists("EXCEL.EXE")
    WinWait("Microsoft Office Excel")
    ControlClick("Microsoft Office Excel", "", "[ID:6]")
    Sleep(2000)
    ControlSend("Save As", "", "", "{ENTER}")
    Sleep(2000)
WEnd

I dont see anywhere inside it closing Excel. When your program enters the loop and there is an excel process the loop will never end.

You need to put some exit logic inside the loop like changing:

ControlClick("Microsoft Office Excel", "", "[ID:6]")

to

If ControlClick("Microsoft Office Excel", "", "[ID:6]")<>1 then ExitLoop

That way if your ControlClick fails it will exit the loop and not loop indefinitely

That makes sense as to avoid the loop but why would it not even attempt to close in the first place? With my Word process at the beginning I would assume that if the control click failed the same thing would end up happening there as well. Though with my current testing it does work, then hangs on the Excel part.

Your suggestion will help to make sure I don't get stuck in an indefinite loop, but do you know why the Word function can "get" an inactive Word window but the Excel function can't? That's where I'm failing to see where the problem is or how to create a workaround for that. I did try this piece of code at first in the Excel function thinking it should work the same way it did in my Word function but it failed. I also read in the help file that this piece of code will only work if Excel is "activated" which doesn't really make sense to me. How does this piece of code really do anything at all if Excel has to be active first? Isn't that what that particular piece of code is doing anyway? "Getting" the existing Excel document? It seems to me that once it actually "gets" the object, I can continue on and manipulate it how I would like. Frustrating that it went so smooth with Word but Excel is being rather difficult.

; Get an existing Excel Object - but doesn't seem to work if it's not already active???
$oExcel = ObjGet("", "Excel.application")

; Get an existing Word Document - works and gets a non active Word document
$oWordApp = ObjGet("", "Word.application")

Basically this is not even getting far enough to get stuck in an indefinite loop.

Is there any way that I can "get" an existing Excel document that is not active without using WinActivate? I trying to make this able to run behind a locked screen and I've read several different places that WinActivate will not work behind a locked screen.

Edited December 30, 2009 by sgebbie

[Juvigy]

Check your code :

$oExcel = ObjGet("", "Excel.application")
_ExcelBookClose ($oExcel)

I think you are closing the open workbook , but excel application is still open - what exactly do you want to do?

If you want to close excel try ProcessClose(Excel).

[sgebbie]

Check your code :

$oExcel = ObjGet("", "Excel.application")
_ExcelBookClose ($oExcel)

I think you are closing the open workbook , but excel application is still open - what exactly do you want to do?

If you want to close excel try ProcessClose(Excel).

I want to close any currently open Excel documents and save any that have not yet been saved. I couldn't get the _ExcelBookSaveAs to work when I was messing with those commands so I was trying to do it this way instead since I had it working with Word already. I was wondering the same thing about only closing the open workbook but it doesn't appear that any of the workbooks are actually closing either. Everything just seems to stop right there.

[Juvigy]

Then you need to do something like this:

$oExcel = ObjGet("", "Excel.Application")
$wbooks = $oExcel.Application.ActiveWorkbook
           For $element In $oExcel.Application.Workbooks
               If $element.Path <> "" Then
                   $element.Save
           $element.Close
               Else  
                   $element.Activate
                   $oExcel.Application.Dialogs(5).Show
               EndIf
           Next
       $wbooks.Activate
$oExcel.Application.Quit

It Saves all workbooks and closes excel.

[the123punch]

Then you need to do something like this:

$oExcel = ObjGet("", "Excel.Application")
$wbooks = $oExcel.Application.ActiveWorkbook
           For $element In $oExcel.Application.Workbooks
               If $element.Path <> "" Then
                   $element.Save
           $element.Close
               Else  
                   $element.Activate
                   $oExcel.Application.Dialogs(5).Show
               EndIf
           Next
       $wbooks.Activate
$oExcel.Application.Quit

It Saves all workbooks and closes excel.

Hi,

How would I go to search for one particular file to see if it is open? In the case it is open, I would want to close it.

Instead of $element.Path can we use $element.FullPath??

Thanks.

the123punch

[Juvigy]

Modified it a little bit:

$oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
               If $element.FullName <> "" Then
                   MsgBox(0,"",$element.FullName)
                   $element.Save
                   $element.Close
               Else  
                   $element.Activate
                   $oExcel.Application.Dialogs(5).Show
               EndIf
           Next
$oExcel.Application.Quit

If you just want to check if a specific wbook is open it is best to use ExcelAtach and pass the filename as a param.

Edited January 5, 2010 by Juvigy