Creating a pivot table in Excel

[crowegreg]

Although this code works, its leaving an instance of Excel open. What am I doing wrong?

Dim xlApp As New Excel.Application


Dim wb1 As Workbook

Set xlApp = New Excel.Application
Set wb1 = xlApp.Workbooks.Open("c:\chi\sla.xlsx")

wb1.Sheets.Add
wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Qry1!R1C1:R2539C5", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

wb1.Sheets("Sheet1").Select
wb1.ActiveSheet.Cells(3, 1).Select
wb1.ActiveSheet.PivotTables("PivotTable1").AddData Field ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Tran_Amount"), "Sum of Tran_Amount", xlSum

xlApp.ActiveWorkbook.Close (True)


Set wb1 = Nothing
Set xlApp = Nothing

[rpeare]

did you try wb1.close as well since that's the actual object that has the open file?

[crowegreg]

No, I haven't.

It has something to do with "wb1.ActiveSheet.PivotTables..." statement. If I comment out that line, The code works properly.

[rpeare]

I'd still try the wb1.close and see if that works and possibly issue a refresh command before you try to close it.
I don't know if it will help you either but I've run into some funky things with excel and you may need to save the file then issue the close command as well. I'm not hugely versed in excel functions because I just don't use them that often but I do remember running into a situation similar to this and it was tied to not saving the file or not issuing that workbook.close statement, I honestly don't remember which it was.

[crowegreg]

Thanks for your suggestions. I'll give it a try and see what happens.

[June7]

I have run into this as well. Some manipulations of spreadsheet cause the Excel instance to persist. Even the Close method won't clear it. Don't remember if running Save method first helps.

[crowegreg]

Here's what I got to work:

Dim xlApp As New Excel.Application
Dim wb1 As Workbook

Set xlApp = New Excel.Application
Set wb1 = xlApp.Workbooks.Open("c:\chi\sla.xlsx")

wb1.Sheets.Add
wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Qry1!R1C1:R2539C5", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

wb1.Sheets(1).Select
wb1.Sheets(1).Cells(3, 1).Select
wb1.Sheets(1).PivotTables("PivotTable1").AddData Field wb1.Sheets(1).PivotTables( _
"PivotTable1").PivotFields("Tran_Amount"), "Sum of Tran_Amount", xlSum

xlApp.ActiveWorkbook.Close (True)

xlApp.Quit

Set wb1 = Nothing
Set xlApp = Nothing


Thanks for your assistance!!

[June7]

Check Task Manager. Make sure the Excel instance is truly terminated.