How to trim table cell values ​​in MS Word using VBA

Question

I have a table in a Word file. This vba program below will go through all the cells of the table

Dim strCellText As String Dim uResp As String Dim Row As Integer Dim Col As Integer Dim itable As Table For Each itable In ThisDocument.Tables uResp = "" For Row = 1 To itable.Rows.Count For Col = 1 To itable.Columns.Count strCellText = itable.Cell(Row, Col).Range.Text uResp = uResp & Trim(strCellText) Next Next MsgBox uResp Next 

On a row, uResp = uResp & Trim(strCellText) VBA adds a new line and a dot to each cell. Thus, MsgBox uResp displays a column message box. How can I delete a new line and period when displaying a message box.

Answer 1

Word uses two characters as the cell separator, so use

 uResp = uResp & Trim(left(strCellText,len(StrCellText)-2)) 

In this case, you will not have a separator between the cell values, so you can combine the space.

Answer 2

Try adding the Replace and InstrRev functions to find the last new line and. in the text box, then Left in the cell text and save the result "Cleared"

 strCellText = itable.Cell(Row, Col).Range.Text 'added Clean = Trim(strcelltext) posFromEnd = InStrRev(Clean, vbCr + chr(7)) 'obviously a newline and a chr(7) .. If (posFromEnd > 0) Then Clean = Trim(Left(Clean, posFromEnd - 1)) End If 'end added uResp = uResp & Clean 'updated 

To verify that the last characters in a string do this in the debugger:

 strCellText = itable.Cell(Row, Col).Range.Text For r = 1 To Len(strCellText) c = Mid(strCellText, r, 1) Debug.Print Right("0" + Hex(Asc(c)), 2) + " "; Next Debug.Print 'set a breakpoint here 

In the next window, copy the result so that we can reason from there

Answer 3

If you are sure that this will ALWAYS happen, you can simply shorten the string with two characters:

  MsgBox LEFT(uResp,LEN(uResp)-2) 

EDIT: Of course, the winod is correct. Not uResp should be shortened, but strCellText. But this answer has already been given. I'm sorry for the mistake.

Answer 4

I am using the following solution:

 endOfCell = Chr(13) + Chr(7) uResp = uResp & Split(strCellText, endOfCell)(0)