View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
deko[_3_] deko[_3_] is offline
external usenet poster
 
Posts: 62
Default Excel VBA - How to enter a reference to another sheet?

Following with/endwith forces VBA to create it's own( internal)
object variable for the worksheet and should be avoided as it is
uncertain if it can be dereferenced when you attempt to quit the
automated instance.


Thanks for the tip. I've tried to take every precaution to avoid a
lingering instance of Excel after Access quits. I don't use any with/end
with or object variables - except when creating a new chart or series:

Dim objChart as Object
Set objChart = xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).ChartObjects.Add _
(Left:=100, Top:=24, Width:=650, Height:=500).Chart

Dim objSeries as Object
Set objSeries = xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).ChartObjects _
(1).Chart.SeriesCollection.NewSeries
....
On Error Resume Next
xlapp.Quit
Set xlapp = Nothing
Set sn = Nothing
Set db = Nothing
Set objSeries = Nothing
Set objChart = Nothing
Exit Function

Then, just to be sure, I call CleanUp("Excel.exe")

Public Function CleanUp(procName As String)
On Error Resume Next
Dim objProcList As Object
Dim objWMI As Object
Dim objProc As Object
'create WMI object instance
Set objWMI = GetObject("winmgmts:")
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)
End If
Next
End If
Set objProcList = Nothing
Set objWMI = Nothing
End Function