OLE /COM obtain open excel files(s) information

[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]

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.

[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

[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]

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.

[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]

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]

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]

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 Expand


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.

[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]

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 Expand


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]

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 Expand


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.