After experimenting... The limit seems to be 32767. This is one less then
65536/2.
65536 As we all know :-) is the row limit in Excel 2003 and equals 2^16.
I use the following code to strip nast dinguses out of strings in *word*....
It should transfer to Excel with no changes.
Not saying it'll cure the bad transform but it may help.
Function fncStripChrs(strpString As String)
' Strip some characters
Dim intlM As Integer
Dim strlS As String
strlS = ""
For intlM = 1 To Len(strpString)
Select Case Asc(Mid(strpString, intlM, 1))
Case 13, 7, 10, 9, 150, 147
Case Else
strlS = strlS & Mid(strpString, intlM, 1)
End Select
Next
fncStripChrs = strlS
' ***********************************************************************
End Function
In Word tables there is an end of cell marker of 2 characters as well. Maybe
you're just hitting that.
This is a *Word* function that strips off the end of cell markers.
Function fncCellText(ipTable As Long, _
ipRow As Integer, _
ipCol As Integer, _
Optional opDoc As Variant) As String
' Return the contents of a cell without the end of cell marker
' Control chrs are stripped.
' Leading and trailing spaces are stripped.
Dim slCell As String
Dim olDoc As Document
If IsMissing(opDoc) Then
Set olDoc = ActiveDocument
Else
Set olDoc = opDoc
End If
slCell = olDoc.Tables(ipTable). _
Cell(ipRow, ipCol).Range.Text
slCell = left(slCell, Len(slCell) - 2)
slCell = fncStripCtlChrs(slCell)
slCell = Trim(slCell)
fncCellText = slCell
' ***********************************************************************
End Function