CODEWITHSUNDEEP
vbaexcelreplacems-word
Daryn
Daryn

Reputation: 19

executing a find and replace in a word doc from Excel VBA

I am trying to open specific Word templates based on data from Excel (that part is working). Then, once the template is open, I am trying to do find based on tags in the Excel doc and replacing with corresponding data in the same column. When I run the macro, it opens the template and just spins and spins without giving me the output. Here is the code:

` Sub New_Purification_SOP()
    '
    ' New_Purification_SOP Macro
    ''Open an existing Word Document from Excel
      Dim objWord As Object
      Dim myValue As Variant
      Dim PurCol As Variant
     'open input box requesting line of the material to be made
      myValue = InputBox("Select Row to create SOP")

      Set objWord = CreateObject("Word.Application")
      objWord.Visible = True
      'Change the directory path and file name to the location
      'of the document you want to open from Excel
       If ActiveSheet.Cells(myValue, 10) = "Supe" And _
          ActiveSheet.Cells(myValue, 12) = "IgG1" Then
         objWord.Documents.Open "S:\generic filename"
         With objWord
            For PurCol = 3 To 13 'move through columns left to right
               TagName = .Cells(10, PurCol).Value 'get tag name from row
               TagValue = .Cells(myValue, PurCol).Value 'get tag name from row
               With objWord.Content.Find
                     .Text = TagName
                     .Replacement.Text = TagValue
                     .Wrap = wdFindContinue
                     .Execute Replace:=wdReplaceAll  'Forward = True, Wrap = _ 
                        wdFindContinue
                End With
            Next PurCol
       End With`
...

I am very new to VBA so please critique as much as you are willing.

Upvotes: 2

Views: 1888

Answers (2)

Cindy Meister
Cindy Meister

Reputation: 25693

The problem comes from using Find.Wrap = wdFindContinue in your code.

This property instructs Word to continue doing the Find - sort of like the user continuously pressing "Find Next" in the dialog box. It should rarely (more like never) be used when coding as it can result in a non-ending loop, as in this instance.

In this case, since wdReplaceAll is being used - meaning the code finishes in one step - Wrap doesn't necessarily need to be specified. But generally good practice is to use Find.Wrap = wdFindStop.

Upvotes: 1

Dave Reilly
Dave Reilly

Reputation: 65

You may want to include the error handling in your opening / creation of the word object. I've found that if I already have an instance of word up it can actually crash the application. That may be contributing to your issue with your program. 

source: https://stackoverflow.com/questions/17177412/close-release-word-object-in-vba
On Error Resume Next
Set objWord = GetObject(, "Word.Application")

'We've tried to get Word but if it's nothing then it isn't open
If objWord Is Nothing Then
    Set objWord = CreateObject("Word.Application")
End If

'It's good practice to reset error warnings
On Error GoTo 0

After that, troubleshooting find/replace can be a painful task. See this article for why: https://gregmaxey.com/word_tip_pages/words_fickle_vba_find_property.html. What you may want to do is use the macro recorder to check the find / replace code, then just lift that into your loop. It can save a lot of time debugging.

Upvotes: 1

Related Questions