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

Stuart,

Thank You!  Thank You!  Thank You!

I owe you a beer!

The Unqualified References were the problem.

I am just starting to dabble with Access/Excel Automation.  I am
learning by trial and error (mostly error).

I really appreciate the help.  I have spent most of the day wrestling
with this problem.

Sincerely,
Brad Marks





---Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Thursday, July 14, 2011 3:17 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
>


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