Deleting a PivotTable.

[Kevin Burton]

create a pivot table just fine



Application.ActiveWorkbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase,
sourceTableName,

Excel.XlPivotTableVersionList.xlPivotTableVersion12).CreatePivotTable(destinationSheet.Cells[1, 1],

pivotTableName,

missing,


Excel.XlPivotTableVersionList.xlPivotTableVersion12);



but when I try to create another pivot table with the same name I get an
error:


Exception from HRESULT: 0x800A03EC


************** Exception Text **************
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from
HRESULT: 0x800A03EC

Server stack trace:


Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&
msgData, Int32 type)
at Microsoft.Office.Interop.Excel.PivotCache.CreatePivotTable(Object
TableDestination, Object TableName, Object ReadData, Object DefaultVersion)
at OrderHistory.ThisWorkbook.OutOfStockPivotTable(String sourceTableName,
Worksheet destinationSheet, String pivotTableName) in I suspect that I need
to delete the former pivot table before I create a new table with the same
name but I don't see how to delete/remove a pivoit table. Like I said that is
what I "suspect" as being the cause of the error. Any suggestions?

THank you.

Kevin

 

[Patrick Molloy]

run this on the sheet before you create the pivot...pass the worksheet to it
...

sub deleteAny(sh as worksheet)
Dim pt As PivotTable
For Each pt In sh.PivotTables
pt.TableRange2.Delete
Next
end sub