I am trying to extract data out of Access & then copy that data on an
excel sheet to create a pivot table. My code will work on every OTHER
try, but on the even attempts I get a run-time error 91 (object or
variable not set). Here is one small section of the code that includes
the problem area:

'Create Pivot Table
'*****
Dim xlWb As Object
Dim xlWs As Object
Dim xlAp As Object


Set xlWb = Excel.Application.ActiveWorkbook
******Set xlWs = xlWb.Worksheets("Sheet3")********* THIS IS THE PROBLEM
STMT

a = xlWs.Range("A1").Address
lastcell = xlWs.Range("A1").SpecialCells(xlCellTypeLastCell). Address
MyRange = xlWs.Name & "!" & a & ":" & lastcell

With xlWb
'Add Table & cross fingers
.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=MyRange) _
.CreatePivotTable TableDestination:="", TableName:="PivotTable1"
End With

.....I actually set each of these variables (xlWb, xlWs, etc.) to
NOTHING at the end of my code (to address late binding issues). Any
ideas why that statement is creating my problem on every other
attempt????

I would guess the second time around, Excel is closed or there is
no active workbook.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"dailem"
wrote in message
I am trying to extract data out of Access & then copy that data on an
excel sheet to create a pivot table. My code will work on every OTHER
try, but on the even attempts I get a run-time error 91 (object or
variable not set). Here is one small section of the code that includes
the problem area:

'Create Pivot Table
'*****
Dim xlWb As Object
Dim xlWs As Object
Dim xlAp As Object

Set xlWb = Excel.Application.ActiveWorkbook
******Set xlWs = xlWb.Worksheets("Sheet3")********* THIS IS THE PROBLEM
STMT

a = xlWs.Range("A1").Address
lastcell = xlWs.Range("A1").SpecialCells(xlCellTypeLastCell). Address
MyRange = xlWs.Name & "!" & a & ":" & lastcell

With xlWb
'Add Table & cross fingers
.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=MyRange) _
.CreatePivotTable TableDestination:="", TableName:="PivotTable1"
End With

.....I actually set each of these variables (xlWb, xlWs, etc.) to
NOTHING at the end of my code (to address late binding issues). Any
ideas why that statement is creating my problem on every other
attempt????