Referencing an Excel Range in Access cause the Excel process to not terminate

[AnExpertNovice]

I have weeded the code down to near minimal to demonstrate the problem.

Wanted:
To have Access Create an Excel Workbook, save it, (preferably not have to
close the Excel workbook, but that is a later issue), and have the Excel
process terminate without having to close the Access database.

The problem:
If a range is referenced in Access the Excel process does not terminate
until Access is terminated.

Demonstrating the problem:
The code below is a complete and self contained module. A very simple
module.
Executing it at is works fine. (It creates a Book1.xls in the default Excel
folder.)

In the second module are these 4 commented lines.
' Selection.Value = "test"
' With .Range(Cells(1, 1), Cells(1, 1))
'' .value = "test"
' End With
Uncommenting the line: Selection.Value = "test"
causes the Excel application to not terminate until Access is closed.

Uncommenting the two following lines (note, not even assigning a value)
' With .Range(Cells(1, 1), Cells(1, 1))
' End With
causes the Excel application to not terminate until Access is closed.

Note: I have not yet tried defining a range object and saying rngCurr =
..Range(Cells(1, 1), Cells(1, 1))
and then at some point setting rngCurr = Nothing.


============ The code is below


Option Compare Database
Option Explicit
Dim gbooAbort As Boolean

Sub test()
On Error GoTo ErrorRoutine
Dim xlApp As Excel.Application
Dim xlWkbk As Excel.Workbook

gbooAbort = False

'Excel has no process running per Taskmanager
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
xlApp.Visible = True
Set xlWkbk = xlApp.Workbooks(1)

Call test2(CLng(12345), xlApp, xlWkbk)
If gbooAbort Then
GoTo ExitRoutine
End If

xlWkbk.Save
ExitRoutine:
On Error Resume Next
xlWkbk.Close
Set xlWkbk = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub
ErrorRoutine:
MsgBox Err.Number & ": " & Err.Description
gbooAbort = True
Resume ExitRoutine
End Sub


Private Sub test2(lngValue As Long, xlApp As Excel.Application, xlWkbk As
Excel.Workbook)
On Error GoTo ErrorRoutine

'Name the worksheet
xlApp.ActiveSheet.Name = CStr(lngValue)

With xlApp.ActiveSheet
.Range("A2").Select
' Selection.Value = "test"
' With .Range(Cells(1, 1), Cells(1, 1))
'' .value = "test"
' End With
End With
ExitRoutine:
On Error Resume Next
Exit Sub
ErrorRoutine:
MsgBox Err.Number & ": " & Err.Description
gbooAbort = True
Resume ExitRoutine
End Sub

 

[TC]

Uncommenting the line: Selection.Value = "test"

causes the Excel application to not terminate until Access is closed.

You need the dot before Selection (.Selection). Otherwise, the Selection
keyword is not fully qualified to the relevant Excel object. (in fact, I'm
surprised that code would work at all.) Any reference to an automated
program object, that is not fully qualified, can cause the automated program
to not go away. Double check that *every* reference to *every* Excel object
(including parameter items) is properly qualified to the relevent Excel
object.

Uncommenting the two following lines (note, not even assigning a value)
' With .Range(Cells(1, 1), Cells(1, 1))
' End With
causes the Excel application to not terminate until Access is closed.

Is Cells a property of the Range object? Or just of the Selection object? I
know there are problems using the Range or Cells object (not sure which)
against an inappropriate parent object. Carefully check the Excel VBA help
for the Range object, & the Cells object, and make sure that you are only
using each object against an item in the Applies To lst for that object.
Taht will be your problem, here.

HTH,
TC

 

[AnExpertNovice]

Any reference to an automated

program object, that is not fully qualified, can cause the automated program
to not go away.

Thanks, I will try a different format of the range object and try to be
careful about fully qualifiying all objects. The corrected version will be
posted to help others.

Thanks.



FYI: From Excel's help:

The example uses Syntax 2 of the Range property.

Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)). _
Font.Italic = True

 

[Tim Ferguson]

FYI: From Excel's help:

The example uses Syntax 2 of the Range property.

Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)). _
Font.Italic = True

There is another potential problem here, as the calls to the Cells method
use the implicit Excel.Application object, and will trigger the same
unhandled reference problem. You need the full syntax

Set rngMyRange = _
objXL.Worksheets("Sheet1").Range( _
objXL.Worksheets("Sheet1").Cells(1,1), _
objXL.Worksheets("Sheet1").Cells(5,3))

The advantage of the With operator is obvious... <g>

HTH


Tim F

 

[AnExpertNovice]

TC,

Thanks. That was the problem and you taught me a lot. It is amazing that
after the years I have worked with Excel that my Cells reference was in
improper form. This might account for those minor random problems in some
of my Excel code, all of which will be revisited now. <sigh>

This code works as anticipated.
With xlApp.ActiveSheet
.Range("A2").Select
With .Range(.Cells(1, 1), .Cells(1, 1))
.Value = "test"
End With
End With

 

[AnExpertNovice]

Absolutely. With statements not only make some code more readable but
speeds up the process.

As you have probably read now I thought my syntax was correct. Per
Microsoft's help, which is NEVER wrong. Right?

This example sets the font style in cells A1:C5 on Sheet1 to italic. The
example uses Syntax 2 of the Range property.

Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)). _
Font.Italic = True
It is obviously my lack of understanding rather than Microsoft being
unclear. However, notice that Cells is not qualified and with the comment
that this was special syntax of the Range property I never thought to
question it's accuracy.Thanks for the reply.

 

[TC]

There is another potential problem here, as the calls to the Cells method
use the implicit Excel.Application object, and will trigger the same
unhandled reference problem. You need the full syntax

Set rngMyRange = _
objXL.Worksheets("Sheet1").Range( _
objXL.Worksheets("Sheet1").Cells(1,1), _
objXL.Worksheets("Sheet1").Cells(5,3))

Exactly. That is what I was getting at with my comments about
not-fully-qualified references. But I did not notice the Cells() problems,
though it was staring me in the face.

TC

 

[TC]

Well done.
TC

TC,

Thanks. That was the problem and you taught me a lot. It is amazing that
after the years I have worked with Excel that my Cells reference was in
improper form. This might account for those minor random problems in some
of my Excel code, all of which will be revisited now. <sigh>

This code works as anticipated.
With xlApp.ActiveSheet
.Range("A2").Select
With .Range(.Cells(1, 1), .Cells(1, 1))
.Value = "test"
End With
End With

 

[Tim Ferguson]

As you have probably read now I thought my syntax was correct. Per
Microsoft's help, which is NEVER wrong. Right?

Within Excel, it is correct... well, not so much Wrong as Not Quite
Complete. I hate the syntax and think the Range method should use something
like

object.Range(RowOne As Integer,
ColumnOne As Integer,
RowTwo As Integer,
ColumnTwo As Integer) As Range

which would be unambiguous. It's very interesting when you mix things up
like

Set rng = SomeSheet.Range( _
OtherSheet.Cells(a, b), _
ThirdSheet.Cells(c,d))

!!

Oh well, back to Access...
All the best


Tim F