RE: Ghostly presence of Excel process

Without testing all this, I can't be absolutely positive where the problem
lies; however, these are some things to consider.

First, this is not uncommon. The problem is caused by incomplete object
referencing to your Excel objects. When Access sees an Excel object
reference that is not fully qualified, it will create another instance of the
Excel process. When you issue the Quit statement, the instance of Excel you
created in destroyed, but the one that Access created can still be running.
I see one place that is suspect:

Cells.Select
Cells.EntireColumn.AutoFit

These two lines don't explicitly tie the cells to your instance.

That may be why blXLRunning = IsExcelRunning is always true.

Also, I notice that after you call your function StripXLFormats, you open
the workbook again but never Quit Excel.

If you did, then the spread*** would disappear. If you want the user to
see the spread*** after you have completed it, the better way is to save
it, close, quit Excel, then use the Shell function to open it.


"ragtopcaddy via AccessMonster.com" wrote:

The code below runs at the end of a sub that exports queries via a
transferspread*** routine to a new workbook which then has to be formatted,
due to the fact that Excel inexplicably turns all of my numbers into text,
and opened so the user can see his handiwork. Even on those rare occasions
when I don't get runtime error '462', and the code executes and opens the
workbook, closing the workbook manually leaves an instance of Excel.exe
running in Task Manager Processes, though not in Applications. Inevitably,
this results in runtime error '462' the next time I try to run the code. What
a nightmare! I'm slowly discovering how many ways one can "close" Excel
without closing it! Any suggestions would be appreciated. I've spent the
entire day searching and reading posts.

Thanks,

Bill R

StripXLFormats strPath & strFile


blXLRunning = IsExcelRunning

If Not blXLRunning Then
Set xlObj = New Excel.Application
Else
Set xlObj = GetObject(, "Excel.Application")
End If

Set WkBk = xlObj.Workbooks.Open(strPath & strFile)
xlObj.Visible = True
Set WkBk = Nothing
Set xlObj = Nothing

blXLRunning = IsExcelRunning

If blXLRunning Then
MsgBox "Excel is STILL running!", vbExclamation & vbOKOnly, "EXCEL"
Stop
End If
End If

Function StripXLFormats(strWkBk As String, Optional strWkSht As String)
Dim WkBk As Excel.Workbook
Dim WkSht As Excel.Work***
Dim Cell As Excel.Range
Dim bl1*** As Boolean
Dim xlObj As Object
Dim blXLRunning As Boolean

blXLRunning = IsExcelRunning

If strWkSht <> "" Then
bl1*** = True
GoTo One***
End If

'Set xlObj = CreateObject("excel.application")
If Not blXLRunning Then
Set xlObj = New Excel.Application
Else
Set xlObj = GetObject(, "Excel.Application")
End If
Set WkBk = xlObj.Workbooks.Open(strWkBk)
For Each WkSht In WkBk.Worksheets
One***:
WkSht.Activate
WkSht.Range("A1").Activate
WkSht.Range("A1").CurrentRegion.Select
For Each Cell In Selection
If Len(Cell.Value) >= 1 And Cell.PrefixCharacter = "'" Then
With Cell
.NumberFormat = "@" 'keep it text
.Value = .Value
If InStr(.Value, "%") > 0 Then
.Replace What:="%", Replacement:="", LookAt:=xlPart
If IsNumeric(.Value) And .Row > 1 Then
.Value = .Value / 100
.NumberFormat = "#%"
End If
ElseIf InStr(.Value, "$") > 0 Then
.Replace What:="$", Replacement:="", LookAt:=xlPart
If IsNumeric(.Value) And .Row > 1 Then
.Value = .Value
.NumberFormat = "$#,##0.00##"
End If
ElseIf IsNumeric(.Value) And .Row > 1 Then
.Value = .Value
.NumberFormat = "#0.0#####"
End If
End With
End If
Next Cell
Cells.Select
Cells.EntireColumn.AutoFit
WkSht.Range("A1").Select
If bl1*** Then GoTo OuttaHere
Next WkSht

OuttaHere:
Set WkSht = Nothing

With WkBk
.Sheets(1).Select
.Save
.Close
End With
Set WkBk = Nothing
xlObj.Quit
Set xlObj = Nothing
'After "closing" excel in the above code, the following is always true
blXLRunning = IsExcelRunning

If blXLRunning Then
MsgBox "Excel is STILL running!", vbExclamation & vbOKOnly, "EXCEL"
Stop
End If

End Function

Public Function IsExcelRunning() As Boolean
Dim xl As Object

On Error Resume Next

Set xl = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)

Set xl = Nothing
Err.Clear
End Function

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via http://www.accessmonster.com

.