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

Arthur and Gustav,

Thanks for the help, I appreciate it.

I made the refinements that Gustav suggested, but I am still receiving
the "91" Error.

I also tried many other things but have not had success.

Perhaps someone has an simple example of how to create an Excel Pivot
table using "Automation" via Access VBA code.  (and be able to execute
the logic more than once without getting the 91 error.)

Again, if I get out of Access and then back in again, everything works
nicely.

Thanks,
Brad

      

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Thursday, July 14, 2011 7:28 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Generating an Excel Pivot Table from Access -
Problemwith Second Execution

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

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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.