Automation - xlapp.Quit does not kill Excel

[deko]

This code runs okay, but I cannot quit Excel. I've read that this may be
due to the way I reference the object variables, but I don't know how else I
would reference them. How do I get Excel to quit gracefully?

Thanks in advance.

Set xlapp = GetObject(, "Excel.Application")
'create new on error if none exists

Set xlwkb = xlapp.Workbooks.Open(strTarget)
For Each fldSub In fld.SubFolders
strSheetName = fldSub.Name
strMdb = fld & "\" & strSheetName
If LinkTable(strMdb) Then
j = xlwkb.Worksheets.Count
xlwkb.Worksheets.Add(After:=Worksheets(j)).Name = strSheetName
'DoCmd.TransferSpreadsheet transferType:=acExport,
queryname:="qryMean", fileName:=strTarget
'DoCmd.TransferSpreadsheet acExport, 8, "qryMean", strTarget,
True
bytWsCt = bytWsCt + 1
xlwkb.Save
End If
Next fldSub

xlapp.Quit ********* 'why doesn't this work?

Select Case Err.Number
Case 429
Set xlapp = CreateObject("Excel.Application")
Resume Next

[Ken Snell]

This line:
xlwkb.Worksheets.Add(After:=Worksheets(j)).Name =
strSheetName

is creating a new reference because you're not fully declaring the
Worksheets(j) to be a property of xlwkb object.

Try this:
xlwkb.Worksheets.Add(After:=xlwkb.Worksheets(j)).N ame =
strSheetName

By the way, I have never seen syntax of
.Name = strSheetName
at the end of a Worksheets.Add method. Is this a copy/paste error?
--

Ken Snell
<MS ACCESS MVP>

"deko" <de**@hotmail.com> wrote in message
news:iX*****************@newssvr13.news.prodigy.co m...

This code runs okay, but I cannot quit Excel. I've read that this may be
due to the way I reference the object variables, but I don't know how else
I
would reference them. How do I get Excel to quit gracefully?

Thanks in advance.

Set xlapp = GetObject(, "Excel.Application")
'create new on error if none exists

Set xlwkb = xlapp.Workbooks.Open(strTarget)
For Each fldSub In fld.SubFolders
strSheetName = fldSub.Name
strMdb = fld & "\" & strSheetName
If LinkTable(strMdb) Then
j = xlwkb.Worksheets.Count
xlwkb.Worksheets.Add(After:=Worksheets(j)).Name = strSheetName
'DoCmd.TransferSpreadsheet transferType:=acExport,
queryname:="qryMean", fileName:=strTarget
'DoCmd.TransferSpreadsheet acExport, 8, "qryMean", strTarget,
True
bytWsCt = bytWsCt + 1
xlwkb.Save
End If
Next fldSub

xlapp.Quit ********* 'why doesn't this work?

Select Case Err.Number
Case 429
Set xlapp = CreateObject("Excel.Application")
Resume Next

[deko]

> This line:

xlwkb.Worksheets.Add(After:=Worksheets(j)).Name =
strSheetName

is creating a new reference because you're not fully declaring the
Worksheets(j) to be a property of xlwkb object.

Thanks - that was indeed it.

As an aside, do you know what the best way is to dump an Access Recordset
into an Excel Workseet? I've tried this:

xlapp.Workbooks(strXlsFile).Worksheets(j + 1).CopyFromRecordset rst
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).CurrentRegion.Columns.AutoFit

but something is not right...

Anyway, for added protection against any lingering instances of Excel, I've
added this:

Private Function CleanUp(procName As String)
On Error GoTo 0
Dim objProcList As Object
Dim objWMI As Object
Dim objProc As Object
'create WMI object instance
Set objWMI = GetObject("winmgmts:")
Debug.Print "Cleaning up " & procName
If Not IsNull(objWMI) Then
'create object collection of Win32 processes
Set objProcList = objWMI.InstancesOf("win32_process")
For Each objProc In objProcList 'iterate through enumerated
collection
If UCase(objProc.Name) = UCase(procName) Then
objProc.Terminate (0)
Debug.Print procName & " was terminated"
End If
Next
End If
Set objProcList = Nothing
Set objWMI = Nothing
End Function

I call it at the end of automation Function like this:

xlapp.Quit
Call CleanUp("Excel")
Set db = Nothing
Set xlapp = Nothing

[Ken Snell]

I have seen other posts from people who do like using CopyFromRecordset for
EXCEL. I personally have not used it, so I cannot comment specifically on
it.

Not sure what you mean by "but something is not right..."
--

Ken Snell
<MS ACCESS MVP>
"deko" <de**@hotmail.com> wrote in message
news:pc*****************@newssvr21.news.prodigy.co m...

This line:
xlwkb.Worksheets.Add(After:=Worksheets(j)).Name =
strSheetName

is creating a new reference because you're not fully declaring the
Worksheets(j) to be a property of xlwkb object.

Thanks - that was indeed it.

As an aside, do you know what the best way is to dump an Access Recordset
into an Excel Workseet? I've tried this:

xlapp.Workbooks(strXlsFile).Worksheets(j + 1).CopyFromRecordset rst
xlapp.Workbooks(strXlsFile).Worksheets(j +
1).CurrentRegion.Columns.AutoFit

but something is not right...

Anyway, for added protection against any lingering instances of Excel,
I've
added this:

Private Function CleanUp(procName As String)
On Error GoTo 0
Dim objProcList As Object
Dim objWMI As Object
Dim objProc As Object
'create WMI object instance
Set objWMI = GetObject("winmgmts:")
Debug.Print "Cleaning up " & procName
If Not IsNull(objWMI) Then
'create object collection of Win32 processes
Set objProcList = objWMI.InstancesOf("win32_process")
For Each objProc In objProcList 'iterate through enumerated
collection
If UCase(objProc.Name) = UCase(procName) Then
objProc.Terminate (0)
Debug.Print procName & " was terminated"
End If
Next
End If
Set objProcList = Nothing
Set objWMI = Nothing
End Function

I call it at the end of automation Function like this:

xlapp.Quit
Call CleanUp("Excel")
Set db = Nothing
Set xlapp = Nothing