macro for performing more than one find and replace procedure at the same time

Thanks again for your help with the find-and-comment-from-list macro, Greg. Deleting that line fixed the character limit issue.

I've been trying to shift from a Word-based database to an Excel-based one. I added the Excel object library to my references, but I can't seem to figure out the analogous Excel objects to "translate" the macro (e.g. "Sheets" vs. "Table," etc.). I'm new to VBA so my method is just trial-and-error (and so far no trial-and-success). Any suggestions?

Delete that entire line.

Thanks for the find-and-comment macro, Greg!

It seems to be working very well, except for one issue: when it reaches a comment over 255 characters, I get a runtime error 5854 (string too long). The debugger highlights this line:

.Replacement.Text = oRngComment.Text

Is there a line I can add that removes the character limit, or sets it to a very high number?

The macro evolving here makes several assumptions.  That is ok, so does my alteration to find and comment later in this thread.  Still I thought I would mention them just to save others from possibly scratching their heads in frustration later on.

First it assumes, probably correctly, that the user wants to search the main text area of the document and fails to indicate or warn the user that the search and replace will affect only the main text area.  The point is that a simple VBA find and replace like this one only looks in the main text storyrange.

It assumes that the file path list and it assumes that the file at that path has a table with at least two columns.  If any of those assumptions are wrong the code will puke.

What follows is still a relatively basic VBA find and replace procedure but it does address the issue identified above.

Sub ReplaceFromTableList()

Dim oListDoc As Document, oDoc As Document

Dim strFile_Path As String

Dim lngType As Long

Dim oTbl As Table

Dim oRng As Range

Dim strFind As String, strReplace As String

  'What to search

  Set oDoc = ActiveDocument

  'Where to search

  lngType = Selection.Range.StoryType

  'What to search for and replace with.  Change path below reflect

  "the name and path of the table document

  strFile_Path = "D:\List.docm"

  'Handle possible errors.

  On Error GoTo Err_Handler

  'Open list file.

  Set oListDoc = Documents.Open(FileName:=strFile_Path, Visible:=False)

  Set oTbl = oListDoc.Tables(1)

  For lngIndex = 1 To oTbl.Rows.Count

    'Set the search range. A simple VBA F&R does not search all storyranges.

    Set oRng = oDoc.StoryRanges(lngType)

    strFind = oTbl.Cell(lngIndex, 1).Range.Text

    strFind = Left(strFind, Len(strFind) - 2)

    strReplace = oTbl.Cell(lngIndex, 2).Range.Text

    strReplace = Left(strReplace, Len(strReplace) - 2)

    With oRng.Find

      .ClearFormatting

      .Replacement.ClearFormatting

      .MatchWildcards = False

      .MatchWholeWord = True

      .Text = strFind

      .Replacement.Text = strReplace

      .Forward = True

      .Wrap = wdFindContinue

      .Execute Replace:=wdReplaceAll

    End With

  Next lngIndex

  oListDoc.Close wdDoNotSaveChanges

lbl_Exit:

  Exit Sub

Err_Handler:

  Select Case Err.Number

    Case 5941:  MsgBox "The source table is missing or missing a required column."

    Case Else:  MsgBox Err.Description

  End Select

  Resume lbl_Exit

End Sub