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

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


"Kevin Burton" <(E-Mail Removed)> wrote in message
news:F8AD63CF-C4C6-4275-9CD6-(E-Mail Removed)...
> 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
>
>