BUG:ForeColor do not Paste into Excel

[Reinfried Englmair]

With
http://support.microsoft.com/default.aspx?scid=kb;en-
us;316586
there is an example for background-color in Office XP
PivotTable:
Sub CopyToXL_1()
Dim sHTML, oXL, oBook
'Replace the mso-pattern style in the HTML returned
from
the PivotTable.
sHTML = ptable.HTMLData
sHTML = Replace (sHTML, "mso-pattern:auto;", "mso-
pattern:auto none;")
'Open the HTML file in Excel using Automation.
Set oXL = CreateObject("Excel.Application")
Set oBook = oXL.Workbooks.Add
oBook.HTMLProject.HTMLProjectItems("sheet1").Text =
sHTML
oBook.HTMLProject.Refreshdocument
oXL.Visible = True
oXL.UserControl = True
End Sub

But:
The ForeColor and BackColor of calculated members
do NOT paste into Excel.

Is this a bug ??????? or have anywhere an idea for an
workaround?

 

[Sandro]

In excel 2002 use this,

Sub Autpen()
Application.CommandBars.Add(Name:="AS Format").Visible =
True
Application.CommandBars("AS Format").Controls.Add
Type:=msoControlButton, ID _
:=283, Before:=1
Application.CommandBars("AS Format").Controls
(1).OnAction = "Formata"
Application.CommandBars("AS Format").Controls
(1).Caption = "Pega Formatos do Cubo OLAP"
Application.CommandBars("AS Format").Controls(1).Style
= msoButtonCaption
End Sub
Sub Auto_Close()
Application.CommandBars("AS Format").Delete
End Sub
Sub Formata()

Set wkbk = ActiveWorkbook
Set wksht = wkbk.ActiveSheet
wksht.PivotTables(1).PivotSelect "", xlDataAndLabel, True

wksht.PivotTables(1).HasAutoFormat = False
wksht.PivotTables(1).MergeLabels = True
wksht.PivotTables(1).ShowCellBackgroundFromOLAP = True

'Call AutoFit over the used range to make sure
'everything is visible.
'wksht.UsedRange.Columns.AutoFit
'wksht.PivotTables(1).PivotSelect "", xlDataOnly, True
wksht.UsedRange.ColumnWidth = 12
wksht.Range("a1").Select

'Get the page setup object
Set pgsetup = wksht.PageSetup

'Also tell it to print grid lines
pgsetup.PrintGridlines = True

End Sub

 

[Reinfried Englmair]

Thank's for the solution,but I don't copy the OWC-
Pivottable to a Excel-Pivottable. I copy the OWC-
Pivottable as an HTML-File ( sHTML = ptable.HTMLData,
look to my Sub CopyToXL_1) to Excel for printing.