Excel Automation Error

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

Guest

I am getting an error: "xlApp.Range("A5", Cells(lastrow...= <Object variable
or With block variable not set>" on this line:

xlApp.ActiveSheet.PageSetup.PrintArea = xlApp.Range("A5", Cells(lastrow,
intColNo)).Address

in the following block of code, which is trying to set the print area of a
worksheet:

intColNo = 5 + ((rsPGrpDetail.Fields.Count) - 7)
Dim lastrow
With xlApp.ActiveSheet
lastrow = .Range("A65536").End(xlUp).Row
End With

''macro4 page setup
With xlApp.ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
End With
xlApp.ActiveSheet.PageSetup.PrintArea = xlApp.Range("A5",
Cells(lastrow, intColNo)).Address

**** there is more, but this is the snipet of the block im havin problems
with. I dont understand what is missing. "xlApp" represents the excel
application object.

***any help is greatly appreciated
 
faberk said:
I am getting an error: "xlApp.Range("A5", Cells(lastrow...= <Object
variable or With block variable not set>" on this line:

xlApp.ActiveSheet.PageSetup.PrintArea = xlApp.Range("A5",
Cells(lastrow, intColNo)).Address

in the following block of code, which is trying to set the print area
of a worksheet:

intColNo = 5 + ((rsPGrpDetail.Fields.Count) - 7)
Dim lastrow
With xlApp.ActiveSheet
lastrow = .Range("A65536").End(xlUp).Row
End With

''macro4 page setup
With xlApp.ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
End With
xlApp.ActiveSheet.PageSetup.PrintArea = xlApp.Range("A5",
Cells(lastrow, intColNo)).Address

**** there is more, but this is the snipet of the block im havin
problems with. I dont understand what is missing. "xlApp"
represents the excel application object.

***any help is greatly appreciated
Click to expand...

I think the problem is your unqualified referencing of the Excel Cell
object, but there might be other.

Perhaps something like the below will work?

xlApp.ActiveSheet.PageSetup.PrintArea = xlApp.Range("A5", _
xlApp.ActiveSheet.Cells(lastrow, intColNo)).Address

I would recommend that you look through the rest of your code, too,
to see if you can identify more such unqualified of Excel objects,
properties or methods.

Some info here
http://support.microsoft.com/default.aspx?kbid=178510

I'm also a fan of declaring and instantiating separate objects for
each "level" I'm working with, i e Workbook and WorkSheet.
 
Range("A5", Cells(lastrow, intColNo)).Address - the .address should be inside
the bracket to refer to the adress of the cell not the adress of the range.
Otherwise you using the value of the cell not it's adress.

try:

xlApp.ActiveSheet.PageSetup.PrintArea = _
xlApp.Range("A5", Cells(lastrow, intColNo).Address)
 
could also try:

xlApp.ActiveSheet.PageSetup.PrintArea = _
"A5:" & Cells(lastrow, intColNo).Address
 
Back
Top