Knowledge base KB0156

I cannot paste data into the think-cell datasheet or the format is lost

Symptoms

You have data in an Excel workbook (.xlsx) and want to copy/paste it into the internal datasheet of a think-cell chart. When you paste the data, you observe one of the following problems:

  • You receive a message: Microsoft Excel cannot paste the data.
  • Only empty cells are pasted.
  • Your date or number format has been lost, e.g., 1/1/2012 turns into 40909 or $3.5 turns into 3.5.
  • After pasting, percentage values are displayed incorrectly. In 100% charts, the chart layout may change.
  • Cell colors do not appear in the chart although you checked the Use Datasheet Fill on Top option.

These problems can also occur when you use think-cell's Excel data links.

Problem 1

Any of the problems listed above can happen if the .xlsx workbook contains more than ~65490 cell styles. You can check the number of cell styles with the following VBA command in the Immediate Window:
print Activeworkbook.Styles.Count

The steps to check this are in detail:

  1. Press Alt+F11 to open the VBA editor.
  2. Go to the Immediate Window. If it is not visible, you can display it by clicking on ViewImmediate Window.
  3. Type the following command and press Enter
    print Activeworkbook.Styles.Count

Solutions 1: Clean up affected workbooks

  • Copy your worksheets into a new workbook

    In Office 2013 and later copying worksheets between workbooks prevents the accumulation of unused cell styles by default:

    • Select all sheets in your workbook: hold SHIFT and click on the first and last worksheet
    • Right-click on the selection: Move or copy…To book: (new book) and tick Create a copyOK
    • Save the new workbook under a new name

    In Office 2007 and 2010 copying a worksheet into a new workbook copies all unused cell styles. To prevent this, please activate the following Hotfix first:

    If your Office version is fully updated, this Hotfix is probably already installed. However, you also must activate this fix either by using the Fix it solution provided by Microsoft or in the registry as explained in the following Microsoft articles:

    If activating the Hotfix does not work, apply the update mentioned in section Resolution in the Microsoft articles above.

  • Clean up your workbooks with a cleanup tool

    Use the cleanup tool recommended by Microsoft in KB213904. Using the XLStyles Tool, click Get Excel File, tick Force all cell styles to 'Normal', then click Process File. When Excel opens with the cleaned up file, save it under a new name.

  • Remove custom cell styles from your workbook using a VBA macro

    Open the VBA editor with Alt+F11. Select your workbook and click on InsertModule. Then paste one of the following macros and run it by pressing F5:

    DeleteCustomStyles() removes all custom cell styles from your workbook.

    Sub DeleteCustomStyles()
    Dim st As Style
    Dim i, i_cust As Long
    i_cust = 0
    For i = (ActiveWorkbook.Styles.Count) To 1 Step -1
    	With ActiveWorkbook.Styles(i)
    		If .BuiltIn = False Then
    			On Error Resume Next
    			.Locked = False
    			On Error GoTo 0
    			If Not .Locked Then
    				.Delete
    				i_cust = i_cust + 1
    			End If
    		End If
    	End With
    	If (i Mod 100) = 0 Then Debug.Print i
    Next
    MsgBox "Styles deleted: " & i_cust & " styles" & Chr(13) & "Styles left: " & ActiveWorkbook.Styles.Count
    End Sub

The best solution would be to prevent the pasting of unused cell styles, but requires Microsoft to fix a bug in Excel 2007 and later. Up to now Microsoft has not considered the business impact high enough to provide this fix. If your company has a Microsoft Office Support contract and you want to contact Microsoft to ask for a fix, you may refer to Microsoft case number 113012810172527, 114021711190574 (Excel 2013).

Explanation 1

The file format used when pasting data into the think-cell datasheet or when transferring it over an Excel data link (Biff12/.xlsb file format) supports ~65490 cell styles. When transferring data from a .xlsx workbook with a greater number of cell styles into the think-cell datasheet, either formatting information is lost, or pasting is not possible.

Some .xlsx workbooks contain a large number (several ten thousands) of cell styles due to bugs and problematic code design in Excel. A contributing factor are also reporting tools that sometimes generate unnecessarily many cell styles. Under certain conditions also all unused cell styles are copied, when copying cells or worksheets between workbooks.

  • Copying a worksheet into a new workbook with Excel 2007 or 2010:
    Right-click on the worksheet tab → Move or copy…To book: (new book) and tick Create a copy.
  • Copy/pasting cells (even a single cell) into another workbook in a separate Excel process or into the internal datasheet of think-cell
    • To start a separate Excel process, right-click on the Excel symbol in the task bar and click on Microsoft Excel 20… How to start separate Excel process.
    • In Excel 2013 and later, keep the ALT key pressed simultaneously to start a separate process. Confirm the Do you want to start a new instance of Excel? message with Yes.
    • think-cell is affected because our datasheet is based on an Excel worksheet in a separate Excel process.

Copying cells into a new workbook within the same Excel process does not copy unused cell styles.

Problem 2

It may be possible to at first successfully copy/paste data from an Excel workbook (.xlsx) into the internal datasheet of a think-cell chart, but any subsequent copy/paste attempts (even of an empty cell) into the same datasheet cause the message, Microsoft Excel cannot paste the data. In this case, the other problems mentioned in the Symptoms section above do not occur.

This problem happens when copying data between separate Excel processes, if a custom table style of the same name exists in both the source file and the target file. Since the first copy/paste also copies any custom table styles, the subsequent copy/paste attempts fail. The problem can be reproduced without think-cell.

Reproduction without think-cell

  1. Open two Excel processes. If Excel is already open, and you create a new workbook with Ctrl+N, the second workbook is kept within the same Excel process.
    • To start a separate Excel process, right-click on the Excel symbol in the task bar and click on Microsoft Excel 20… How to start separate Excel process.
    • In Excel 2013 and later, keep the ALT key pressed simultaneously to start a separate process. Confirm the Do you want to start a new instance of Excel? message with Yes.
  2. In the workbook that is open in the first Excel process, go to ExcelHomeStylesFormat as TableNew Table Style …OK. This will insert a new custom table style named "Table Style 1".
  3. Copy/paste a cell from the workbook containing "Table Style 1" to the other workbook in the second Excel process. This works the first time.
  4. Once again, copy/paste a cell from the workbook containing "Table Style 1" to the other workbook in the second Excel process. The second time this fails with the message, Microsoft Excel cannot paste the data.

Solution 2

  • Excel 2007 and earlier are not affected by this issue.
  • In Excel 2010, the problem still occurs as of November, 2015. If your company has a Microsoft Office Support contract and you want to contact Microsoft to ask for a fix, you may refer to Microsoft case number 113012810172527, 114021711190574 (Excel 2013).

    In the meantime, you can use the following workarounds:

    • Paste the data into the think-cell datasheet as text:

      think-cell datasheetPasteMore (the little triangle button next to the Paste button) → Paste Special …TextOK.

    • Click the Clear the Formats of all Cells button in the internal datasheet of think-cell. This removes any custom table styles from the datasheet and subsequent paste commands will work at least once.
  • Microsoft has fixed this issue for Excel 2013. Please install either KB2881014 from the Microsoft Office updates on June 10, 2014, or the hotfix KB2881042.

Share