Microsoft KB Archive/316586

= BUG: Background Colors in PivotTable Do Not Paste Into Excel =

Article ID: 316586

Article Last Modified on 6/29/2007

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office Web Components

-



This article was previously published under Q316586



SYMPTOMS
When you paste the contents of a PivotTable component into a Microsoft Excel workbook, background colors in the PivotTable are not transferred to the workbook.



CAUSE
When you copy a PivotTable, an HTML representation of the PivotTable is sent to the clipboard for paste operations. The resulting HTML on the clipboard is missing a default style attribute for cells ().



RESOLUTION
With the HTML for a PivotTable, the default style for a cell is the following:   mso-pattern:auto; However, it should be the following:   mso-pattern:auto none; To correct this problem with the Copy command, you can use script to retrieve the HTMLData property for the PivotTable, modify the style attribute, and then automate Excel to view the modified HTML. To view the HTML, you can use either of the following methods:
 * Save the modified HTML to a file and then open the HTML file in Excel. -or-


 * Manipulate the HTMLProject in the workbook to insert the modified HTML into a worksheet.

Both of these workarounds are illustrated in the &quot;More Information&quot; section.

NOTE: The workarounds that are discussed in this article involve Automation to Excel. For Excel Automation to work in Internet Explorer, your security settings must allow for initialization and scripting of ActiveX controls that are not marked as safe. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

195826 PRB: CreateObject Fails from Client-Side Scripts



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
  Paste the following into any HTML or text editor and save the file as Pivotcopy.htm:

&quot; id=&quot;PTable&quot;>



Function Window_OnLoad BuildPivotTable End Function

Function BuildPivotTable

'Connect to the database and provide the command text for the rowset. PTable.ConnectionString = &quot;Provider = Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source = c:\program files\microsoft office\office10\&quot; & _ &quot;samples\northwind.mdb&quot;

PTable.CommandText = &quot;Select * from [Product Sales for 1997]&quot; Dim oView Set oView = PTable.ActiveView

'Add fields and a total. oView.RowAxis.InsertFieldSet oView.FieldSets(&quot;CategoryName&quot;) oView.RowAxis.InsertFieldSet oView.FieldSets(&quot;ProductName&quot;) oView.DataAxis.InsertFieldSet oView.Fieldsets(&quot;ProductSales&quot;) oView.DataAxis.InsertTotal oView.AddTotal(&quot;Sales Total&quot;, _       oView.FieldSets(&quot;ProductSales&quot;).Fields(0), _        PTable.Constants.plFunctionSum)

'Change colors for the fields and subtotals. oView.TotalBackColor = &quot;LemonChiffon&quot; oView.HeaderBackColor = &quot;Gold&quot; oView.FieldSets(&quot;ProductName&quot;).Fields(0).GroupedBackColor = &quot;PeachPuff&quot; oView.FieldSets(&quot;ProductName&quot;).Fields(0).SubtotalBackColor = &quot;LemonChiffon&quot; oView.FieldSets(&quot;ProductName&quot;).Fields(0).SubtotalLabelBackColor = &quot;PeachPuff&quot; oView.FieldSets(&quot;CategoryName&quot;).Fields(0).GroupedBackColor = &quot;PeachPuff&quot; oView.FieldSets(&quot;CategoryName&quot;).Fields(0).SubtotalBackColor = &quot;Gold&quot; oView.FieldSets(&quot;CategoryName&quot;).Fields(0).SubtotalLabelBackColor = &quot;PeachPuff&quot;

End Function

Function PTable_BeforeContextMenu(x, y, Menu, Cancel) Dim cmMenu(0), cmSubMenu(2) cmSubMenu(0) = Array(&quot;&Copy (default action)&quot;, PTable.Constants.ocCommandCopy) cmSubMenu(1) = Array(&quot;Copy To Excel Using HTML &File&quot;, &quot;CopyToXL1&quot;) cmSubMenu(2) = Array(&quot;Copy to Excel Using Workbook's &HTMLProject&quot;, &quot;CopyToXL2&quot;) cmMenu(0) = Array(&quot;&Copy...&quot;, cmSubMenu) Menu.Value = cmMenu End FUnction

Function PTable_CommandExecute(Command, Succeeded) If Command = &quot;CopyToXL1&quot; Then CopyToXL_1 If Command = &quot;CopyToXL2&quot; Then CopyToXL_2 End Function

Function CopyToXL_1 Dim sHTML, oFSO, oFile 'Replace the mso-pattern style in the HTML returned from the PivotTable. sHTML = PTable.HTMLData sHTML = Replace (sHTML, &quot;mso-pattern:auto;&quot;, &quot;mso-pattern:auto none;&quot;) 'Save the modified HTML to a file. Set oFSO = CreateObject(&quot;Scripting.FileSystemObject&quot;) Set oFile = oFSO.CreateTextFile(&quot;c:\MyPivot.htm&quot;, True) oFile.Write(sHTML) oFile.Close 'Open the HTML file in Excel using Automation. Dim oXL Set oXL = CreateObject(&quot;Excel.Application&quot;) oXL.Workbooks.Open &quot;C:\MyPivot.htm&quot; oXL.Visible = True oXL.UserControl = True End Function

Function CopyToXL_2 Dim sHTML, oXL, oBook 'Replace the mso-pattern style in the HTML returned from the PivotTable. sHTML = PTable.HTMLData sHTML = Replace (sHTML, &quot;mso-pattern:auto;&quot;, &quot;mso-pattern:auto none;&quot;) 'Open the HTML file in Excel using Automation. Set oXL = CreateObject(&quot;Excel.Application&quot;) Set oBook = oXL.Workbooks.Add oBook.HTMLProject.HTMLProjectItems(&quot;Sheet1&quot;).Text = sHTML oBook.HTMLProject.Refreshdocument oXL.Visible = True oXL.UserControl = True End Function

Note The code sample uses Northwind.mdb. If you do did not install Office to the default folder (C:\Program Files\Microsoft Office), modify the connection string in the BuildPivotTable function so that the path to the database matches your Office installation.

Replace the  in the above code with the Class ID of the Pivot Table control being used.

For Microsoft Office PivotTable 10.0

0002E552-0000-0000-C000-000000000046

For Microsoft Office PivotTable 11.0

0002E55A-0000-0000-C000-000000000046  Start Internet Explorer and browse to Pivotcopy.htm. A Web page that contains a populated, formatted PivotTable is rendered.</li> Right-click the PivotTable title bar, click Copy, and then click Copy (default action).

NOTE: For demonstration purposes, the sample code modifies the shortcut menu in the PivotTable.</li> Start a new workbook in Excel and paste the clipboard contents in a worksheet. The PivotTable data is accurately transferred to the workbook; however, the cell background colors are not pasted into the workbook.</li> To test the first workaround, right-click the PivotTable title bar, click Copy, and then click Copy To Excel Using HTML File.</li> To test the second workaround, right-click the PivotTable title bar, click Copy, and then click Copy To Excel Using Workbook's HTMLProject.</li></ol>

<div class="references_section">