KB0156: I can't paste data into a think-cell datasheet, or the format is lost

Problem

When you copy data in an Excel workbook (.xlsx) and paste the data into the datasheet of a think-cell element, you encounter 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—for example, 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.
• Although you selected Use Datasheet Fill on Top, cell colors do not appear in the element.

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

Solution

Any of these problems can occur if the .xlsx workbook contains more than approximately 65,490 cell styles. To check the number of cell styles, in the VBA editor's Immediate window, use the command print Activeworkbook.Styles.Count. Read more

To clean up the number of cell styles in the affected workbook, try any of the following:

• Copy your worksheets into a new workbook. Read more

  In Excel 2013 and later, you can prevent the accumulation of unused cell styles by copying the affected worksheets into a new workbook.

  1. Select all worksheets in your workbook: select the first sheet, hold Shift, then select the last sheet.
  2. Right-click the selection, then select Move or Copy.
  3. In the Move or Copy dialog, on the To book dropdown menu, select (new book).
  4. Select Create a copy, then select OK.
  5. Save the new workbook under a new name.
• Clean up your workbook using XLStyles Tool. Read more
  1. Open XLStyles Tool.
  2. Select Get File.
  3. Select the file that you want, then select Scan Selected File.
  4. In Used Styles, select Force all Styles to "Normal".
  5. Select Process File.
• Remove custom cell styles from your workbook using a VBA macro. Read more

  Use the DeleteCustomStyles() macro to remove all custom cell styles from your workbook.

  1. Open the VBA editor (Alt+F11).
  2. Select your workbook, then select Insert > Module.
  3. Enter the the following macro, then run it (F5).

  Copy

  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

  Copied to Clipboard

The best solution would be to prevent users from pasting unused cell styles into other workbooks, but this requires Microsoft to fix a bug in Excel 2007 and later. So far, 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 ask Microsoft for a fix, you can refer to the Microsoft case numbers 113012810172527 and 114021711190574 (Excel 2013).

Explanation

When you copy and paste Excel data, or transfer data through an Excel data link, the data is stored in the BIFF12/.xlsb format. This file format supports approximately 65,490 cell styles. If you copy data from an .xlsx workbook with more cell styles than the format supports, either you can't paste the data into a think-cell datasheet, or the pasted data loses formatting information. Read more