[AccessD] Generating an Excel Pivot Table from Access - Problem with Second Execution

Hi Brad

Without going deeper into your code, in Excel always be extremely careful and strict with properties, methods, and objects.
Here you mix Sheet for Worksheet which is a no-no, and you use Select where you could use the much faster Activate.
Also, objects should be closed if possible before setting them to Nothing.

/gustav


>>> BradM at blackforestltd.com 14-07-2011 14:27 >>>
All,

I am just starting to experiment with Access/Excel Automation

Through a process of trial and error I have developed  a small test
Access application that is able to build a simple Excel Pivot Table.
Building and viewing the Pivot Table works nicely the first time I
execute the Access VBA code.  If I exit Access and get back in again,
things work nicely.

However, when I try to execute the code more than one time (without
getting out of Access), I receive Error 91 - "Object Variable or With
block variable Not Set".

I have tried many things but I cannot figure out how to prevent this
error.  I must be missing something.  

Below is the code that I am using.  Thanks for your help with this.

Brad

~~~~~~~~~~~~~ 
Dim XLApp As Excel.Application
Dim XLWorkbook As Excel.Workbook
Dim XLSheet As Excel.Worksheet

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

Set XLWorkbook = XLApp.Workbooks.Add

Set XLSheet = XLWorkbook.Sheets(1)

XLSheet.Cells(1, 1).Value = "State"
XLSheet.Cells(2, 1).Value = "MN"
XLSheet.Cells(3, 1).Value = "WI"
XLSheet.Cells(4, 1).Value = "SD"
XLSheet.Cells(5, 1).Value = "ND"

XLSheet.Cells(1, 2).Value = "Sales_Amt"
XLSheet.Cells(2, 2).Value = "200"
XLSheet.Cells(3, 2).Value = "300"
XLSheet.Cells(4, 2).Value = "400"
XLSheet.Cells(5, 2).Value = "500"

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R5C2",
Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet1!R9C1", TableName:="PivotTable3",
DefaultVersion _
        :=xlPivotTableVersion12
    
Sheets("Sheet1").Select
    
Cells(9, 1).Select

ActiveSheet.PivotTables("PivotTable3").PivotFields("State").Orientation
= xlRowField

ActiveSheet.PivotTables("PivotTable3").PivotFields("State").Position = 1

ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
      "PivotTable3").PivotFields("Sales_Amt"), "Sum of Sales_Amt", xlSum

Set XLApp = Nothing
Set XLWorkbook = Nothing
Set XLSheet = Nothing


~~~~~~~~~~~~~  End of Code ~ ~~~~~~~~~



The Error 91 is issued on this statement on the "Second Execution"

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R5C2",
Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet1!R9C1", TableName:="PivotTable3",
DefaultVersion _
        :=xlPivotTableVersion12

~~~~~~~~~~~~