MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to find bad characters in cells? What VBA checks?

  Asked By: Jose    Date: Nov 23    Category: MS Office    Views: 2199

I have a Excel 2003 worksheet with ~15 columns & ~70 rows some of
whose cells were copy & pasted from a Word 2003 table, maybe cell by
cell, maybe multiple cells at a time - who knows. Some cells have 800-
900 characters of text.

When the Excel sheet is uploaded into SharePoint 2007 some data seems
to be causing XSLT transform errors.

What kind of checks should I perform in VBA to find & identify the bad
cells & characters? I guess I have to scan every character of every
cell, but what for?



3 Answers Found

Answer #1    Answered By: Indie Williams     Answered On: Nov 23

Did you get an answer to this yet??????

Answer #2    Answered By: Kuhlbert Schmidt     Answered On: Nov 23

Not exactly sure, I thought Excel has a 256 character limit in a cells  content
(from a 'paste' function perspective)

Answer #3    Answered By: Maria Hughes     Answered On: Nov 23

After experimenting... The limit seems to be 32767. This is one less then

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
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
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

Didn't find what you were looking for? Find more on How to find bad characters in cells? What VBA checks? Or get search suggestion and latest updates.