Find/replace in Word from Excel VBA - not working (possible Object Library problem)

[oleppedersen]

Hi all,

have searched a lot to find a solution, hope some can verify what my problem is and how to solve it.

I want to search/replace text in a Word doc from Excel. This should be plain sailing, multiple solutions exist online.

However, I can only find the text, and fill the replace dialogue box - it does not do the actual replacing.

After searching, it seems the problem is that I need to add Microsoft Word 16.0 Object Library in my reference section in my VBA. However - here I run into a message saying that the name is in conflict with "existing module, project or object library".

The libraries I have activated are: VBA for Appliations, Excel 16.0 Object Library (these two I am not allowed to switch off), Office 16.0 Object Library and OLE Automation. Switching off the latter two does not help; the error message still exists.

It seems either:
1. The reference does not matter and I have messed up the code.
2. The reference is needed, in which case I do not understand how to proceed? I use Win/Office 365 with a continuously updated Office pack.

I suspect the problem is 2), which is why I ask for help here.

Below is code for reference:

Sub DocSearch()
Dim wdApp As Object, wdDoc As Object
Set wdApp = CreateObject("word.application")
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open("C:\Users\OPP PC 2016\Dropbox\jobb\Visual Basic\wordtest.docx")
With wdDoc.Content.Find
.Text = "Date:"
.Replacement.Text = "Datetest"
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
Set wdApp = Nothing: Set wdDoc = Nothing
End Sub

 

[Yongle]

Do I need to add Microsoft Word 16.0 Object Library in my reference section in my VBA?

Yes you need it if you want to run macro from Excel- although as a workaround you could modify the macro and run it from Word.
Just so that you know, I tested your code and it works perfectly but adding the Reference was necessary to get it working.

I have 2 suggestions for you
1. Hopefully you have simply corrupted the workbook somehow - create a new workbook and see if it lets you add the reference in there
2. If above does not work, I would re-install Office365 which it is much quicker than messing around and is (ALMOST!) guaranteed to fix your problem

 

[oleppedersen]

Yes you need it if you want to run macro from Excel- although as a workaround you could modify the macro and run it from Word.
Just so that you know, I tested your code and it works perfectly but adding the Reference was necessary to get it working.

I have 2 suggestions for you
1. Hopefully you have simply corrupted the workbook somehow - create a new workbook and see if it lets you add the reference in there
2. If above does not work, I would re-install Office365 which it is much quicker than messing around and is (ALMOST!) guaranteed to fix your problem

Thanks for replying; after testing today on my work computer I figured out what caused the problem: I named the Module where I put the code "Word". I tried the same at my work computer, AFTER I had activated the Word reference, and was not allowed to name the module "Word". So a stupid user mistake there, though to me it was a bit strange that I could not name a module anything I would like; one would think that the module names are only for a user reference, and that they have unique IDs in the background used by VBA. But I probably do not know too much of how the framework works anyway.

So, for future reference: Be aware how I name my modules in VBA

 

[Yongle]

Thanks for the feedback - I do not think I would have spotted that even if I was looking at your actual file! And (thanks to you) I have an amended rule to follow when naming procedures, constants, variables, and arguments...

Old Rule
Avoid using names that are the same as functions, statements, and methods
Amended rule
Avoid using names that are the same as functions, statements, methods and applications

 

[Macropod]

No, you don't need to set a reference to Word, since your code is using late binding. The problem, though, is that you've also tries to use a Word constant (wdReplaceAll) with the late binding. If you'd set Option Explicit for your code module, the error would have been trapped. Change:
wdReplaceAll
to:
2

 

[oleppedersen]

Thank you! I did not realize this. I was just using some test code found online. I will prefer to use Word constants and will therefore be more thorough when coding, using early binding.

No, you don't need to set a reference to Word, since your code is using late binding. The problem, though, is that you've also tries to use a Word constant (wdReplaceAll) with the late binding. If you'd set Option Explicit for your code module, the error would have been trapped. Change:
wdReplaceAll
to:
2

 

[oleppedersen]

A small step for mankind, but still