user32 api DESTROY WINDOW need help

  • Thread starter Thread starter Kevin McCartney
  • Start date Start date
K

Kevin McCartney

Hi, if you not familiar with api references you may not
want to try and help.

brief description: my database exports data to Excel, a
user reviews it and if the don't like it, they closes the
workbook window and not the application, Excel disappears
from the Task Bar but remains in the Task manager. I
retrieve the window handle of all hidden Excel session and
then I want to destroy them to close them so the stop
taking up resources. Problem the DESTROY WINDOW fails to
close the window handle that i pass it. The attached text
file contains an example simply put it in module and
execute the last sub procedure. I place comments where you
should put break points so you can start Task Manager and
then you'll see what I'm getting stressed about.

Hope you can help
Best regards
KM

Oh poo when did they not allow attachments. below is a lot
of code.

********************************************************
Option Explicit

Private Declare Function GetCurrentVbaProject
Lib "vba332.dll" Alias "EbGetExecutingProj" (hProject As
Long) As Long
Private Declare Function GetFuncID Lib "vba332.dll"
Alias "TipGetFunctionId" (ByVal hProject As Long, ByVal
strFunctionName As String, ByRef strFunctionId As String)
As Long
Private Declare Function GetAddr Lib "vba332.dll"
Alias "TipGetLpfnOfFunctionId" (ByVal hProject As Long,
ByVal strFunctionId As String, ByRef lpfn As Long) As Long
Private Declare Function GetWindowText Lib "user32"
Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpString
As String, ByVal cch As Long) As Long
Private Declare Function GetWindowTextLength Lib "user32"
Alias "GetWindowTextLengthA" (ByVal hWnd As Long) As Long
Private Declare Function GetClassName Lib "user32"
Alias "GetClassNameA" (ByVal hWnd As Long, ByVal
lpClassName As String, ByVal nMaxCount As Long) As Long
Private Declare Function IsWindowVisible Lib "user32"
(ByVal hWnd As Long) As Long
Private Declare Function EnumWindows Lib "user32" (ByVal
lpEnumFunc As Long, ByVal lngParam As Long) As Long
Private Declare Function DestroyWindow Lib "user32" (ByVal
hWnd As Long) As Long
Private Declare Function GetLastError Lib "kernel32" () As
Long

Private Declare Sub SetLastError Lib "kernel32" (ByVal
dwErrCode As Long)
Private Declare Function FormatMessage Lib "kernel32"
Alias "FormatMessageA" (ByVal dwFlags As Long, lpSource As
Any, ByVal dwMessageId As Long, ByVal dwLanguageId As
Long, ByVal lpBuffer As String, ByVal nSize As Long,
Arguments As Long) As Long

Const FORMAT_MESSAGE_ALLOCATE_BUFFER = &H100
Const FORMAT_MESSAGE_FROM_SYSTEM = &H1000
Const LANG_NEUTRAL = &H0
Const SUBLANG_DEFAULT = &H1
Const ERROR_BAD_USERNAME = 2202&

Dim maryWindows() As String
Dim mZ As Integer

Private Function fncAddrOf(strFuncName As String) As Long
Dim hProject As Long
Dim lngResult As Long
Dim strID As String
Dim lpfn As Long
Dim strFuncNameUnicode As String

Const NO_ERROR = 0

' The function name must be in Unicode, so convert it.
strFuncNameUnicode = StrConv(strFuncName, vbUnicode)

' Get the current VBA project
Call GetCurrentVbaProject(hProject)

' Make sure we got a project handle... we always should,
but you never know!
If hProject <> 0 Then
' Get the VBA function ID (whatever that is!)
lngResult = GetFuncID(hProject, strFuncNameUnicode,
strID)

' We have to check this because we GPF if we try to
get a function pointer of a non-existent function.
If lngResult = NO_ERROR Then
' Get the function pointer.
lngResult = GetAddr(hProject, strID, lpfn)

If lngResult = NO_ERROR Then
fncAddrOf = lpfn
End If
End If
End If
End Function

Private Function fncCallBackFunc(ByVal hWnd As Long, ByVal
lngParam As Long) As Long

Dim strCaption As String

' The following expression is true unless lngParam is
True (you're looking only for visible
' windows) and the current window isn't visible. Look up
the Imp operator in online help for more info.
If CBool(lngParam) Imp IsWindowVisible(hWnd) Then
' Retrieve the window's caption. If it's got a
caption, add it to the list of windows.
strCaption = fncGetClassName(hWnd)
If Len(strCaption) > 0 And InStr(1,
strCaption, "XLMAIN") And IsWindowVisible(hWnd) = 0 Then
'frmCallbacks.AddItem strCaption, hwnd
ReDim Preserve maryWindows(mZ)
maryWindows(mZ) = Left(strCaption, InStr(1,
strCaption, "-") - 1)
mZ = mZ + 1
End If
End If
' Returning anything besides True causes the enumeration
to stop.
fncCallBackFunc = True
End Function

Private Function fncGetClassName(hWnd As Long) As String
Dim nCount As Long
Dim strBuffer As String

strBuffer = Space(80)
nCount = GetClassName(hWnd, strBuffer, 80)
fncGetClassName = hWnd & "-" & Mid(strBuffer, 1, nCount)
End Function

Public Sub subCloseHiddenExcel()
Dim Buffer As String
Dim hWnd As Long
Dim Z As Integer

mZ = 0
Call EnumWindows(fncAddrOf("fncCallBackFunc"), CLng
(False))
If Not IsEmpty(maryWindows) Then
For Z = 0 To UBound(maryWindows)
hWnd = CLng(maryWindows(Z))
' Place a breakpoint here because the next line is
supposed to destroy the hidden Excel Application.
' But the last error always states 'The specified
username is invalid'
If DestroyWindow(hWnd) = 0 Then
Buffer = Space(200)
SetLastError ERROR_BAD_USERNAME
FormatMessage FORMAT_MESSAGE_FROM_SYSTEM, ByVal
0&, GetLastError, LANG_NEUTRAL, Buffer, 200, ByVal 0&
MsgBox Buffer
End If
Next Z
End If
End Sub

Private Sub ExampleExcelHiddenWindow()
Dim xlapp As New Excel.Application

xlapp.SheetsInNewWorkbook = 1
xlapp.Workbooks.Add
' Numerous code to spin through several queries
exporting data and then format the sheets
' for presentational purpose adding sub totals and
adding graphs on additional sheets.
' In some case the user may not want to keep the report
because they would like to limit the
' queries again with different criteria driven from a
menu. Thus they just close the workbook window
' and the Excel application hides it self. I used to
force the user into saving the excel file so
' I could quit the application but users were not happy
having to save and delete unwanted reports.
' Thus I do not quit the application but I do not want
resources used up by hidden Excel sessions
' because the users don't close the application, they
just close the workbook.
' I've placed the code to simulate the problem. So place
a breakpoint on the below line, this will
' allow you to open Task Manager, you will then see an
Image Name EXCEL.EXE
' Now when you close the active workbook, (Use the close
window X on the window bar to simulate.)
' the Excel Application will disappear from your task
bar but
' remain in the tak manager, thus I want to use the
above procedure subCloseHiddenExcel to destroy
' the windows of hidden Excel sessions before a report
is generated. The only problem is the api
' call destroy window does not destroy the window handle
that I pass it and I don't know why.
xlapp.Visible = True

End Sub
*********************************************************
 
Gak! Why bother with APIs for that? If you started Excel via automation, you
already have an active reference to it. Wait for the user to close the
workbook, then quit Excel via that reference. If, after doing that, you can
still see Excel in the task manager, then, Windows has decided to leave it
there - and presumeably, will be just as happy to throw it away, if it
decides that it needs more resources.

HTH,
TC
 
Simple reason is that I don't want to put my access
applicatoin in a loop to wait until the Excel object is
finished with because the user will not doubt want to
compare to reports and save the appropriate one, thus the
export process has to end.

HTH... no it didn't
regards
KM
 
Well I figured it out myself, I just need the API call
GetWindowThreadProcessId, so if anyone has a similar
problem you can use the following code.

The code is looking for all hidden Excel session, looking
XLMAIN which is the class name 'GetClassName'

Hope its useful for you.
regards
KM

*******************************************
Option Explicit

Private Declare Function CloseHandle Lib "kernel32" (ByVal
hObject As Long) As Long
Private Declare Function EnumWindows Lib "user32" (ByVal
lpEnumFunc As Long, ByVal lngParam As Long) As Long
Private Declare Function GetAddr Lib "vba332.dll"
Alias "TipGetLpfnOfFunctionId" (ByVal hProject As Long,
ByVal strFunctionId As String, ByRef lpfn As Long) As Long
Private Declare Function GetClassName Lib "user32"
Alias "GetClassNameA" (ByVal hWnd As Long, ByVal
lpClassName As String, ByVal nMaxCount As Long) As Long
Private Declare Function GetCurrentVbaProject
Lib "vba332.dll" Alias "EbGetExecutingProj" (hProject As
Long) As Long
Private Declare Function GetFuncID Lib "vba332.dll"
Alias "TipGetFunctionId" (ByVal hProject As Long, ByVal
strFunctionName As String, ByRef strFunctionId As String)
As Long
Private Declare Function GetWindowThreadProcessId
Lib "user32" (ByVal hWnd As Long, lpdwProcessId As Long)
As Long
Private Declare Function IsWindowVisible Lib "user32"
(ByVal hWnd As Long) As Long
Private Declare Function OpenProcess Lib "Kernel32.dll"
(ByVal dwDesiredAccessas As Long, ByVal bInheritHandle As
Long, ByVal dwProcId As Long) As Long
Private Declare Function TerminateProcess Lib "kernel32"
(ByVal hProcess As Long, ByVal uExitCode As Long) As Long

Private Const SYNCHRONIZE = &H100000
Private Const PROCESS_TERMINATE As Long = &H1

Dim maryWindows() As Long
Dim mZ As Integer

Private Function fncAddrOf(strFuncName As String) As Long
Dim hProject As Long
Dim lngResult As Long
Dim strID As String
Dim lpfn As Long
Dim strFuncNameUnicode As String

Const NO_ERROR = 0

' The function name must be in Unicode, so convert it.
strFuncNameUnicode = StrConv(strFuncName, vbUnicode)

' Get the current VBA project
Call GetCurrentVbaProject(hProject)

' Make sure we got a project handle... we always should,
but you never know!
If hProject <> 0 Then
' Get the VBA function ID (whatever that is!)
lngResult = GetFuncID(hProject, strFuncNameUnicode,
strID)

' We have to check this because we GPF if we try to
get a function pointer of a non-existent function.
If lngResult = NO_ERROR Then
' Get the function pointer.
lngResult = GetAddr(hProject, strID, lpfn)

If lngResult = NO_ERROR Then
fncAddrOf = lpfn
End If
End If
End If
End Function

Private Function fncCallBackFunc(ByVal hWnd As Long, ByVal
lngParam As Long) As Long
Dim strCaption As String
Dim P_ID As Long

' The following expression is true unless lngParam is
True (you're looking only for visible
' windows) and the current window isn't visible. Look up
the Imp operator in online help for more info.
If CBool(lngParam) Imp IsWindowVisible(hWnd) Then
' Retrieve the window's caption.
strCaption = fncGetClassName(hWnd)
' If it's got the application caption that were are
after, add it to the array list.
If Len(strCaption) > 0 And InStr(1,
strCaption, "XLMAIN") And IsWindowVisible(hWnd) = 0 Then
'Get the associated Process ID on the window handel
GetWindowThreadProcessId hWnd, P_ID
ReDim Preserve maryWindows(mZ)
maryWindows(mZ) = P_ID
mZ = mZ + 1
End If
End If
' Returning anything besides True causes the enumeration
to stop.
fncCallBackFunc = True
End Function

Private Function fncGetClassName(hWnd As Long) As String
Dim nCount As Long
Dim strBuffer As String

strBuffer = Space(80)
nCount = GetClassName(hWnd, strBuffer, 80)
fncGetClassName = Mid(strBuffer, 1, nCount)
End Function

Public Sub subKillHiddenExcel()
Const fdwAccess As Long = SYNCHRONIZE Or
PROCESS_TERMINATE
Dim hProcess As Long
Dim Z As Long

Call EnumWindows(fncAddrOf("fncCallBackFunc"), CLng
(False))
If IsArrayPopulated(maryWindows) Then
For Z = 0 To UBound(maryWindows)
hProcess = OpenProcess(fdwAccess, 0&, maryWindows(Z))
If hProcess Then
TerminateProcess hProcess, 0&
CloseHandle hProcess
End If
Next Z
End If
End Sub
********************************************************

-----Original Message-----
Hi,
If you simply want to kill all EXCEL.EXE processes,
take a look at this sample. If you have VB installed you can
run it, if not you can still look at the code.
http://www.freevbcode.com/ShowCode.ASP?SearchString=kill% 20process&ID=5543

There are 3 sites I go to when I want some VB code:
http://www.freevbcode.com

http://www.mvps.org/vbnet/

http://www.mvps.org/vb/index2.html?samples.htm

I've rarely had to look elsewhere.

--
HTH
Dan Artuso, Access MVP


"Kevin McCartney" <[email protected]>
Click to expand...
wrote in message [email protected]...
 
First, I have no experience with automation of Excel sessions and
perhaps you may want to ask your question in an Excel NG. WIth that
being said, can you not simply use WIthEvents and write code to respond
to the WOrksheet Close event?
Finally, in response to your current effort, I would try sending a
CloseWIndow message not a DestroyWindow message.

-
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Simple reason is that I don't want to put my access
applicatoin in a loop to wait until the Excel object is
finished with because the user will not doubt want to
compare to reports and save the appropriate one, thus the
export process has to end.
Click to expand...

I don't understand. Regardless of which method you use to close Excel, your
application will have to wait for the user to finish using Excel, before it
forcefully closes Excel. So just defer the wait until your user is ready to
quit the Access application. If the Excel user is still using Excel, at that
time, you will have to wait then, regardless of whether you use my method or
yours to close Excel. Yes? No?

HTH... no it didn't
Click to expand...

Ok, I'll skip the HTH's!

TC
 
Back
Top