Closing an Excel workbook window with no title

Started by mjwillyone, December 02, 2021, 03:29:31 AM

Previous topic - Next topic

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.

"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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) Select
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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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

Quote from: td on December 03, 2021, 02:24:31 PM
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) Select


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


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) Select
;;;
;; 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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".
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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


"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Quote from: td on December 04, 2021, 02:12:13 PM
The OPs description may be open to multiple interpretations


That is an understatement :D .  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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

td

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

https://www.winbatch.com/hints.html#extension
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade