Replace text in word from Access becomes bold when should not be bold (1 Viewer)

[sxschech]

Been working on automating the editing of word docx that needs cleanup prior to conversion to pdf. I've got most of the logic, a bit convoluted due to multiple checkboxes, dates and wording that in combination determines what should be displayed. Been able to pull data off the word file and evaluate it. If the logic encounters an issue, it prompts user with an Inputbox to provide corrected values. I noticed that when the data gets replaced in word, it becomes bold if the preceding text was bold even though the actual text being replaced was not bold to begin with. In the screenshot, this is how the data should look. However, after running the access vba code, all the text becomes bold. Also, I noticed that sometimes there are extra spaces or tabs after the text, what is best way to clean that up without removing the final tab as it seems that word uses a final tab to indicate the end of the cell in a word table cell (or that's how it appears to me).

As an example in the code below, it checks the date, if original entry was "TDB", "ASAP", etc, it gets flagged and prompts user to enter a date, the default being today's date.

If Not IsDate(stDateSubmitted) Then
        'Get new date and replace it in the cell of the word doc
        '20190221
        stDateSubmitted_New = InputBox(stDateSubmitted & " is not a valid Submit date. Please adjust accordingly.", "Date Issue", Format(Date, "mm/dd/yy"))
        WordDoc.tables(1).cell(6, 2).Range.Text = Replace(WordDoc.tables(1).cell(6, 2).Range.Text, stDateSubmitted, stDateSubmitted_New)
        stDateSubmitted = stDateSubmitted_New
    End If

 

[Micron]

Reminds me of how much I came to dislike Word and its idiosyncrasies. No expert when it comes to your problem, but what I'd look for first is what's going on in the table cells when it comes to Styles. You can have a particular style for any paragraph, cell, heading - whatever, then change the format to something else. This doesn't modify the underlying style. When you paste copied text into a stylized portion of the document (come to think of it, there may not be any portion of a Word document that doesn't have a style setting) the pasted text often conforms to the style setting. I'd start by looking at the style settings for the area you're replacing.

 

[sneuberg]

I think your answer lies in the way they are doing it in this thread https://stackoverflow.com/questions/18609963/to-find-and-replace-a-text-in-the-whole-document-in-ms-word-2010-including-tabl.

In your case something like:

With WordDoc.Tables(1).Cell(1, 1).Range.Find
      .Text = stDateSubmitted
      .Replacement.Text = stDateSubmitted_New
      .Replacement.ClearFormatting
      .Replacement.Font.Italic = False
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      .Execute Replace:=wdReplaceAll
End With

But you will probably want to tailor this a bit.

 

[The_Doc_Man]

Micron - you are probably correct about Word and styles. Every document has a "current style set" and every style has a more or less "default style." So even if you tried to remove all styles, the defaults would kick in. And if you tried to remove the style set, a default exists there, too.

 

[sxschech]

Hi Guys.

Micron, I checked the styles of the existing text in the cell.

Date Submitted: 01/21/19

I selected the text for each part. and viewed the style inspector window.

Date Submitted
--------------------
Table Paragraph (Plus: Left: 0.01", Before: 0.2 pt)
Default Paragraph Font (Plus: Arial Narrow, Bold)

01/21/19
------------------
Normal (Plus <none>)
Default Paragraph Font (Plus: Arial Narrow)

sneuberg, I copied your code and stepped through it. There was no change to the wording or style of the text nor any error message. 01/21/19 remained at 01/21/19.

 

[NauticalGent]

Would HTML tags work? Something like </b> Your Text <b> (if you wanted to turn it back on...

 

[sxschech]

NauticalGent, not sure how would incorporate html into it, also, trying to prevent the replaced "pasted text" from being bold. The replaced text should remain normal/unbold as was the original text.

 

[sneuberg]

sneuberg, I copied your code and stepped through it. There was no change to the wording or style of the text nor any error message. 01/21/19 remained at 01/21/19.

I've attached a zip file with the Word Document and Access DB I used the to test this. Maybe seeing how I tested it will help you incorporate it into your code. The test code is in a the module Word Table Cell Test.

I just notice that the code I posted had Tables(1).Cell(1, 1) rather than tables(1).cell(6, 2) as it should be for your case.

 

[NauticalGent]

...also, trying to prevent the replaced "pasted text" from being bold. The replaced text should remain normal/unbold as was the original text.

Understood, the </b> turns bold off. I got the idea from Allen Browne's site:

http://allenbrowne.com/AppSearchHighlight.html

I have tested it in Access Forms, but it only works if you set the control's TextFormat = acTextFormatHTMLRichText.

Not sure how you would transfer that to Word, but I figured some of you are smarter than me...

 

[Micron]

Then replacing the text is basically causing the new text to inherit the style of the proceeding line. If you were to place the cursor on the top line of the cell, hit enter and paste, my guess is that the new paragraph would be interpreted in the non-bold style.
The code solution proposed is probably the way you'll have to go. I was working on a similar block of code but didn't save it when I saw that posting. Using Find/Replace is probably the way to go. There is another possible method - involves Word code to insert the text as red, then replace all red with your standard font. You could insert a lot of red replacements then change all red font to black & not bold by using the entire document (or just a table) as the Range.

 

[sxschech]

Thanks for providing a useful file sneuberg. Was a challenge to track down the issue. I ran your file and it worked on your sample. I then copied the "With" block into my code and it still didn't work. I modified your code to open my word file rather than the sample to see if it was the word doc. Running the code from your file, all was well. As I was about to give up, mouse hovered over each item in the block and then I noticed that in my file, when the mouse was on the line

.Execute Replace:=wdReplaceAll

it said empty. Yours had a numeric value. I changed my code to the numeric value and the code did what we wanted it to do, replace text without changing to bold.*

Any idea how I can take this block to the next step and call it so that I don't have to have a bunch of these in the main code for each word cell I am updating? I tried below, and it said object required upon hitting the With block.

Sub msReplaceCellData(OrigText As String, ReplaceText As String, r As Integer, c As Integer)
    With WordDoc.Tables(1).Cell(r, c).Range.Find
      .Text = OrigText
      .Replacement.Text = ReplaceText
      .Replacement.ClearFormatting
      .Replacement.Font.Italic = False
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      .Execute Replace:=1 'Replace 1 instance
    End With
End Sub

*found the numeric values:
https://docs.microsoft.com/en-us/office/vba/api/word.wdreplace

Name Value Description
wdReplaceAll 2 Replace all occurrences.
wdReplaceNone 0 Replace no occurrences.
wdReplaceOne 1 Replace the first occurrence encountered.

 

[Cronk]

I think you are getting the bold because you are inserting text before the cell delimiter (or whatever it's called) which is formatted bold. You might be able to format that delimiter to normal and not have the problem.

Alternatively, I use bookmarks in the document I am completing from Access. I can then set the formatting before inserting the text. Bookmarks I find are a more precise way of positioning the cursore

Incidentally, while the Word macro generator inserts all the different formatting lines within the With/End block, I remove all the unnecessary crud.

 

[sxschech]

Hi Cronk,

Per your suggestion, I cut down the statements to:

    With WordDoc.Tables(1).Cell(6, 2).Range.Find
      .Text = stDateSubmitted
      .Replacement.Text = stDateSubmitted_New
      .Execute Replace:=1 'Replace 1 instance
    End With

So far, running it on my test file it works. We'll see once I start receiving more files where there is creativity and updates in layout formatting by the file creators/originators/users.

Haven't worked with bookmarks in word, so don't have a reference point as to what they can do adv/disadv etc. and how to put them to use.

Regarding the

the bold because you are inserting text before the cell delimiter

I don't think it has to do with the cell (but maybe that isn't what you meant by cell and delimiter) as this is something word does with standard text in paragraphs too. Usually, I notice that if you select/highlight a word, it extends beyond the word to the empty space between words and any formatting applied to the word also applies to the empty space, unless you specifically adjust the highlighted/selected text only. That is probably what is happening in this case, the originator of the document highlight text in bold including the space after the text, so that when I replace it inherited the format.

 

[sneuberg]

Thanks for providing a useful file sneuberg. Was a challenge to track down the issue...
Any idea how I can take this block to the next step and call it so that I don't have to have a bunch of these in the main code for each word cell I am updating? I tried below, and it said object required upon hitting the With block.

You can pass the Word.Document object to the subroutine like:

Sub msReplaceCellData(WordDoc As Word.Document, OrigText As String, ReplaceText As String, r As Integer, c As Integer)
    With WordDoc.Tables(1).Cell(r, c).Range.Find
      .Text = OrigText
      .Replacement.Text = ReplaceText
      .Replacement.ClearFormatting
      .Replacement.Font.Italic = False
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      .Execute Replace:=1 'Replace 1 instance
    End With
End Sub

In my test code I called this with:

msReplaceCellData WordDoc, stDateSubmitted, stDateSubmitted_New, 1, 1

You probably could simplify this. You probably don't need all of those properties set, for example, I don't think ".Replacement.Font.Italic = False" is doing anything for you. I'm finding it hard to find the basic documentation on the find method used this way but to get you started you could poke around these links:

https://docs.microsoft.com/en-us/office/vba/api/word.table.range

https://docs.microsoft.com/en-us/office/vba/api/overview/word

 

[sxschech]

Thanks, that worked well. Appreciate your help/guidance. I did have to change WordDoc As Word.Document to WordDoc As Object in order for it to compile and work.

 

[sxschech]

UPDATE:

After a couple of years and having the unbold not always happen (happens when the phrase to find appears more than once, it only changes the first one. Normally that is perfect, but in a cell containing two items, this isn't going to work), I think I finally got the syntax right to unbold the entire cell and then apply bold only to the text that should remain bold rather than trying to remove the bold from the variable text portion. @Micron and @Cronk alluded to this in post #10 and #12 and until today when I finally had time to revisit, figured out the syntax which may have been included somewhere in the link provided by @sneuberg in post #14 and combined with this other link https://docs.microsoft.com/en-us/office/vba/api/word.range.bold

Here is the code I am now using to unbold text in a word table cell rather than the previous code posted above :

Sub UnboldInCell(worddoc As Object, boldtext As String, r As Integer, c As Integer, Optional BoldCell As Boolean)
'Based solution on how to unbold text in a cell rather than
'document from here:
'https://stackoverflow.com/questions/36729431/using-word-vba-to-format-parts-of-table-cell-text
'and combined with code style used in msReplaceCellData and UnboldText in this
'code module
'20190531
'Further refined by paragraph: Finding text without changing the selection
'because the previous code was changing text throughout the document rather
'than the cell.  So far, this seems to change only the text in the portion
'of the document that we want to unbold.
'https://docs.microsoft.com/en-us/office/vba/word/concepts/customizing-word/finding-and-replacing-text-or-formatting
'20190604
'Added optional BoldCell so that can use same function to bold text
'20200205
    Dim WordRangeCell As Object
    Set WordRangeCell = worddoc.Tables(1).cell(r, c).Range
   
    With WordRangeCell.Find
        .Text = boldtext '"Change the Text"
        .Forward = True
        .Execute
        If BoldCell = False Then
            If .Found = True Then .Parent.Bold = False
        Else
            If .Found = True Then .Parent.Bold = True
        End If
    End With
End Sub

This is how it is called:

'Revised Code for Spec and Segment
'First Unbold the cell altogether --False tells code to unbold the text
'Add back the bold for the phrase(s) --True tells code to make the text bold
'20220104
'https://docs.microsoft.com/en-us/office/vba/api/word.range.bold
worddoc.Tables(1).cell(3, 2).Range.Bold = False
UnboldInCell worddoc, "Spec.:", 3, 2, True
UnboldInCell worddoc, "Segment:", 3, 2, True