[AccessD] OT copying worksheets

Hi All:

There is also the absolute 'kill; an application. Used back when Access97
kept failing to die.

Brutally Kill an application

Option Explicit

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA"
 (ByVal lpClassName As String, 
  ByVal lpWindowName As String) As Long

Private Declare Function GetWindowThreadProcessId Lib "user32" 
 (ByVal hwnd As Long, 
  lpdwProcessId As Long) As Long

Private Declare Function GetWindowThreadProcessId Lib "user32" 
 (ByVal hwnd As Long, 
  lpdwProcessId As Long) As Long

Private Declare Function OpenProcess Lib "kernel32" 
 (ByVal dwDesiredAccess As Long, 
  ByVal bInheritHandle As Long, 
  ByVal dwProcessId As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" 
 (ByVal hObject As Long) As Long 

Sub Kill ExcelApplication()

 Dim lHwnd As Long
 Dim lPid As Long, 
 Dim lReturn As Long, 
 Dim lhwndProcess As Long
 Dim lpClassName As String
    
 Const PROCESS_ALL_ACCESS = &H1F0FFF
 
 
 ' check to see if application alive and running
 ' Believe that XLMain is the appropriate class 
 ' but could be wrong.. please check. 
 lpClassName = "XLMain"

 lHwnd = FindWindow(lpClassName, vbNullString)

 If lHwnd > 0 Then
    'Get the PID (process ID) from the application handle
    lReturn = GetWindowThreadProcessId(lHwnd, lPid)
    
    'Terminate the application
    lhwndProcess = OpenProcess(PROCESS_ALL_ACCESS, 0&, lPid)
    if (TerminateProcess(lhwndProcess, 0&) <> 0) = True then
	MsgBox "Excel has been sucessfully terminated" 
    Else
	MsgBox "Excel has not been sucessfully terminated" 
    End if

    lReturn = CloseHandle(lhwndProcess) 
 Else
	MsgBox "Excel is not running or Class name is wrong"	  
 End if
End Sub

Jim
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Wednesday, September 13, 2006 12:29 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] copying worksheets

use xlSheet.Copy after:=xlApp.Worksheets("Sheet1")
This is one of my pet hair pullers- Excel hates to give up being the center
of attention. Explicit referencing as shown above should solve the problem.
Jim Hale

-----Original Message-----
From: Hale, Jim [mailto:Jim.Hale at fleetpride.com]
Sent: Wednesday, September 13, 2006 2:03 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] copying worksheets


you need to set xlSheet to nothing as well. I have found that all variable
objects (excel related or not) need to be set to nothing or more often than
not excel will stay in memory.
Jim Hale

-----Original Message-----
From: Billy Pang [mailto:tuxedoman888 at gmail.com]
Sent: Wednesday, September 13, 2006 1:53 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] copying worksheets


ok.. here is it.  what this does is it creates a brand new excel workbook,
write something to sheet2 and then make copy of sheet2.

what i have noticed is that when copy sheet2 code is there, the xlApp object
is indestructible! (ie. cannot be destroyed; like superman).  you can still
see that excel.exe still exists in windows task manager after execution.
xlapp survived the set xlApp=nothing onslaught.

however, if you comment out the copy sheet2 code, xlApp can be destroyed.
the kryponite.

Public Function fCopyYoWorksheet()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Debug.Print "starting..."
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet2")
xlSheet.Range("B2").Value = "Yo! numbers"
xlSheet.Copy after:=Worksheets("Sheet1")
xlBook.Close ' it will prompt you to save
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

Debug.Print "finished!"

End Function





***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com