Solved VBA error 438

[Lschai]

Hi everyone
I am trying to run a code that will search & replace a word in multiple word documents. However, I am always getting the error code 438. i am using excel 2016. Sorry I am a total newbie, so no clue what to do.
It‘s always the first line that is highlighted yellow.

Sub FindAndReplaceInFolder()
  Dim objDoc As Document
  Dim strFile As String
  Dim strFolder As String
  Dim strFindText As String
  Dim strReplaceText As String
  '  Pop up input boxes for user to enter folder path, the finding and replacing texts.
  strFolder = InputBox("Enter folder path here:")
  strFile = Dir(strFolder & "\" & "*.docx", vbNormal)
  strFindText = InputBox("Enter finding text here:")
  strReplaceText = InputBox("Enter replacing text here:")
  '  Open each file in the folder to search and replace texts. Save and close the file after the action.
  While strFile <> ""
    Set objDoc = Documents.Open(Filename:=strFolder & "\" & strFile)
    With objDoc
      With Selection
        .HomeKey Unit:=wdStory
        With Selection.Find
          .Text = strFindText
          .Replacement.Text = strReplaceText
          .Forward = True
          .Wrap = wdFindContinue
          .Format = False
          .MatchCase = False
          .MatchWholeWord = False
          .MatchWildcards = False
          .MatchSoundsLike = False
          .MatchAllWordForms = False
        End With
        Selection.Find.Execute Replace:=wdReplaceAll
      End With
      objDoc.Save
      objDoc.Close
      strFile = Dir()
    End With
  Wend
End Sub

thank you!

edit: so i‘ve tried to debug it and i get until .HomeKey Unit:=wdStory

 

[Gasman]

And error 438 is what?

 

[Lschai]

it says: error 438 object doesnt support this property or method

 

[arnelgp]

you forgot to Create an instance of Word.Application:

Sub FindAndReplaceInFolder()
  Dim objDoc As Object
  Dim strFile As String
  Dim strFolder As String
  Dim strFindText As String
  Dim strReplaceText As String
  Dim wdApp As Object
 
  Const wdStory As Integer = 6
  Const wdFindContinue As Integer = 1
  Const wdReplaceAll As Integer = 2
 
  Set wdApp = CreateObject("Word.Application")
 
  '  Pop up input boxes for user to enter folder path, the finding and replacing texts.
  strFolder = InputBox("Enter folder path here:")
  strFile = Dir(strFolder & "\" & "*.docx", vbNormal)
  strFindText = InputBox("Enter finding text here:")
  strReplaceText = InputBox("Enter replacing text here:")
  '  Open each file in the folder to search and replace texts. Save and close the file after the action.
  While strFile <> ""
    'Set objDoc = Documents.Open(FileName:=strFolder & "\" & strFile)
    Set objDoc = wdApp.Documents.Open(FileName:=strFolder & "\" & strFile)
    With objDoc
      With wdApp.Selection
        .HomeKey Unit:=wdStory
        With wdApp.Selection.Find
          .Text = strFindText
          .Replacement.Text = strReplaceText
          .Forward = True
          .Wrap = wdFindContinue
          .Format = False
          .MatchCase = False
          .MatchWholeWord = False
          .MatchWildcards = False
          .MatchSoundsLike = False
          .MatchAllWordForms = False
        End With
        wdApp.Selection.Find.Execute Replace:=wdReplaceAll
      End With
      objDoc.Close savechanges:=True
      Set objDoc = Nothing
      strFile = Dir()
    End With
  Wend
  wdApp.Quit
  Set wdApp = Nothing
End Sub

 

[deletedT]

you forgot to Create an instance of Word.Application:

@arnelgp
OP's code works perfectly here. Not even one error. And the replace is done in the target file.

(on Microsoft Office 365)

Edit: Sorry. I run the code from Microsoft Word. Is it Word question or Access?

 

[Gasman]

From where you obtained that code, did it mention that you needed certain references?
Wast it from here https://www.datanumen.com/blogs/find-replace-contents-multiple-word-documents/ as it looks the same.

arnelgp has likely given you the answer as that code was meant to run from within Word?

I received an object not defined error until I added the word reference, rather than late bind.?

 

[Gasman]

@arnelgp
OP's code works perfectly here. Not even one error. And the replace is done in the target file.

(on Microsoft Office 365)

Edit: Sorry. I run the code from Microsoft Word. Is it Word question or Access?

@Tera

Excel

 

[Lschai]

you forgot to Create an instance of Word.Application:

Sub FindAndReplaceInFolder()
  Dim objDoc As Object
  Dim strFile As String
  Dim strFolder As String
  Dim strFindText As String
  Dim strReplaceText As String
  Dim wdApp As Object
 
  Const wdStory As Integer = 6
  Const wdFindContinue As Integer = 1
  Const wdReplaceAll As Integer = 2
 
  Set wdApp = CreateObject("Word.Application")
 
  '  Pop up input boxes for user to enter folder path, the finding and replacing texts.
  strFolder = InputBox("Enter folder path here:")
  strFile = Dir(strFolder & "\" & "*.docx", vbNormal)
  strFindText = InputBox("Enter finding text here:")
  strReplaceText = InputBox("Enter replacing text here:")
  '  Open each file in the folder to search and replace texts. Save and close the file after the action.
  While strFile <> ""
    'Set objDoc = Documents.Open(FileName:=strFolder & "\" & strFile)
    Set objDoc = wdApp.Documents.Open(FileName:=strFolder & "\" & strFile)
    With objDoc
      With wdApp.Selection
        .HomeKey Unit:=wdStory
        With wdApp.Selection.Find
          .Text = strFindText
          .Replacement.Text = strReplaceText
          .Forward = True
          .Wrap = wdFindContinue
          .Format = False
          .MatchCase = False
          .MatchWholeWord = False
          .MatchWildcards = False
          .MatchSoundsLike = False
          .MatchAllWordForms = False
        End With
        wdApp.Selection.Find.Execute Replace:=wdReplaceAll
      End With
      objDoc.Close savechanges:=True
      Set objDoc = Nothing
      strFile = Dir()
    End With
  Wend
  wdApp.Quit
  Set wdApp = Nothing
End Sub

Thanks - i didn‘t get an error code but it also didn‘t replace the words. Any idea why? Macros are enabled in word..

 

[Lschai]

From where you obtained that code, did it mention that you needed certain references?
Wast it from here as it looks the same.

arnelgp has likely given you the answer as that code was meant to run from within Word?

I received an object not defined error until I added the word reference, rather than late bind.?

Hi, no it didn‘t mention any references. But i did have issues with that in the beginning, so i looked it up and added microsoft word 16.0 obejct library and microsoft word scripting runtime which solved the reference issue for me.

 

[Lschai]

@arnelgp
OP's code works perfectly here. Not even one error. And the replace is done in the target file.

(on Microsoft Office 365)

Edit: Sorry. I run the code from Microsoft Word. Is it Word question or Access?

I run the code from excel vba

 

[arnelgp]

sorry, just tested it and it did replace the text i sought for.

 

[arnelgp]

Macros are enabled in word

macros are Always On when using Automation.
microsoft will not block it.

 

[Lschai]

sorry, just tested it and it did replace the text i sought for.

You‘re right, it did work now for me as well! Thanks a lot for your help.

 

[arnelgp]

 

[Lschai]

I have a followup question though if you don‘t mind - would it be possible to change this code so that the header of any document will be changed as well? Right now it‘s only changing the text.

 

[Isaac]

This should have been posted in Excel or Word. A lot of AWF members "watch" new threads based on a variety of metrics, but most notably, which Forum it is in, so it's helpful to put it in the correct one.