johnweber
03-01-2015, 06:30 PM
I've requirement where in I need to compare two samples of text in Excel using VBA. Since each text sample is more than 10000 characters in LEN, I use a Userform.TextBox field fetch,storing them into string variables.
Next, I use a VBA code that I found here by 'mikerickson': VBA routine to perform Text Diff (http://www.mrexcel.com/forum/excel-questions/714927-visual-basic-applications-routine-perform-text-diff.html)
To compare the texts. The problem is that the output of this code is input into a Cell: A1. Since the text samples contain more than 10000 characters each, the result is not displayed in A1 since excel has limit on the number of characters per cell.
I would like to know how to get the result (with all the formatting like underline and strikethrough) in to an MS Word Doc.
Currently, this is the code used to output to cell A1.
strResult = ComparedText(strOne, strTwo, olStart, olLength, nwStart, nwLength)
With outCell.Cells(1, 1)
.Clear
.Value = strResult
For i = LBound(olStart) To UBound(olStart)
If olStart(i) <> 0 Then
With .Characters(olStart(i), olLength(i)).Font
.ColorIndex = 3
.StrikeThrough = True
End With
End If
Next i
For i = LBound(nwStart) To UBound(nwStart)
If nwStart(i) <> 0 Then
With .Characters(nwStart(i), nwLength(i)).Font
.ColorIndex = 4
.Underline = True
End With
End If
Next i
End With
Need a replacement code here which will enter strResult into a word document with the exact same formatting for the mismatches as in the code above.
No intermediate Excel Cells to be used to store the strResult, it needs to be directly written into the word document.
Hoping somebody helps. Thanks in advance.
Next, I use a VBA code that I found here by 'mikerickson': VBA routine to perform Text Diff (http://www.mrexcel.com/forum/excel-questions/714927-visual-basic-applications-routine-perform-text-diff.html)
To compare the texts. The problem is that the output of this code is input into a Cell: A1. Since the text samples contain more than 10000 characters each, the result is not displayed in A1 since excel has limit on the number of characters per cell.
I would like to know how to get the result (with all the formatting like underline and strikethrough) in to an MS Word Doc.
Currently, this is the code used to output to cell A1.
strResult = ComparedText(strOne, strTwo, olStart, olLength, nwStart, nwLength)
With outCell.Cells(1, 1)
.Clear
.Value = strResult
For i = LBound(olStart) To UBound(olStart)
If olStart(i) <> 0 Then
With .Characters(olStart(i), olLength(i)).Font
.ColorIndex = 3
.StrikeThrough = True
End With
End If
Next i
For i = LBound(nwStart) To UBound(nwStart)
If nwStart(i) <> 0 Then
With .Characters(nwStart(i), nwLength(i)).Font
.ColorIndex = 4
.Underline = True
End With
End If
Next i
End With
Need a replacement code here which will enter strResult into a word document with the exact same formatting for the mismatches as in the code above.
No intermediate Excel Cells to be used to store the strResult, it needs to be directly written into the word document.
Hoping somebody helps. Thanks in advance.