By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,101 Members | 971 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,101 IT Pros & Developers. It's quick & easy.

excel automation question

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.