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

oleppedersen

New Member
Joined
Mar 23, 2013
Messages
13
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Last edited:
Upvote 0
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 :)
 
Upvote 0
(y)
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top