470,647 Members | 1,248 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

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,
Nov 12 '05 #1
3 1470
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,

Nov 12 '05 #2
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.
Nov 12 '05 #3
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,


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.