Excel automation in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following Sub in Access... It works, but I know that a reference
is wrong. Can someone help? I get "Error 1004- Method 'Range' of object
'_Application' failed."

Public Sub FormatLevel2(ByRef xlApp As Excel.Application)
On Error GoTo Err_FormatLevel2

Dim xlc As Object

With xlApp

.Range("A1").Select
.Range(.Selection, .Selection.End(xlToRight)).Select
.Range(Selection, .Selection.End(xlDown)).Select
Set xlc = .Selection
For Each xlc In .Selection
If xlc.Interior.ColorIndex = 6 Then
xlc.AddComment "This is the earliest task plotted for week
specified."
End If
Next xlc

End With

Set xlc = Nothing


Exit_FormatLevel2:
Exit Sub

Err_FormatLevel2:
Call ErrHandler("FormatLevel2 routine", Err.Number, Err.Description)
Resume Exit_FormatLevel2
End Sub
 
xlApp is the EXCEL application. It has no Range object. You need to tell
your code which workbook and which worksheet is to be used; then use the
Range object of the worksheet.

You set xlc equal to the EXCEL.Application.Selection and then use xlc in the
For Each loop for the EXCEL.Application.Selection. This makes no sense. I
believe that Selection is an object of the worksheet?
 
I am telling my code the worksheet to use. The below sub is being called
from that procedure. So the worksheet is already the active sheet. All I
want is to add a comment to any cell on the worksheet that is yellow.
 
You're trying to use the EXCEL VBA instrinsic constants in your code. ACCESS
VBA does not know what those values are unless you've set a reference to the
EXCEL library in the datbase, which you obviously (and correctly) have not
done.

Change
xlToRight
to
-4161


Change
xlDown
to
-4121

in the code.
--

Ken Snell
<MS ACCESS MVP>
 
Back
Top