excel automation question

[apgoodb]

I have code that formats an excel report. Most of the time this code
works; however, sometimes it bombs out at the statement:
With XLApp.ActiveSheet.Range(rangedef)
.Select
ActiveSheet.Paste
.Copy
.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
skipblanks:=False, Transpose:=False
End With

With the message 'Object variable or With block variable not set
(Error 91)'

Why would this work sometimes but fail others? Any ideas?

Thanks,

[Mike MacSween]

Looks to me like the variable that's missing sometimes is XLApp. Is that
always declared and set? What's the complete procedure? Is there a chance
that somewhere higher up it isn't getting set?

Mike

"apgoodb" <ap*****@yahoo.com> wrote in message
news:86**************************@posting.google.c om...

I have code that formats an excel report. Most of the time this code
works; however, sometimes it bombs out at the statement:
With XLApp.ActiveSheet.Range(rangedef)
.Select
ActiveSheet.Paste
.Copy
.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
skipblanks:=False, Transpose:=False
End With

With the message 'Object variable or With block variable not set
(Error 91)'

Why would this work sometimes but fail others? Any ideas?

Thanks,

[DCM Fan]

It needs a dot in front of ActiveSheet, that's for sure. Also, you better have
a reference to the prevailing Excel Object Model if you're going to use named
arguments.

[Matthew Sullivan]

In addition to the stuff the other guys suggested, objects can lose
their values when unhandled errors occur.

One other thing: I've seen LOTS of problems pop up when people use
that ".Select" or ".Copy" syntax when automating Excel from Access.
You need to *always* reference objects explicitly. Something like
(air code):

Set xlRange = xlApp.ActiveSheet.Range(rangedef)
xlRange.Select
xlApp.ActiveSheet.Paste
etc....

-Matt
On 2 Apr 2004 10:21:50 -0800, ap*****@yahoo.com (apgoodb) wrote:

I have code that formats an excel report. Most of the time this code
works; however, sometimes it bombs out at the statement:
With XLApp.ActiveSheet.Range(rangedef)
.Select
ActiveSheet.Paste
.Copy
.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
skipblanks:=False, Transpose:=False
End With

With the message 'Object variable or With block variable not set
(Error 91)'

Why would this work sometimes but fail others? Any ideas?

Thanks,