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

Brad Marks BradM at blackforestltd.com
Thu Jul 14 07:27:54 CDT 2011


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

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




More information about the AccessD mailing list