BUG: Background Colors in PivotTable Do Not Paste Into Excel (316586)


The information in this article applies to:

  • Microsoft Office 2003 Web Components
  • Microsoft Office XP 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 (<TD>).

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 "More Information" 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.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Paste the following into any HTML or text editor and save the file as Pivotcopy.htm:
    <html>

<body>
<OBJECT classid="clsid:<Class Identifier For Pivot Table Control>" id="PTable"></OBJECT>
</body>

<script language="VBScript">

Function Window_OnLoad()
   BuildPivotTable
End Function

Function BuildPivotTable()

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

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

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

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

End Function

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

Function PTable_CommandExecute(Command, Succeeded)
   If Command = "CopyToXL1" Then CopyToXL_1
   If Command = "CopyToXL2" 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, "mso-pattern:auto;", "mso-pattern:auto none;")
   'Save the modified HTML to a file.
   Set oFSO = CreateObject("Scripting.FileSystemObject")
   Set oFile = oFSO.CreateTextFile("c:\MyPivot.htm", True)
   oFile.Write(sHTML)
   oFile.Close
   'Open the HTML file in Excel using Automation.
   Dim oXL
   Set oXL = CreateObject("Excel.Application")
   oXL.Workbooks.Open "C:\MyPivot.htm"
   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, "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 Function

</script>

</html>
    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 <Class Identifier For Pivot Table Control> 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
  2. Start Internet Explorer and browse to Pivotcopy.htm. A Web page that contains a populated, formatted PivotTable is rendered.
  3. 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.
  4. 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.
  5. To test the first workaround, right-click the PivotTable title bar, click Copy, and then click Copy To Excel Using HTML File.
  6. To test the second workaround, right-click the PivotTable title bar, click Copy, and then click Copy To Excel Using Workbook's HTMLProject.

REFERENCES

For more information about the Office Web Components, see the following Microsoft Developer Network (MSDN) Web site:

Microsoft Office Development with Visual Studio
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/vsofficedev.asp

Modification Type:MajorLast Reviewed:4/27/2006
Keywords:kbbug kbOfficeWebPivot kbpending KB316586
©2004 Microsoft Corporation. All rights reserved.