Find/Replace Macro

[DavetheVBNewb]

Hey Everyone,

I currently have a macro that runs a mail merge from a Access database to a word template. After merging, the new document has section breaks between each record set. A simple find/replace in word will replace these with page breaks, but I'm having trouble making this work from Access. Here's my code:

---
templatePath = "path.doc"
sourceQuery = "Query"
wordDoc.Visible = True
wordDoc.Documents.Open templatePath

[Code for merge]

With wordDoc.Selection.Find
.Text = "^b"
.Replacement.Text = "^m"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
wordDoc.Selection.Find.Execute Replace:=wdReplaceAll

Exit Sub

---
There aren't any errors and when the code finishes the section breaks are highlighted but unchanged. Any Ideas?

Thanks,
Dave

 

[darbid]

Hello DTVBN,

The best way I find out generally how to do automation is by recording an automation with the macro record.

This is slightly different to yours

.Text = "^b"
        .Replacement.Text = "^m"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchByte = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = False
        .MatchFuzzy = False

If this does not help then get one of the documents you are trying to change and do the replace manually. Does it work???

 

[darbid]

sorry added it twice - the forum is not working so fast recently and times out a lot.

 

[DavetheVBNewb]

Darbid,

Thanks for the suggestion. From what I can tell, the code is more or less the same as mine in terms of functionality and I'm guessing this part of the code is exceptable. I think the problem has something to do with the last line "wordDoc.Selection.Find.Execute Replace:=wdReplaceAll".

I would have tried the macro recorder but Access doesn't have one. The code I gave previously is based on a recording from Word, though doesn't seem to want to work from Access VBA.

 

[darbid]

I would have tried the macro recorder but Access doesn't have one. The code I gave previously is based on a recording from Word, though doesn't seem to want to work from Access VBA.

That is exactly what I do I record the macro in Word, then add the application object to the front to make it acceptable to access.

So in Word can you manually do this replace? I mean can you manually do a find ^b and replace with ^m and it works with your documents.

wordDoc.Selection.Find.Execute Replace:=wdReplaceAll".

As I am a tester and not a knower what is "worddoc" is that the document object or the application object? If it is the document then I think selection comes from the application. Can you test that?

 

[DavetheVBNewb]

Yes, the find and replace works with "^b" and "^m" manually. As a macro script it also works (in the form posted without referencing "wordDoc").

Sorry, I meant to include that line in the first bit of the script I posted (it's included in the VBA, I just forgot to copy it). Prior to the code is the line:

Set wordDoc = CreateObject("Word.Application")
Dim templatePath As String, sourceQuery As String
templatePath = "path.doc"
sourceQuery = "Query"
wordDoc.Visible = True
wordDoc.Documents.Open templatePath
...

I'm guessing this is the application object but am new to VBA so I may have the terminology wrong. Is this what you meant?

 

[DavetheVBNewb]

Darbin,

Figured it out...apparently the wdFindContinue and wdReplaceAll statements don't work from Access VBA. They needed to be replaced with "1" and "2", respectively. Go figure. Thanks again for the assistance and the follow ups.

Dave