OLE /COM obtain open excel files(s) information

Started by RAK, November 25, 2013, 10:59:23 AM

Previous topic - Next topic

RAK

Is there a way to get information on open excel windows? I would like ot use com to aquire open file names, locations, and parent window info.. Possible? ALl the methods I have tried: GetObject(, "Excel.Application") seem to open new objects. I require all open excel file names.

Thanks!

stanl

One method would be to use Windows Sysinternals (handle utility) which can dump the information to a text file. You might also try WMI, but not sure it is that reliable.

Are you looking for all insances of Excel, all workbooks in an instance, on a single PC or for remote systems?

td

Quote from: RAK on November 25, 2013, 10:59:23 AM
Is there a way to get information on open excel windows? I would like ot use com to aquire open file names, locations, and parent window info.. Possible? ALl the methods I have tried: GetObject(, "Excel.Application") seem to open new objexts. I just information on existing open files.

Thanks!

If GetObject(, "Excel.Application")  where not connecting to an existing instance of an Excel process, you would get a COM initialization error.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

td

Also keep UAC in mind if you are running on Windows Vista or newer.  You can't access a restricted process's COM objects  from an elevated process.  See the following tech. article:

http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Excel~ObjectGet~and~UAC.txt
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

nrr

Following is right from the Tech Database and works on my PC running Windows 7.  Try it using WBStudio_IF.wbt 

objXL = ObjectGet(,"Excel.Application")
objXL.Visible = @TRUE
objActiveWorkbook = objXL.ActiveWorkbook
if objActiveWorkbook == 0
    Message("","No active workbook")
    ObjectClose(objXL)
    exit
endif
wrkbkname = objActiveWorkbook.Name

Deana

Quote from: nrr on November 25, 2013, 12:00:43 PM
Following is right from the Tech Database and works on my PC running Windows 7.  Try it using WBStudio_IF.wbt 

objXL = ObjectGet(,"Excel.Application")
objXL.Visible = @TRUE
objActiveWorkbook = objXL.ActiveWorkbook
if objActiveWorkbook == 0
    Message("","No active workbook")
    ObjectClose(objXL)
    exit
endif
wrkbkname = objActiveWorkbook.Name

I think you meant: WBStudio_IF.exe. You can also test your script using the AsInvoker manifest, by giving your script the file extension .wbt_if. This will use the AsInvoker manifested interpreter.
Deana F.
Technical Support
Wilson WindowWare Inc.

RAK

The exe is digitally signed and it is set to as invoker. NO UAC prompts appear.

I had tried this:
objXL = ObjectGet(,"Excel.Application")
objXL.Visible = @TRUE
objActiveWorkbook = objXL.ActiveWorkbook
if objActiveWorkbook == 0
    Message("","No active workbook")
    ObjectClose(objXL)
    exit
endif
wrkbkname = objActiveWorkbook.Name

There are 2 issues.. what about INactive workbooks AND what about other sessions. This only sees one (and it has no sheet). I have tried a loop to drop the first excel object and look again but it seems to find the same BLANK one each time - not one of the open excel windows.



RAK


If GetObject(, "Excel.Application")  where not connecting to an existing instance of an Excel process, you would get a COM initialization error.
Yes but when the Excel window appears from the script, it is an empty window - not one of the running excel file windows.. Maybe it's not seeing the open files?

RAK

Quote from: stanl on November 25, 2013, 11:16:15 AM
One method would be to use Windows Sysinternals (handle utility) which can dump the information to a text file. You might also try WMI, but not sure it is that reliable.

Are you looking for all insances of Excel, all workbooks in an instance, on a single PC or for remote systems?

A single PC - Local - looking for all open excel file names and parent/child information - and if possible the file paths

thanks

RAK

Quote from: Deana on November 25, 2013, 01:19:26 PM
Quote from: nrr on November 25, 2013, 12:00:43 PM
Following is right from the Tech Database and works on my PC running Windows 7.  Try it using WBStudio_IF.wbt 

objXL = ObjectGet(,"Excel.Application")
objXL.Visible = @TRUE
objActiveWorkbook = objXL.ActiveWorkbook
if objActiveWorkbook == 0
    Message("","No active workbook")
    ObjectClose(objXL)
    exit
endif
wrkbkname = objActiveWorkbook.Name

I think you meant: WBStudio_IF.exe. You can also test your script using the AsInvoker manifest, by giving your script the file extension .wbt_if. This will use the AsInvoker manifested interpreter.


OK - It works! -almost.. The Issue was I must use it in the compiled version to see the existing sessions as it is signed asInvoker.  BUT - how do I get inactive sheet names and also loop through multiple Excel windows?

stanl

Quote from: RAK on November 26, 2013, 08:51:13 AM
BUT - how do I get inactive sheet names and also loop through multiple Excel windows?

You can use WB functions to find XLMAIN, which is top Excel Window, then iterate other windows from the Application Object - however, this will not guarantee results if multiple instances of Excel are open. This assumes when you say 'window' you are not referring to an instance, but to something like the Find window in Excel, or the ActiveSheet. Perhaps you can provide more details about exactly what you are trying to accomplish.

Deana

If multiple instances of Microsoft Excel are running, ObjectGet attaches to the instance that is launched first. If you then close the first instance, another call to ObjectGet attaches to the second instance that was launched, and so forth. You can attach to a specific instance ONLY if you know the name of an open document in that instance. For example, if an instance of Excel is running with an open workbook named Book2, the following code attaches successfully to that instance even if it is not the earliest instance that was launched:

Code (winbatch) Select

xlApp = ObjectGet("Book2").Application


As Stan suggested...You might look into the SysInternals Handle.exe tool. It might be able to give you information about what, if any, Excel files are loaded.
Deana F.
Technical Support
Wilson WindowWare Inc.

RAK

Thanks for your responses,
The goal is to get a list of open excel files.

I need file names of all open Excel files.
- child windows, maximized windows, active and inactive -
this 'state' of the window effects winitemize lists so I am trying to use COM. Any method is welcome.

I want their paths too but I can search for paths  - files names are the only important goal.
Roy

stanl

Quote from: RAK on November 26, 2013, 12:48:22 PM
Thanks for your responses,
The goal is to get a list of open excel files.

I need file names of all open Excel files.
- child windows, maximized windows, active and inactive -
this 'state' of the window effects winitemize lists so I am trying to use COM. Any method is welcome.

I want their paths too but I can search for paths  - files names are the only important goal.
Roy

You might find this code snippet useful:
Code (WINBATCH) Select

oXL=0
oXL= FindWindow("XLMAIN")
If oXL<>""
   oXL = GetObject(,"Excel.Application")
Display(3,"Current Excel Workbook",oXL.ActiveWorkbook.Fullname)
If oXL.Workbooks.Count>1
   ForEach wb in oXL.Workbooks
   Display(3,"Enumerating...",wb.Fullname)
Next
Endif
oXL=0
Else
   Display(2,"Sorry....","Excel Not Currently Active")   
Endif


RAK

I am getting the object but oXL.ActiveWorkbook.Fullname errors.. Played for awhile and went back to win functions.. I did create a solution..

thanks!

stanl

Quote from: RAK on November 26, 2013, 07:39:12 PM
I am getting the object but oXL.ActiveWorkbook.Fullname errors.. Played for awhile and went back to win functions.. I did create a solution..

thanks!

Possibly you had an instance open w/out an open workbook. You can modify slightly as
Code (WINBATCH) Select

oXL=0
oXL= FindWindow("XLMAIN")

If oXL<>""
   oXL = GetObject(,"Excel.Application")
   If oXL.Workbooks.Count>0
      cWB=''
      ForEach wb in oXL.Workbooks
         cWB=cWB:wb.Fullname:@CRLF
      Next
   Endif
   oXL=0
Message("Active WorkBooks",cWB)
Else
   Display(2,"Sorry....","Excel Not Currently Active")   
Endif



Works for me, even on remote machines where it is a function call on compiled exes.