Excel not exiting correctly

[adamr99]

Hi~

I am opening an instance of excel 2000 with access 2000 pulling some data into the database and closing Excel. The only problem is after I run my code, excel is still running in the background (I can see it in task manager but not in the task bar). PLEASE help me solve this major memory leak!! Here is the valid code:

Option Compare Database
Option Explicit


Private Sub Command0_Click()

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
'some objects to refer to Excel

Dim db As DAO.Database
Dim sample_info As DAO.Recordset
Dim species_comp As DAO.Recordset
Dim bio_vol As DAO.Recordset
Dim letter_look As DAO.Recordset
Dim sample_look As DAO.Recordset
Dim genus_look As DAO.Recordset


Set db = CurrentDb
Set sample_info = db.OpenRecordset("TEST_Sample_Info_FINAL&quot 'open our recordsets
Set species_comp = db.OpenRecordset("TEST_Species_Composition_Data_FINAL&quot
Set bio_vol = db.OpenRecordset("TEST_BioVolume_Data_FINAL&quot
Set letter_look = db.OpenRecordset("TEST_Letter_Lookup&quot
Set sample_look = db.OpenRecordset("Sample_ID_Master&quot
Set genus_look = db.OpenRecordset("Genus_lookup&quot



Set objXL = CreateObject("Excel.Application&quot
Set xlWB = objXL.workbooks.Open("C:\USER\sample.xls&quot
Set xlWS = xlWB.worksheets("52&quot
'set references to our excel worksheet




'xlWB.Save 'do not save the spread sheet after grabing the data


xlWB.Close
objXL.Quit


Set xlWS = Nothing
Set xlWB = Nothing
Set objXL = Nothing


sample_info.Close
species_comp.Close
bio_vol.Close
letter_look.Close
sample_look.Close
genus_look.Close


Set sample_info = Nothing
Set species_comp = Nothing
Set bio_vol = Nothing
Set letter_look = Nothing
Set sample_look = Nothing
Set genus_look = Nothing

Set db = Nothing
'tidy up time

End Sub

Any Ideas???

Thanks!
Adam

 

[adamr99]

Nobody has any ideas on this????


Adam

 

[Nikita6003]

Hiya,

just to try out - dunno if this'll help because the rogue xl thread's a real problem. But try using the correct references to Excel Objects in your Dim statements:

    Dim objXL As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Workbook

You've done the rest correcly - you've even set the objects to Nothing, so ...

hope this helps (let us know plz - many ppl have the same problem)!

Cheers
Nikki

 

[rss01]

Adam,

I had this same issue. I cant find it now but Microsoft had this as a known issue with no fix.

I would experience it worse if I tried to use excel during the time my code was running. Now when I run the code I dont use excel and have not had any more issues. Not the greatest thing but it works for now.

 

[beetee]

You may consider closing the window 'manually' using the Task API.

I unfortunately cannot provide you with an exact example as I'm running Access 97 here; but here's some sample code you may try using. The idea would be to try to identify the name of the Excel window (or task) you wish to close, then to use the CloseWindowByName function to rid yourself of it.



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 GetClassName Lib "user32" Alias _
"GetClassNameA" (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

Private Declare Function GetWindow Lib "user32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long

Private Declare Function GetNextWindow Lib "user32" Alias _
"GetNextQueueWindow" (ByVal hWnd As Long, ByVal wFlag As Integer) As Long

Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" ( _
ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) 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

Const WM_QUIT = &H12
Const WM_CLOSE = &H10

Private Const GW_HWNDFIRST = 0
Private Const GW_HWNDNEXT = 2

Const c_MaxTextLen = 256

Dim MyIteration As Integer

Public Function zTrim(ByVal MyText As String) As String
Dim Pos As Long
Pos = InStr(MyText, vbNullChar)
If Pos Then
zTrim = Left(MyText, Pos - 1)
Else
zTrim = MyText
End If
End Function


' modify this code to identify the Excel window
Sub OutputWindowNames()
MyIteration = MyIteration + 1

Dim hWnd As Long

hWnd = GetWindow(hWnd, GW_HWNDFIRST)

Dim StartWindow As Long
StartWindow = 0

Do While hWnd <> 0
Dim WindowText As String
Dim WindowCaption As String
GetWindowNames hWnd, WindowText, WindowCaption
Debug.Print hWnd & &quot;, &quot; & WindowText & &quot;, &quot; & WindowCaption
hWnd = GetWindow(hWnd, GW_HWNDNEXT)
If hWnd = StartWindow Then Exit Do
If StartWindow = 0 Then
StartWindow = hWnd
End If
Loop

MyRst.Close
MyDb.Close
End Sub




' this function closes a window with a given name

Function CloseWindowByName(ByVal LookFor As String)
Dim hWnd As Long
hWnd = Application.hWndAccessApp
hWnd = GetWindow(hWnd, GW_HWNDFIRST)

Dim StartWindow As Long
StartWindow = 0

Dim HelpLookForLen As Long
HelpLookForLen = Len(LookFor)

Do While hWnd <> 0
Dim NumChars As Long
Dim WindowText As String

WindowText = Space(c_MaxTextLen)
NumChars = GetWindowText(hWnd, WindowText, c_MaxTextLen)
If NumChars > HelpLookForLen Then
NumChars = HelpLookForLen
End If
WindowText = Trim(zTrim(WindowText))
If Len(WindowText) > 0 Then
If InStr(WindowText, LookFor) <> 0 Then
PostMessage hWnd, WM_QUIT, 0, 0
PostMessage hWnd, WM_CLOSE, 0, 0
End If
End If

hWnd = GetWindow(hWnd, GW_HWNDNEXT)
If hWnd = StartWindow Then Exit Do
If StartWindow = 0 Then
StartWindow = hWnd
End If
Loop
End Function





Hope this helps...

 

[beetee]

I left out a function in the above message:


Function GetWindowNames(hWnd As Long, WindowText As String, WindowCaption As String)
Dim NumChars As Long

WindowText = Space(c_MaxTextLen)
NumChars = GetWindowText(hWnd, WindowText, c_MaxTextLen)
WindowText = Left(zTrim(WindowText), NumChars)

WindowCaption = Space(c_MaxTextLen)
NumChars = GetClassName(hWnd, WindowCaption, c_MaxTextLen)
WindowCaption = Left(zTrim(WindowCaption), NumChars)
End Function

 

[adamr99]

Hi~

Thanks for all of your help. As it turns out ( and I know that this isn't a very good solution), I just had my office moved and got a new computer. With the new computer (still using all 2K, windows, acccess, and excel) i use the same exact code, but it closes fine.

Unfortunnately, I am unable to tell what is the solution.

Thanks! Adam