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:
| Platform | Registry path |
|---|---|
| Excel 97 & Win OS 32 bits | HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel |
| Excel 2000 and later versions & Win OS 32 bits | HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel |
| Excel 97 & Win OS 64 bits | HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\3.5\Engines\Excel |
| Excel 2000 and later versions & Win OS 64 bits | HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel |
| Microsoft Office 2010 | HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel |
| Microsoft Office 2013 | HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel |
| Microsoft Office 2016 | HKEY_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.
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.
Related Articles
My Excel linked data is not refreshed or is giving errors.
Make sure that your Excel sheet is in shared mode to allow access to multiple persons. You are working on the document and DataPoint too. Open your Excel file and click Tools > Share Workbook (Excel 2000-2002-2003) or click Review > Share workbook ...Problem while starting viewer: Could not load file or assembly 'Microsoft.Office.Interop.PowerPoint, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies.
When using Microsoft PowerPoint you have to ensure that .Net programmability support is enabled. To verify this click Start from the Windows menu > Control Panel. Select Programs > Programs and Features. Locate the Microsoft Office setup in the list ...