Is there really an ActiveSheet?

--
George Nicholson

Remove 'Junk' from return address.


"Darrell Wesley" wrote in message
...
I have a visual basic application that creates a Excel spreadsheet and
then creates a pivot table from the data found on Sheet1. The first pass
thru the code everything works fine but if I send it thru this section of
code again without closing the application I get an:

Run_Time error '91'

Object variable or with block variable not set

The code for the pivot table creation is as follows:

With ObjExcel
.ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:= _
"Sheet1!R1C1:R" & row1 & "C20").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1"
.ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
.ActiveSheet.Cells(3, 1).Select
With .ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
.SmallGrid = False
End With
.ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Run#", _
"WMW", "WML", "COIL HGT")
.ActiveSheet.PivotTables("PivotTable1").PivotField s("Qty").Orientation
= _
xlDataField

.ActiveSheet.PivotTables("PivotTable1").PivotField s("WML").Subtotals =
Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)

.ActiveSheet.PivotTables("PivotTable1").PivotField s("WMW").Subtotals =
Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)

.ActiveSheet.PivotTables("PivotTable1").PivotField s("Run#").Subtotals
= Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
.ActiveSheet.PivotTables("PivotTable1").PivotField s("Count of
Qty").Function = _
xlSum
.ActiveSheet.PivotTables("PivotTable1").PrintTitle s = True
.ActiveSheet.Name = "BOXES"
End With

The line that gets the error is

.ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

Any ideas as to what is happening?

The code you provided does not contain a loop. Therefore, I assume the 1st
and 2nd pass you refer to are instigated by some other part of your program.
Since this piece of code behaves differently the 2nd time, my guess is that
the status of the workbook or worksheet changes between the 1st call and the
2nd.

If there is no active sheet when this code is called, it will fail on the
line you indicate. There may be another reason for the failure, but that is
the 1st question I would ask myself.

Stated another way: this code depends on the status of the workbook being a
specific way before it is called in order for it to work. I suspect
something about how you "send it thru this section of code again" the 2nd
time differs from the 1st and is the source of the problem. However, I
can't say that for certain.

--
George Nicholson

Remove 'Junk' from return address.


"Darrell Wesley" wrote in message
...
How would I know? Like I said the first pass through everthing works, the
second time around with no changes is when the erro shows up.

I could post the entire routine if helpfull.


----- George Nicholson wrote: -----

Is there really an ActiveSheet?

--
George Nicholson

Remove 'Junk' from return address.


"Darrell Wesley" wrote in
message
...
I have a visual basic application that creates a Excel spreadsheet
and
then creates a pivot table from the data found on Sheet1. The first
pass
thru the code everything works fine but if I send it thru this
section of
code again without closing the application I get an:
Run_Time error '91'
Object variable or with block variable not set
The code for the pivot table creation is as follows:
With ObjExcel
.ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base,
SourceData:= _
"Sheet1!R1C1:R" & row1 & "C20").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1"
.ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3,
1)
.ActiveSheet.Cells(3, 1).Select
With .ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
.SmallGrid = False
End With
.ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Run#", _
"WMW", "WML", "COIL HGT")

..ActiveSheet.PivotTables("PivotTable1").PivotFiel ds("Qty").Orientation
= _
xlDataField

..ActiveSheet.PivotTables("PivotTable1").PivotFiel ds("WML").Subtotals =
Array( _
False, False, False, False, False, False, False, False,
False,
False, False, False)

..ActiveSheet.PivotTables("PivotTable1").PivotFiel ds("WMW").Subtotals =
Array( _
False, False, False, False, False, False, False, False,
False,
False, False, False)

..ActiveSheet.PivotTables("PivotTable1").PivotFiel ds("Run#").Subtotals
= Array( _
False, False, False, False, False, False, False, False,
False,
False, False, False)
.ActiveSheet.PivotTables("PivotTable1").PivotField s("Count of
Qty").Function = _
xlSum
.ActiveSheet.PivotTables("PivotTable1").PrintTitle s = True
.ActiveSheet.Name = "BOXES"
End With
The line that gets the error is
.ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
Any ideas as to what is happening?