Enable or disable the property "send to Excel" of a pivot table with a macro

[#1]

I should enable or disable the property "send to Excel" of a pivot table with a macro.

Can anyone tell me if it is possible via VB and how to set this property.

Thanks.

[#2]

It looks like there is a typo in the API Guide, but it can be done on a chart using:

Sub HideXL
 set mybox = ActiveDocument.GetSheetObject("CH01")
 mbp = mybox.GetProperties
 mbp.GraphLayout.Frame.XLIcon = false
 mybox.SetProperties mbp
End Sub

I think other objects would use:

Sub HideXL
 set mybox = ActiveDocument.GetSheetObject("LB01")
 mbp = mybox.GetProperties
 mbp.Layout.Frame.XLIcon = false
 mybox.SetProperties mbp
End Sub

That disables them, set XLIcon to true to show the icon.

[#3]

Thanks, it works very well

[Anonymous]

Hi all, this is my first post (actually it's a question). I tried the solution to hiding excel icons and got it to work. However I want to hide / show ALL excel icons from all objects with a button. Is there a quick way to do this? I don't want to create dozens of buttons or a really long script. Many thanks in Advance.

[#5]

Hi David,

a simple way is to use a loop on ojects:

function DisableSendToExcel

for i=1 to 3

set mybox = ActiveDocument.GetSheetObject("CH"&i)

mbp = mybox.GetProperties

mbp.GraphLayout.Frame.XLIcon = false

mybox.SetProperties mbp

next

end function 'DisableSendToExcel

... Better would be to automatically assign IDs

[Anonymous]

I thought it must be something simple. I'll give it a go - many thanks. Really appreciated.