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:
These problems can also occur when you use think-cell's Excel data links.
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:
The steps to check this are in detail:
In Office 2013 and later copying worksheets between workbooks prevents the accumulation of unused cell styles by default:
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.
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
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.
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 cells into a new workbook within the same Excel process does not copy unused cell styles.
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.
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.
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: