Closing an Excel workbook window with no title

[mjwillyone]

Hello, all.  I have a quick question about closing an Excel file when there is no window title.  Roboscript can’t find one and when I attempt keystrokes to close the file (using the saved name of the file as the window title) my alt keystrokes end up in cells of the file.

I can kill Excel, but since there are 2 more Excel workbooks open and some not yet saved, when I reopen Excel I have to deal with auto-save warning issues.

So, I would like to close each open window, with no need to save changes on any of them.  The name of the file on the top of the open windows will differ each day the routine is run.

Thank you very much for your help!!
Mike

[td]

Normally COM Automation is the choice for Office applications but I believe Windows does not allow WinBatch to access Excel objects ROT on Windows 10. You didn't mention which version of Excel you are using, but you might consider using the Analysis.wbt script in your WinBatch install folder. That will give you some idea of the window names and child windows available with an Excel instance. You and use the Control Manager Extender and/or several WIL functions to get a window handle to each Excel instance and close them.  One trick is to use partial-window-names to access windows with changing names. It will take a little time and patience but it should be doable.

 

[mjwillyone]

Thank you.  Version 2007 is our version of Excel and it usually works great with Winbatch .. in fact, I am running macros using keystrokes without any issue on the Excel workbook that created this file.  For some reason that one file (lacking a name, so says Roboscript) simply isn't receiving any keystrokes.

[td]

That is why you need to use the Analyzer script. Robocripter can only see the Window at the top of the z-order using the cursor drag technique. Windows applications often have multiple windows layered on top of each other with the title bearing main window covered by another window at the top of the z-order.

[stanl]

I'm a little confused. Are we talking about multiple instances of Excel, or a single instance with multiple workbooks. And, roboscript or sendkeys to deal with things?, I have never had any issues with closing either as COM Objects.

[td]

The problem isn't closing an Excel object. The problem is COM gaining access to an already running Excel process. This is traditionally done with WIL by calling ObjectGet with nothing in the first parameter and the Excel progid in the second parameter. However, on Windows 10 an Excel object is listed in the ROT by each running Excel process but ObjectGet cannot access them. On Windows 7 you could work around the problem by making sure that the script was running at the same UAC security level as the Excel process but that doesn't appear to be the case on Windows 10.  There may still be a workaround for this but I would require more research and testing to identify.

For Roboscripter and the SendKey functions, it is likely a matter of z-order and input focus.

[td]

That was easy. The ROT COM object for a running Excel object can be accessed using GetObject. After typing the above I checked the test script and discovered that I had used an empty string ('') for the first parameter instead of calling the function like the following:
 

Code: Winbatch

objEx = ObjectGet( , "Excel.Application")

Of course, you still need to have the script running with the same integrity as the running Excel process.

Whether or not that helps the OP is unknown.

[stanl]

Another stab from the past. Found some WB 2004 code I compiled for remote users where I had to check if Excel instances were running and have them close before my script would continue. [obviously pre-Win10]

[stanl]

Of course, you still need to have the script running with the same integrity as the running Excel process.

I tried this as GetObject_if.wbt (with Excel Open with Blank workbook)

Code: Winbatch

oXL=0
ObjectClrOption ( 'useany', 'System')
oM = ObjectClrNew('System.Runtime.InteropServices.Marshal')
;Message("InteropServices",oM)
oXL=oM.GetActiveObject('Excel.Application')


If oXL<>0
   Message("Active Instance of Excel",oXL)
   cnt = oXL.Workbooks.Count
        Message(oXL,"ActiveWorkbooks: ",cnt)
Else
        Message(oXL,"No Active Excel Instances")
Endif
oXL=0
oM=0
Exit


 

It fails on Workbooks Count... but this was just for fun

[stanl]

and there is this - though probably not very useful

Code: Winbatch

oXL=0
ObjectClrOption ( 'useany', 'System')
oProcess = ObjectClrNew('System.Diagnostics.Process')
xl = oProcess.GetProcessesByName("Excel")
isvalid = Arrinfo(xl,-1)
If isvalid Then Message("Excel Instances",Arrinfo(xl,1))
oProcess=0
Exit
 

[td]

To use COM Automation you need to give the file a .wbt_if extension so that it runs as invoker/false. For example, ExcelRot.wbt_if.

Code: Winbatch

;;;
;; Since Excel can leave a detached process
;; running with a ROT table entry, you can't
;; count on ObjectGet failing. To workaround
;; this use a for loop instead of while loop.
IntControl(73,1,0,0,0)
for i = 1 to 5
   Excel = ObjectGet( , "Excel.Application")
   Excel.Quit()
   Excel = 0
next

:WBERRORHANDLER
IntControl(73,0,0,0,0)
exit
 

Of course, you can use COM properties and methods to check each running object to determine if it needs to be saved before it is terminated among other things.

[stanl]

Maybe reading a different set of rules, but my understanding is if even 2-3 instances of Excel are running. GetObject(), GetActiveObject() or GetProcessesByName["Excel") will only return the first instance. I tried each with 3 instances of Excel active.  This places a few caveats on whatever goal the OP has in mind.

[td]

The OPs description may be open to multiple interpretations but as I read it the goal was to close each open Excel "Window" without the unsaved data warning. The script I posted above can be used for that purpose* because each iteration will close one of them.   

* with the addition of the line "Excel.DisplayAlerts = @FALSE".

[td]

This may or may not suit the OPs needs and is susceptible to vagaries of Excel objects in the ROT but it kinda sorta does the job.

Code: Winbatch

IntControl(73,1,0,0,0)
for i = 1 to 5
   Excel = ObjectGet( , "Excel.Application")
   Excel.DisplayAlerts = @FALSE
   Book = Excel.ActiveWorkbook()
   File = Book.FullName()
   ; Use an applicable file name here.
   if StrIndex(File, 'test', 1, @Fwdscan) then Book.SaveAs(File)  
   Excel.Quit()
   Excel = 0
next

:WBERRORHANDLER
IntControl(73,0,0,0,0)
exit

[stanl]

The OPs description may be open to multiple interpretations

That is an understatement .  In the original post the OP wrote: So, I would like to close each open window, with no need to save changes on any of them


So, to save or not to save... that is the question.  I agree, regardless of method/function to discover running Excel instances, that a loop or foreach is required.

[td]

I added the savas call to illustrate one way of handling the general case. Obviously, when saving changes is not a concern then the relevant lines can be removed unless saving data is necessary to force Excel to terminate. And obviously, saving a file before terminating Excel prevents Excel from fussing about recovering data when the file is opened the next time.

[td]

Neglected to mention that WinBatch script file extensions are documented here:

https://www.winbatch.com/hints.html#extension