Making several Excel Apps Visible

[Guest]

Is it possible to write a sub that would make visible (Excel.Visible=True)
various instances of Excel applications that are currently running invisible?

I'm debugging a VB program that opens a new Excel application and keeps it
invisible (Excel.Visible-False) while processing data, and then closes it
(Excel.Quit). As I am debugging it, I often interrupt the program (because
of exceptions, etc.) before it quits Excel, and I subsequently find in the
Task Manager numerous Excel applications running, but they are all invisible,
so I can't look at them or close them easily.

I know you can access one Excel app using GetObject(,"Excel.Application"),
but is there a way to loop through all of the Excel apps running to make each
of them visible? I can see how many are running using
Diagnostics.Process.GetProcessesByName, but I can't figure out how to make
all of them visible.

 

[RB Smissaert]

How about when you startup the Excel app keep hold of the objects in a
collection or array and go through that to make them all visible?
What is your code that starts the Excel apps?

RBS

 

[Guest]

I'm not creating an array of Excel apps. I'm simply running a program that
starts with e.g.

Dim Exc as new Excel.Application
Exc.Visible=False
.... (code here) ...
Exc.Quit

The problem is that during the "..." above, an exception occurs, I quit the
program, fix the bug, and then start the program again. Since the program
was interrupted before the Exc.Quit command was executed, the instance of
Excel that was created is still running. When I run the program again,
another instance is created. After doing this a few times, I end up with
several instances of Excel running that are all invisible.

Anyway, regardless of what causes it, I'd like to know if there were a way
of looping through the various instances of Excel that are running. This
could be helpful for other purposes as well. I know how you can do this with
Internet Explorer using ShellWindows, but I don't know of a comparable
command in Excel.

 

[Jake Marx]

Hi Ed,

Anyway, regardless of what causes it, I'd like to know if there were
a way of looping through the various instances of Excel that are
running. This could be helpful for other purposes as well. I know
how you can do this with Internet Explorer using ShellWindows, but I
don't know of a comparable command in Excel.

I don't think there's a (non-API) way to do it. You'd have to use
FindWindow and other API calls to find each EXCEL process, get the window
handle, then make it visible. If you're in VB.NET, you could kill all
running EXCEL processes with something like this:

Dim processList() As Diagnostics.Process
Dim i As Integer

processList = Diagnostics.Process.GetProcessesByName("EXCEL")

For i = 0 To processList.Length - 1
processList(i).Kill()
Next

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[RB Smissaert]

I'm not creating an array of Excel apps

No, but you could store the created objects to find them back and make them
visible.
Otherwise you could use the FindWindow API.

RBS

 

[Guest]

Hi Jake,
Yes, I was aware the the Diagnostics.Process procedure could be used to loop
through each app and close it.

I could not find an API to make a window visible. What is it? (I assume
you have to obtain the HWND for the window, which you could do using the
Diagonostics.Process business, so once I have the window's HWND, which API do
I use to make it visible?)
Ed

Hi Ed,

Anyway, regardless of what causes it, I'd like to know if there were
a way of looping through the various instances of Excel that are
running. This could be helpful for other purposes as well. I know
how you can do this with Internet Explorer using ShellWindows, but I
don't know of a comparable command in Excel.

I don't think there's a (non-API) way to do it. You'd have to use
FindWindow and other API calls to find each EXCEL process, get the window
handle, then make it visible. If you're in VB.NET, you could kill all
running EXCEL processes with something like this:

Dim processList() As Diagnostics.Process
Dim i As Integer

processList = Diagnostics.Process.GetProcessesByName("EXCEL")

For i = 0 To processList.Length - 1
processList(i).Kill()
Next

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[Chip Pearson]

Rather than

Dim Exc as new Excel.Application

you should use

Dim Exc As Excel.Application ' note: no New keyword
On Error Resume Next
Set Exc = GetObject(, "Excel.Applicaton")
If Err.Number <> 0 Then
Set Exc = CreateObject("Excel.Application")
End If

This way you'll be working with only one instance of Excel in the
first place.

If you want to close all running instances of Excel, use code
like

Private Declare Function FindWindow Lib "user32" Alias
"FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String)
As Long
Private Declare Function SendMessage Lib "user32" Alias
"SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As
Long, lParam As Any) As Long
Private Const WM_CLOSE = &H10


Private Sub CloseAllExcels()
Dim XLHwnd As Long
XLHwnd = FindWindow("XLMAIN", vbNullString)
Do Until XLHwnd = 0
SendMessage XLHwnd, WM_CLOSE, 0&, 0&
XLHwnd = FindWindow("XLMAIN", vbNullString)
Loop
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jake Marx]

Hi Ed,

Yes, I was aware the the Diagnostics.Process procedure could be used
to loop through each app and close it.

I could not find an API to make a window visible. What is it? (I
assume you have to obtain the HWND for the window, which you could do
using the Diagonostics.Process business, so once I have the window's
HWND, which API do I use to make it visible?)
Ed

I think SetWindowPos has a flag for visibility. And yes, it just takes in a
HWND.

http://msdn.microsoft.com/library/d...dowreference/windowfunctions/setwindowpos.asp

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[RB Smissaert]

That is it, nice and simple.

RBS

Rather than

Dim Exc as new Excel.Application

you should use

Dim Exc As Excel.Application ' note: no New keyword
On Error Resume Next
Set Exc = GetObject(, "Excel.Applicaton")
If Err.Number <> 0 Then
Set Exc = CreateObject("Excel.Application")
End If

This way you'll be working with only one instance of Excel in the first
place.

If you want to close all running instances of Excel, use code like

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam
As Any) As Long
Private Const WM_CLOSE = &H10


Private Sub CloseAllExcels()
Dim XLHwnd As Long
XLHwnd = FindWindow("XLMAIN", vbNullString)
Do Until XLHwnd = 0
SendMessage XLHwnd, WM_CLOSE, 0&, 0&
XLHwnd = FindWindow("XLMAIN", vbNullString)
Loop
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

In the last part of your reply, where you have a loop through all the Excel
instances and you use FindWindow to get the handle for each Excel window, is
there another API command that could be used to make the window visible
(instead of closing it)?

 

[Chip Pearson]

I don't believe that is possible. The reason the loop worked with
WM_CLOSE is that sending that message causes that instance Excel
to close, so FindWindow would find the subsequent Excel window
handle. Looping without closing the application window will cause
FindWindow to find the same window each time.

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

In the last part of your reply, where you have a loop through
all the Excel
instances and you use FindWindow to get the handle for each
Excel window, is
there another API command that could be used to make the window
visible
(instead of closing it)?

 

[Guest]

If you know the fully qualified name of one of the workbooks in each of the
invisible Excel sessions, you can make all of them visible:

set xl=getobject(,"fully qualified workbookname")
' Check that you have got the object here

xl.Application.Visible = True

You would need to loop through the collection of workbooks that the Excel
sessions are using.

 

[Guest]

In order to get a list of all Excel handles, use the EnumWindow API call; in
the application defined callback function, use the GetClassName API (if the
window's class is XLMAIN, it is Excel); given the handle of the Excel
sessions, send SW_SHOWNORMAL using PostMessage or SendMessage API calls.

 

[Walter Wang [MSFT]]

Hi Ed,

Thank you for posting!

Here's a sample to show all excel windows using EnumWindows and callback:

Option Explicit

Public Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc As Long,
ByVal lParam As Long) As Long
Public Declare Function IsWindowVisible Lib "user32" (ByVal hwnd As Long)
As Long
Public Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA"
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Public Declare Function GetWindowTextLength Lib "user32" Alias
"GetWindowTextLengthA" (ByVal hwnd As Long) As Long
Public Declare Function BringWindowToTop Lib "user32" (ByVal hwnd As Long)
As Long
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As
Long) As Long
Public Declare Function GetClassName Lib "user32" Alias "GetClassNameA"
(ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long)
As Long
Public Declare Function ShowWindow Lib "user32.dll" (ByVal hwnd As Long,
ByVal nCmdShow As Long) As Long

Public Const SW_SHOW As Long = 5
Public Const SW_SHOWDEFAULT As Long = 10
Public Const SW_SHOWMAXIMIZED As Long = 3
Public Const SW_SHOWMINIMIZED As Long = 2
Public Const SW_SHOWMINNOACTIVE As Long = 7
Public Const SW_SHOWNA As Long = 8
Public Const SW_SHOWNOACTIVATE As Long = 4
Public Const SW_SHOWNORMAL As Long = 1


Public Function EnumWindowsProc(ByVal hwnd As Long, ByVal lParam As Long)
As Long
If ClassName(hwnd) = "XLMAIN" Then
ShowWindow hwnd, SW_SHOWNORMAL
End If

EnumWindowsProc = 1 ' return 0 will stop enumerating
End Function

Sub Main()
EnumWindows AddressOf EnumWindowsProc, ByVal 0&
End Sub

Public Function WindowTitle(ByVal lHwnd As Long) As String
Dim lLen As Long
Dim sBuf As String

lLen = GetWindowTextLength(lHwnd)
If (lLen > 0) Then
sBuf = String$(lLen + 1, 0)
lLen = GetWindowText(lHwnd, sBuf, lLen + 1)
WindowTitle = Left$(sBuf, lLen)
End If
End Function

Public Function ClassName(ByVal lHwnd As Long) As String
Dim lLen As Long
Dim sBuf As String
lLen = 260
sBuf = String$(lLen, 0)
lLen = GetClassName(lHwnd, sBuf, lLen)
If (lLen <> 0) Then
ClassName = Left$(sBuf, lLen)
End If
End Function



Regards,

Walter Wang
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter T]

If you know the fully qualified name of one of the workbooks in each of the
invisible Excel sessions, you can make all of them visible:

set xl=getobject(,"fully qualified workbookname")
' Check that you have got the object here

xl.Application.Visible = True

You would need to loop through the collection of workbooks that the Excel
sessions are using.

Just to add, if running instances include an unsaved wb named BookX can use
the same method to attach a reference

set xl=getobject(,"BookX").parent

There will be no identically named BookX's in multiple instances. Can
enumerate first "XLMAIN" then "EXCEL7" via "XLDESK" to determine if each
instance contains such a wb. Parse and trap the unique "X" suffix and go on
to set the reference.

If an instance does not include an unsaved BookX, and full name to no other
wb is known, can bring such an instance to the front and with DDE add a new
wb and use the same method to attach a reference.

I think the OP's requirements have been met with your (AA2e72E in adjacent
post) suggested use of SW_SHOWNORMAL and similar in the working example of
Walter Wang.

On the more general subject of attaching references I don't know a 100%
bullet proof method with vb/vba, though I understand with C# or.Net it's
straightforward via the ROT.

However if anyone is interested to try a clunky vba demo of what I've
outlined above contact me off-line. This normally works to set a collection
of withevents application ref's to all unknown instances. But 'normally' of
course is not quite good enough for general distribution, I suspect it can
be improved.

Regards,
Peter T
pmbthornton gmail com