1

I've got a manual for a machine that i need to replace a large amount of part numbers with updated ones (17624 numbers). Ive got an excel doc that ive combiled with row A with old numbers and row B with the new numbers. The manual is in word (2016). Ive tried some code that i found but im unable to get it to work. Ive created a smaller excel doc with only a small list of numbers for testing reasons.

Sub findandreplace()

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

Dim i As Integer, j As Integer
Dim lastRow As Integer

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("C:\Users\lforget\Downloads\process_batch_folder_addin\list.xlsx")
Set xlWS = xlWB.Worksheets("Sheet1") 'Replace String with your Worksheet Name

lastRow = 1

For i = 1 To ThisDocument.Words.Count - 1 Step 1
For j = 1 To lastRow Step 1
        ThisDocument.Words(i) = Replace(ThisDocument.Words(i), xlWS.Cells(j, 1).Value, xlWS.Cells(j, 2).Value)
    Next j
Next i

Set xlWS = Nothing
xlWB.Close True
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

Runtime error 6

  • ived tried reducing the amount of lines in the excel doc but that hasnt made any difference
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Lee Forget
  • 13
  • 5
  • 1
    Use `Long` instead of `Integer`. – BigBen Aug 10 '19 at 19:41
  • 2
    See this for how to replace all of the instances of one word with another - https://stackoverflow.com/questions/18609963/to-find-and-replace-a-text-in-the-whole-document-in-ms-word-2010-including-tabl Make sure you use `MatchWholeWord = True` to avoid replacing only parts of a longer number. – Tim Williams Aug 10 '19 at 20:56
  • The part numbers are standard in length but i can see how that would help Changing Int to Long appears to let the program run, but it hangs and doesnt appear to be doing anything. Only way to get it to stop is to end task in program manager. – Lee Forget Aug 10 '19 at 21:48
  • looking into the use of integer and load, integer should work in the first place. The part numbers are only 7 digits. – Lee Forget Aug 10 '19 at 22:14
  • The problem isn't the length of the part numbers, it's the number of words in the document: `ThisDocument.Words.Count`. And potentially also `lastRow`. In any case, see the question mentioned [previously](https://stackoverflow.com/questions/57445147/extracting-data-from-excel-to-use-in-word-find-and-replace#comment101367384_57445147) for how to do what you want. – BigBen Aug 10 '19 at 23:14
  • I changed the code considerably and got it working reliably. before posting my updated code for the next guy, i keep getting errors (word has encountered a problem. You will not be able to undo this action once its complete. Do you want to continue? yes/no) Is there a command to skip these or just hit yes automatically? On Error Resume Next ? – Lee Forget Aug 10 '19 at 23:31
  • Try `ThisDocument.UndoClear` within the loop. may be like `If i mod 100= 0 then ThisDocument.UndoClear` . Also try these to speed up `Application.ScreenUpdating = False` `With Options .Pagination = False .CheckSpellingAsYouType = False .CheckGrammarAsYouType = False End With` May look at [Code Review link](https://stackoverflow.com/questions/57445147/extracting-data-from-excel-to-use-in-word-find-and-replace) – Ahmed AU Aug 10 '19 at 23:59
  • You might be able to use [`Application.DisplayAlerts`](https://learn.microsoft.com/en-us/office/vba/api/word.application.displayalerts) as well. – BigBen Aug 11 '19 at 00:01
  • Yup, i bet you thats the problem. Ill update my code once its done running, do another test and post the updated code. Thank you for all the help guys. – Lee Forget Aug 11 '19 at 00:06
  • As promised i have final code im using. Im sure there are more optimizations possible but its working quite well. no more prompts and youre able to leave it run in the background. ive added notes for novices (like me) so they can edit it to work for their own projects. ::EDIT:: I am unable to post it because its too long, where can i post it? – Lee Forget Aug 11 '19 at 03:31
  • edited code in my original post to have my final version. – Lee Forget Aug 13 '19 at 02:02
  • Please don't add an answer into your question. Self answering is fine, but add it as an _answer_ – chris neilsen Aug 13 '19 at 02:39

0 Answers0