Category: Excel

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 View → Immediate 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
  • 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

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.

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

 

Ref:-https://www.think-cell.com/en/support/kb/0156.shtml