Help with find and replace macro

[brymail]

Hi All,

I am having a heck of a time trying to figure out what I am doing
wrong. I recently upgraded to Office 2007 and my previous macro is
behaving oddly. I am trying to go through and find and replace key
words with information obtained from a database such as name, file
number, etc. I have different headers and footers so my macro goes
through each of the stories and separately replaces in each one. I
had no problems in Word 2003 but in Word 2007 it is hit or miss.

Here is the guts of the macro

Sub Test()
Dim myRange As Range
Dim myStoryRange As Range
Dim FindReplaceArray(20, 2) As Variant
Dim i As Integer
Dim ArraySize As Integer

FindReplaceArray(0, 0) = "FILENAME"
FindReplaceArray(0, 1) = "John Doe"
FindReplaceArray(1, 0) = "FILENO"
FindReplaceArray(1, 1) = "100000"
FindReplaceArray(2, 0) = "DATE"
FindReplaceArray(2, 1) = Format(Date, "mmmm d, yyyy")
ArraySize = 2

For Each myStoryRange In ActiveDocument.StoryRanges
For i = 0 To ArraySize
With myStoryRange.Find
.Text = FindReplaceArray(i, 0)
.Replacement.Text = FindReplaceArray(i, 1)
.MatchCase = True
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
Next i
While Not (myStoryRange.NextStoryRange Is Nothing)
Set myStoryRange = myStoryRange.NextStoryRange
For i = 0 To ArraySize
With myStoryRange.Find
.Text = FindReplaceArray(i, 0)
.Replacement.Text = FindReplaceArray(i, 1)
.MatchCase = True
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
Next i
Wend
Next myStoryRange

End Sub

I actually load the FindReplaceArray with data from a database but
have simplified here. I created a document with FILENAME, FILENO and
DATE in the main text, front page headers and footer and second page
headers and footers. When I run the macro, the main text is replaced
perfectly. In the first page header, the FILENAME and DATE are
replaced, but not the FILENO. In the first page footer and second
page header and footer, only the FILENO is replaced.

Does any of this make sense or can you offer any suggestions?

Thanks,

Bryan

 

[BigBry]

Hi All,

I am having a heck of a time trying to figure out what I am doing
wrong. I recently upgraded to Office 2007 and my previous macro is
behaving oddly. I am trying to go through and find and replace key
words with information obtained from a database such as name, file
number, etc. I have different headers and footers so my macro goes
through each of the stories and separately replaces in each one. I
had no problems in Word 2003 but in Word 2007 it is hit or miss.

Here is the guts of the macro

Sub Test()
Dim myRange As Range
Dim myStoryRange As Range
Dim FindReplaceArray(20, 2) As Variant
Dim i As Integer
Dim ArraySize As Integer

FindReplaceArray(0, 0) = "FILENAME"
FindReplaceArray(0, 1) = "John Doe"
FindReplaceArray(1, 0) = "FILENO"
FindReplaceArray(1, 1) = "100000"
FindReplaceArray(2, 0) = "DATE"
FindReplaceArray(2, 1) = Format(Date, "mmmm d, yyyy")
ArraySize = 2

For Each myStoryRange In ActiveDocument.StoryRanges
For i = 0 To ArraySize
With myStoryRange.Find
.Text = FindReplaceArray(i, 0)
.Replacement.Text = FindReplaceArray(i, 1)
.MatchCase = True
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
Next i
While Not (myStoryRange.NextStoryRange Is Nothing)
Set myStoryRange = myStoryRange.NextStoryRange
For i = 0 To ArraySize
With myStoryRange.Find
.Text = FindReplaceArray(i, 0)
.Replacement.Text = FindReplaceArray(i, 1)
.MatchCase = True
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
Next i
Wend
Next myStoryRange

End Sub

I actually load the FindReplaceArray with data from a database but
have simplified here. I created a document with FILENAME, FILENO and
DATE in the main text, front page headers and footer and second page
headers and footers. When I run the macro, the main text is replaced
perfectly. In the first page header, the FILENAME and DATE are
replaced, but not the FILENO. In the first page footer and second
page header and footer, only the FILENO is replaced.

Does any of this make sense or can you offer any suggestions?

Thanks,

Bryan

 

[BigBry]

I have been puzzling over this all day. I have tried to simplify it and see
if I can figure out what the problem is. I have created a new document with
a single header and footer. The main text, header and footer have the word
FILENAME in them. I have simplified the macro to

Sub Test()

Dim myStoryRange As Range

For Each myStoryRange In ActiveDocument.StoryRanges
With myStoryRange.Find
.Text = "FILENAME"
.Replacement.Text = "John Doe"
.MatchCase = True
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
Next myStoryRange

End Sub


When i run this, only the FILENAME in the main text portion is replaced.
The FILENAME in the header and footer are not replaced. I have used msgbox
to verify that myStoryRange is including the header and footer and it is.

Any suggestions?

Thanks,

Bryan

 

[Tony Jollans]

Well done for investigating! However the code you have posted here works for
me in Word 2007. I just did a very simple test - new document, typed
FILENAME into the three places, cut and pasted and ran your code. Could
there be something unusual about your test document? Are there any more
details you could provide?

 

[BigBry]

Hi Tony

Thanks for your help. I am glad to know it is supposed to be working! Here
is where I am at now:

Windows XP
Office 2007 Pro

I have created a brand new document. Office button - new - blank document
I have inserted the word FILENAME in the main text, header and footer.
I have cut and pasted the below simplified code for sub Test into the Visual
Basic editor for this document
I then run the code by pressing the run sub key in the visual basic editor
The main text and the header are replaced. The footer is not replaced.

I then added a msgbox statement to display myStoryRange,
myStoryRange.StoryType and myStoryRange.StoryLength just before the With
statement to confirm that it is cycling through each of the story types.

It confirms that it is going through 7 different story types in the
following order.

Story Type Contains Length
wdMainTextStory FILENAME 9
wdPrimaryHeaderStory FILENAME 9
wdPrimaryFooterStory FILENAME 9
wdFootnoteSeparatorStory odd character 2
wdFootnoteContinuationSeparatorStory odd character 2
wdEndnoteSeparatorStory odd character 2
wdEndnoteContinuationSeparatorStory odd character 2

I then used the debug menu and stepped through the macro line by line. It
worked perfectly and replaced all three areas.

I then used the debug menu and placed the cursor at the next MyStoryRange
line and had it run to cursor. I did this seven times for each of the story
types. It worked perfectly.

I then used the debug menu and placed the cursor at the end sub line and had
it run to cursor. Same problem with running the sub. The footnote is not
being replaced.

I also added a line myStoryRange.select immediately before the With
statement and it worked correctly.

I am completely confused. Any advice will be much appreciated.

If it is important, the references available for this project are:
visual basic for applications
microsoft word 12.0 object library
OLE Automation
Normal
Microsoft Office 12.0 Object library

Thanks,

Bryan

 

[Tony Jollans]

Hi Bryan,

Firstly you are now saying that it works in the Header and Body whereas
before it was only in the Body - has that change occurred?

When code works by stepping through but not when let run it is my experience
that breakpoints clear whatever 'lock' may be in place - the trick is
identifying it. What are you doing in terms of editing the document when you
run? Not that I think it ought to make a difference, but nothing else
springs to mind immediately.

 

[BigBry]

Hi Tony!

It is now replacing the header. The difference is that I only have FILENAME
in the header. Before I had FILENAME and DATE in the header. For some
reason, when it is only 1 word in the header it will replace correctly.

Also, if I have just a footer, it will replace in the footer. But when I
add the header, it will only replace the header and ignore the footer.

OK, spoke too soon! Thank you for your suggestion of a "lock". I went and
looked at the add ins for Word and disabled all 3 of them - Business Contact
Manager for Outlook, Person Name (Outlook e-mail recipients) and a Microsoft
Small Business Accounting PayPal addin.

It now works perfectly. By narrowing it down, the problem addin was
Microsoft.BusinessSolutions.SmallBusinessAccounting.Addon.Paypal.UI.WordAddin.Connect Friendly Name
Publisher - Microsoft Corp.
Location - Microsoft Small Business\Small Business Accounting
2007\PayPalWordAddinShim.dll

I have disable this and it works great.

I have tried it with my old document and it is also working again.

Thanks for all of your help!!!!! This was driving me up the wall. You have
saved me from a mental breakdown - at least for this week

Thanks again and have a great weekend!

Bryan