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

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 copy > OK
  • 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:

  • Microsoft Office 2007: KB2553085
  • Microsoft Office 2010: KB2598127, which was published in the Office 2010 update for April 2012.

  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 Insert > Module. 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.

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.

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 datasheet > Paste > More (the little triangle button next to the Paste button) > Paste Special … > Text > OK.

  • 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.