Automation - xlapp.Quit does not kill Excel

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

re: Automation - xlapp.Quit does not kill Excel

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" <deko@hotmail.com> wrote in message
news:iX3Ud.9681$Pz7.6299@newssvr13.news.prodigy.co m...[color=blue]
> 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
>
>[/color]

re: Automation - xlapp.Quit does not kill Excel

> This line:[color=blue]
> 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.[/color]

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

re: Automation - xlapp.Quit does not kill Excel

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" <deko@hotmail.com> wrote in message
news:pchUd.7542$OU1.4941@newssvr21.news.prodigy.co m...[color=blue][color=green]
>> 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.[/color]
>
> 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
>
>
>[/color]