Extra character when copying text from Word table cell to spreadsheet cell

user_19677835 0
Viewed 23

I am copying text from table cells in a Word document to cells in an Excel spreadsheet.

I have a VBA macro in Word that mostly works using the following statement to copy the cell text

nWorksheet.Cells(nRow, nCol).Text = oDocument.Range(Start:=oCell.Range.Start, End:=oCell.Range.End - 1)

Unfortunately, what ends up in the spreadsheet cell seems to have an extra invisible character that acts like a newline or carriage return.

Does anyone know what this extra character is or how to avoid it?

Thanks!

ms-word copy
1 Answers

I am using this small function to return the text from a cell, as I always forget the logic, but calling getCellText is easy to remember.

Public Function getCellText(c As Word.cell) As String
    'returns text without cell-end
    getCellText = Replace(c.Range.Text, Chr(13) & Chr(7), vbNullString)
    'alternative:
    'getCellText = Left(c.Range.text, Len(c.Range.text) - 2)
End Function

Chr(13) & Chr(7) represents the cell end that you need to remove. Therefore yo need either to move the end by two characters or replace it.

You can use it like this:

nWorksheet.Cells(nRow, nCol).Text = getCellText(oCell)

user_165784240
Related