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 Excel 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 ~3000 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 clean up tool

    Use the clean up tool XLCleanerDotNET4.0 , that Microsoft recommends in KB213904. After starting the tool, click on Get Excel File, tick Force all cell styles to 'Normal', then click on 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

    Remove_Styles() removes only unused custom cell styles from your workbook. It is similar to the macro above, however it may take very long to run. The macro was provided by the Microsoft Office Support.

    Option Explicit
    Dim st_array() As String
    Dim i_x As Long
    Sub Remove_Styles()
    Dim stname As String
    Dim ustname As String
    Dim uc As Range
    Dim retval As Boolean
    Dim ust As Style
    Dim sh As Worksheet
    i_x = 0
    For Each sh In ActiveWorkbook.Worksheets
        For Each uc In sh.UsedRange
            stname = uc.Style.Name
            retval = Check_Array(stname)
            If retval = False Then
            ReDim Preserve st_array(i_x)
            st_array(i_x) = stname
            i_x = i_x + 1
            End If
        Next uc
    Next sh
    For Each ust In ActiveWorkbook.Styles
        If ust.BuiltIn = False Then
            ustname = ust.Name
            retval = Delete_Styles(ustname)
            On Error Resume Next
            If retval = True Then ust.Delete
            On Error GoTo 0
        End If
    Next ust
    End Sub
    Function Delete_Styles(stylename As String) As Boolean
    Delete_Styles = True
    Dim i_y As Long
    For i_y = 0 To i_x - 1
        If st_array(i_y) = stylename Then Delete_Styles = False
    Next i_y
    End Function
    Function Check_Array(stylename As String) As Boolean
    Check_Array = False
    Dim i_y As Long
    For i_y = 0 To i_x - 1
        If st_array(i_y) = stylename Then Check_Array = True
    Next i_y
    End Function

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 REG:113012810172527.

Explanation 1

The file format used when pasting data into the think-cell datasheet or when transferring it over an Excel data link (Biff8/.xls file format) only supports ~3000 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. When 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 for Excel 2013, REG:114021711190574.

    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.