Ghostly presence of Excel process

[ragtopcaddy via AccessMonster.com]

The code below runs at the end of a sub that exports queries via a
transferspreadsheet 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.Worksheet
Dim Cell As Excel.Range
Dim bl1Sheet As Boolean
Dim xlObj As Object
Dim blXLRunning As Boolean

blXLRunning = IsExcelRunning

If strWkSht <> "" Then
bl1Sheet = True
GoTo OneSheet
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
OneSheet:
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 bl1Sheet 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

 

[RoyVidar]

The code below runs at the end of a sub that exports queries via a
transferspreadsheet 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.Worksheet
Dim Cell As Excel.Range
Dim bl1Sheet As Boolean
Dim xlObj As Object
Dim blXLRunning As Boolean

blXLRunning = IsExcelRunning

If strWkSht <> "" Then
bl1Sheet = True
GoTo OneSheet
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
OneSheet:
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 bl1Sheet 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

You are making what I call unqualified referencing

For Each Cell In Selection

uses excels selection object implicitly, and most likely is at least
one
of the reasons for your extra instance of excel in memory.

Since excel also has a cell object, I'd probably alter the name of that
variable Cell -> objCell

For Each objCell In xlObj.Selection

Likewise with
Cells.Select
Cells.EntireColumn.AutoFit

Try
xlObj.Cells.Select
xlObj.Cells.EntireColumn.AutoFit

For more details, check out
http://support.microsoft.com/default.aspx?kbid=178510

 

[Guest]

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 spreadsheet would disappear. If you want the user to
see the spreadsheet 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]

Many thanks (yet again!) to you and Roy,

I've implemented your suggestions, but I'm still having difficulty getting
rid of the Excel process. The Do While loop below is an endless loop. Could
you tell me what's missing?

The following code runs at the end of my StripXLFormats function:

Do While blXLRunning
MsgBox "Excel is still running!!!"
Stop
Set xlObj = GetObject(, "Excel.Application")
xlObj.Quit
Set xlObj = Nothing
blXLRunning = IsExcelRunning
Loop

End Function

The IsExcelRunning function code is below:

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

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 spreadsheet would disappear. If you want the user to
see the spreadsheet after you have completed it, the better way is to save
it, close, quit Excel, then use the Shell function to open it.

The code below runs at the end of a sub that exports queries via a
transferspreadsheet routine to a new workbook which then has to be formatted,

[quoted text clipped - 124 lines]

Err.Clear
End Function

--
Bill Reed

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

Message posted via AccessMonster.com

 

[ragtopcaddy via AccessMonster.com]

PS:

The shell function will open the application (assuming it's in the same
folder on every user PC) but not the file. How do I implement that?

Thanks,

Bill R

"Barada Nikto"

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 spreadsheet would disappear. If you want the user to
see the spreadsheet after you have completed it, the better way is to save
it, close, quit Excel, then use the Shell function to open it.

The code below runs at the end of a sub that exports queries via a
transferspreadsheet routine to a new workbook which then has to be formatted,

[quoted text clipped - 124 lines]

Err.Clear
End Function

--
Bill Reed

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

Message posted via AccessMonster.com

 

[RoyVidar]

Many thanks (yet again!) to you and Roy,

I've implemented your suggestions, but I'm still having difficulty getting
rid of the Excel process. The Do While loop below is an endless loop. Could
you tell me what's missing?

The following code runs at the end of my StripXLFormats function:

Do While blXLRunning
MsgBox "Excel is still running!!!"
Stop
Set xlObj = GetObject(, "Excel.Application")
xlObj.Quit
Set xlObj = Nothing
blXLRunning = IsExcelRunning
Loop

End Function

The IsExcelRunning function code is below:

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

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 spreadsheet would disappear. If you want the user to
see the spreadsheet after you have completed it, the better way is to save
it, close, quit Excel, then use the Shell function to open it.

The code below runs at the end of a sub that exports queries via a
transferspreadsheet routine to a new workbook which then has to be
formatted, [quoted text clipped - 124 lines] Err.Clear
End Function

My view is to focus on *not* getting an extra instance of excel in the
first place - i e work with the cause of it - not trying to get rid of
it afterwards, which is more like dealing with the symptoms. The link I
gave, should give some hints on that.

For me to be of further assistance, I think I'd like to see the current
code. See also if it makes any difference instantiating with
createobject in stead of with the New keyword, and leave off all that
IsExcelRunning stuff. For instance something along the lines of the
following air code:

on error resume next
Set xlObj = GetObject(, "Excel.Application")
if err.number <> 0 then
' ouch - no instance open, then create one
err.clear
Set xlObj = createobject("Excel.Application")
if err.number <> 0 then
' double-ouch - is excel installed???
exit sub
end if
end if
on error goto <your error handler>

You might perhaps drop in a DoEvents after saving/closing the workbook,
and also after quiting excel?

 

[ragtopcaddy via AccessMonster.com]

Roy,

I did replace all of those "Excel" objects with instantiated(if that's the
correct word) objects, such as xlObj.Cells, etcs. That helped considerably.

The scenario is, I have a procedure, cmdExport_Click, that uses the
TransferSpreadsheet method to create several worksheets in a single workbook.
The familiar text formatting ("'" prefix character) problem then necessitates
a formatting function, "StripXLFormatting" that needs to open the workbook to
reformat the cells. At the end of that function, despite repeated attempts to
close the xlObj, IsExcelRunning returns True in an endless loop.

If I step out of the Do While blXLRunning loop to End Function and return to
the cmdExport_Click sub and test again, IsExcelRunning now returns False!

I am following Klaatu's recommendation and using the Shell function (which I
figured out, incidentally) in place of an Excel object to open the workbook
for the user at the end of the cmdExport_Click sub.

So, you see, I've just got this one last wrinkle to deal with.

My intention is to continue using IsExcelRunning to track down the problem.
Once I've remedied the problem, I have no intention of continuing the use of
IsExcelRunning in the final application version.

Many thanks for your very practical suggestions,

Bill R

Many thanks (yet again!) to you and Roy,

[quoted text clipped - 57 lines]

formatted, [quoted text clipped - 124 lines] Err.Clear
End Function

My view is to focus on *not* getting an extra instance of excel in the
first place - i e work with the cause of it - not trying to get rid of
it afterwards, which is more like dealing with the symptoms. The link I
gave, should give some hints on that.

For me to be of further assistance, I think I'd like to see the current
code. See also if it makes any difference instantiating with
createobject in stead of with the New keyword, and leave off all that
IsExcelRunning stuff. For instance something along the lines of the
following air code:

on error resume next
Set xlObj = GetObject(, "Excel.Application")
if err.number <> 0 then
' ouch - no instance open, then create one
err.clear
Set xlObj = createobject("Excel.Application")
if err.number <> 0 then
' double-ouch - is excel installed???
exit sub
end if
end if
on error goto <your error handler>

You might perhaps drop in a DoEvents after saving/closing the workbook,
and also after quiting excel?

 

[ragtopcaddy via AccessMonster.com]

Roy,

Inasmuch as Excel DOES stop running at the end of the StripXLFormats
procedure, I will abandon my attempts to understand why it loops endlessly
before the function ends (for now).

However, now that I have everything running just fine, Shell function and all,
I hate it!

One of the sheets that is getting formatted for a currency type ("$#,##0.
0###") consists of a matrix having in the neighborhood of 170X170 cells. This
is taking forever. Although I've gained a lot of knowledge perfecting these
procedures, I don't know that my users will sit still for a formatting
process that may take several minutes.

In your opinion, would it be respectable to export at least this one
worksheet as a text file, convert it to CSV, open it in Excel, save it as an
Excel workbook/worksheet, and finally Move the thing over to the workbook?

Thanks,

Bill

Many thanks (yet again!) to you and Roy,

[quoted text clipped - 57 lines]

formatted, [quoted text clipped - 124 lines] Err.Clear
End Function

My view is to focus on *not* getting an extra instance of excel in the
first place - i e work with the cause of it - not trying to get rid of
it afterwards, which is more like dealing with the symptoms. The link I
gave, should give some hints on that.

For me to be of further assistance, I think I'd like to see the current
code. See also if it makes any difference instantiating with
createobject in stead of with the New keyword, and leave off all that
IsExcelRunning stuff. For instance something along the lines of the
following air code:

on error resume next
Set xlObj = GetObject(, "Excel.Application")
if err.number <> 0 then
' ouch - no instance open, then create one
err.clear
Set xlObj = createobject("Excel.Application")
if err.number <> 0 then
' double-ouch - is excel installed???
exit sub
end if
end if
on error goto <your error handler>

You might perhaps drop in a DoEvents after saving/closing the workbook,
and also after quiting excel?

 

[RoyVidar]

Roy,

Inasmuch as Excel DOES stop running at the end of the StripXLFormats
procedure, I will abandon my attempts to understand why it loops endlessly
before the function ends (for now).

However, now that I have everything running just fine, Shell function and
all, I hate it!

One of the sheets that is getting formatted for a currency type ("$#,##0.
0###") consists of a matrix having in the neighborhood of 170X170 cells. This
is taking forever. Although I've gained a lot of knowledge perfecting these
procedures, I don't know that my users will sit still for a formatting
process that may take several minutes.

In your opinion, would it be respectable to export at least this one
worksheet as a text file, convert it to CSV, open it in Excel, save it as an
Excel workbook/worksheet, and finally Move the thing over to the workbook?

Thanks,

Bill

This is something I don't like answering ;-)

Usually, I'll let requirements and user feedback determine what I do.

Sometimes it's OK for users to fire off a lengthy process when they
leave for lunch ;-) sometimes speed is the essence

I suggest you ask the users, and see what they say, or see if someone
else suggests anything.

 

[ragtopcaddy via AccessMonster.com]

Of course I meant to do all that in code, if feasible.

Roy,

[quoted text clipped - 18 lines]

Bill

This is something I don't like answering ;-)

Usually, I'll let requirements and user feedback determine what I do.

Sometimes it's OK for users to fire off a lengthy process when they
leave for lunch ;-) sometimes speed is the essence

I suggest you ask the users, and see what they say, or see if someone
else suggests anything.

--
Bill Reed

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

Message posted via AccessMonster.com