excel automation question

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,

re: excel automation question

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" <apgoodb@yahoo.com> wrote in message
news:868c8f89.0404021021.198eec45@posting.google.c om...[color=blue]
> 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,[/color]

re: excel automation question

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.

re: excel automation question

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, apgoodb@yahoo.com (apgoodb) wrote:
[color=blue]
>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,[/color]