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

Search the archives for "unquailfied reference" I must have posted the same answer about 
half a dozen times.

<standard quote>The old "unqualified reference" strikes again.
See http://support.microsoft.com/kb/319832
When you write code to use an Excel object, method, or property,
you should always precede the call with the appropriate object variable.
If you do not, Visual Basic establishes its own reference to Excel...
</ standard quote>

These lines for a start are unqualfied:
Sheets("Sheet1").Select
Cells(9, 1).Select

--
Stuart

> -----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
>