Attn: Alastair Pivot Table Pain!

[Guest]

Hello

On 2/22/04 you posted the comments below which pinpointed my problem; I was creating a second instance of Excel causing an error message. I've got the advantage of being able to check the task manager for such things. Now that I know what the problem is how do I solve it

Thanks for your hel

Set xll = CreateObject("Excel.application"

xll.Workbooks.Open "C:\testme
With xl
.Visible = Tru
Sheets(1).Name = "Data
Set PTCache = .ActiveWorkbook.PivotCaches.Add(xlDatabase, .Sheets("data").Range("A1").CurrentRegion.Address
Sheets.Ad
Sheets(1).Activat
Set PT = PTCache.CreatePivotTable(Tabledestination:=Workbooks("testme.xls").Sheets(1).Range("a3"), TableName:="Pivot Table"
ActiveWorkbook.ActiveSheet.PivotTables("Pivot Table").addfields rowfields:=Array("Month Created"),
Pagefields:=Array("Supname", "Supnum", "category"

With .ActiveWorkbook.ActiveSheet.PivotTables("Pivot Table").PivotFields("DTR Number"
.Orientation = xlDataFiel
.Caption = "DTR
.Position =
End Wit
With .ActiveWorkbook.ActiveSheet.PivotTables("Pivot Table").PivotFields("Category"
.Orientation = xlColumnFiel
.Position =
End Wit

End Wit

Subject: Re: Pivot Table Pain! 2/22/2004 7:59 AM PST

By: Alastair (search by author) In: microsoft.public.excel.programming

I thought that a possible problem could've been that an instance of exce
would remain in memory and somehow the caches of the two instances of Exce
could error. Unfortunately I am accessing excel on a remote pc and can't se
the instances of excel stored on the pc

Thanks again

Alastai

 

[Guest]

Sorry - that should be xll.Quit, not Exit (brain cramp)

 

[Tom Ogilvy]

Set xll = CreateObject("Excel.application")

start a new version of excel.

If you are writing this from within Excel, don't use createObject, GetObject
or New.

--
Regards,
Tom Ogilvy

Hello,

On 2/22/04 you posted the comments below which pinpointed my problem; I

was creating a second instance of Excel causing an error message. I've got
the advantage of being able to check the task manager for such things. Now
that I know what the problem is how do I solve it?

Thanks for your help

Set xll = CreateObject("Excel.application")

xll.Workbooks.Open "C:\testme"
With xll
.Visible = True
Sheets(1).Name = "Data"
Set PTCache = .ActiveWorkbook.PivotCaches.Add(xlDatabase, ..Sheets("data").Range("A1").CurrentRegion.Address)
Sheets.Add
Sheets(1).Activate
Set PT =

PTCache.CreatePivotTable(Tabledestination:=Workbooks("testme.xls").Sheets(1)
..Range("a3"), TableName:="Pivot Table")

ActiveWorkbook.ActiveSheet.PivotTables("Pivot Table").addfields

rowfields:=Array("Month Created"), _

Pagefields:=Array("Supname", "Supnum", "category")

With .ActiveWorkbook.ActiveSheet.PivotTables("Pivot

Table").PivotFields("DTR Number")

 

[Guest]

Thanks for all your inpu

Actually, I'm controlling Excel via Access. I'm using Access to merge data from several sources (excel, lotus notes and a text file). The merged (joined) data is then exported to excel and reports (pivot tables, graphs) are created. I am using Excel for reports as it is easier for user to manipulate later (pivot tables vs. crosstabs)

My code works every other time. The second time the second instance of Excel is still open, causing the aforementioned error message. I'd like Access to create the excel file and leave it open if this is possible

Thank

Sid