A parameter in a DataDirect function stored in an Excel cell is limited to 255 characters
Trying to save a DataDirect function from a DataDirect dialog into an Excel spreadsheet can result in a dialog being displayed that says the resultant macro has exceeded 255 characters and needs to be shortened. And, if the DataDirect function is added directly to a cell, the displayed data can be #VALUE! instead of the expected results. Finally, the IM Data Access and Reports guide (versions up to 3BUF001094-510 A), contains the following inaccurate statement
There is a string limitation in Excel 2007 that causes an error while retrieving data when a combination of objectname, propertyname, and logname exceed 255 characters. To work around this problem:
1. Place the tag name in a different cell in Excel.
2. Write the function or edit the existing function.
3. Replace the object name in the function with a reference to the cell created in Step 2.Symptoms:
If you are using a DataDirect dialog to create a function and save it into a spreadsheet and get the "Batch Data: Formula Too Long" dialog, select okay to exit the dialog. Then select the cell or range of cells where you want the formula to go and paste the formula from the clipboard. The function will work fine as long as no single parameter in the formula exceeds the 255 character limit.
If more the 255 characters are used by one parameter in a DataDirect formula, instead of the expected data in the cells, #VALUE! is displayed. If the formula was entered correctly, then calculate the length of each parameter in the formula. If any exceed 255, then the only option is to make the function call from a macro instead of a cell. In a VB macro there is no limit on parameter lengths.