PresentationPoint

            Problem: Truncated Texts of Excel Connections

            Sometimes longer texts coming from an Excel connection, are truncated in length. Not all text is visible in PowerPoint. This happens when the Excel drivers makes an incorrect conclusion by scanning the first 8 data rows to determine its data type.

            When the first 8 rows is not holding text longer than 255 characters, then internally the driver will use short text fields (max 255 characters) and cut off possible later and larger values.

            You can influence the number of rows that Excel is scanning to determine the type of data. By default, this number is 8. You can higher this number (up to 16) or set it to 0 for a full scan. Microsoft warns that putting this value to 0, could result in performance issues. But we have have never seen big performance issues with this change, so go ahead, but keep in this mind. To change the number of rows to scan, use the registry editor.

            • On the Start menu, click Run.
            • Type in regedit and click OK.
            • In the registry editor that opens after that, expand the nodes of the registry and navigate to a few possible locations. The locations vary on the operating system, 32 or 64 bits, Microsoft Office version installed etc. Possible locations are:
            PlatformRegistry path
            Excel 97 & Win OS 32 bitsHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
            Excel 2000 and later versions & Win OS 32 bitsHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
            Excel 97 & Win OS 64 bitsHKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\3.5\Engines\Excel
            Excel 2000 and later versions & Win OS 64 bitsHKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel
            Microsoft Office 2010HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
            Microsoft Office 2013HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel
            Microsoft Office 2016HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
            • In that folder, look for the property name TypeGuessRows at the right and select it.
            • Right click the property and choose Modify… from the popup menu.
            • Set the Value data to 0 and hit OK.
            • Exit the registry editor now.
            registry change typeguessrows
            edit value

            After this change, restart PowerPoint and try to read the information again. DataPoint will now scan all values of your Excel file and choose the correct type of data, the longer text field in this matter. All Excel texts, short and long, will be visible correctly in PowerPoint as of now.

            This tweak for Microsoft Excel works perfectly for DataPoint as well as for other products using the Excel link driver.

            Updated: 24 Nov 2015 01:43 AM
            Helpful?  
            Help us to make this article better
            1 0