Using Excel to run a Word Macro

I have an excel document that lists all of the documents in a given directory/folder. The file path is saved into column B. I have a macro that opens the document who's file path is in column B2.

I have a WORD document that copies the header and footer from a template document (open in the background)into a letter that is open (macro run from the letter which is the active document).

Both macros work independently - BUT when I run the macro from Excel and open the word document (a letter) the Word Macro will not run automatically - when I do run the word macro manually it then doesnt find the template file (still open in the background).

The EXCEL macro: Sub automateword() Dim strFolder As String

strFolder = Range("B2").Value
strDoc = Range("C2").Value
Set WordApp = CreateObject("word.Application")

WordApp.Documents.Open strFolder
WordApp.Visible = True
WordApp.Activate

DoCmd.RunMacro "Update"
Application.Run (Update)

WordApp.Documents.Save
'WordApp.Quit
End Sub
The WORD macro: Sub Update()

Application.Templates.LoadBuildingBlocks

Letter = ActiveDocument & " [Compatibility Mode]"
Template = "Rebranded.doc" & " [Compatibility Mode]"

'Activate Letter Template and copy header
Windows(Template).Activate
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.WholeStory
Selection.Copy
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

'Activate Letter to amend and Paste Header
Windows(Letter).Activate
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.WholeStory
Selection.Paste
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

'Activate Letter Template and copy footer
Windows(Template).Activate
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
Selection.WholeStory
Selection.Copy
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

'Active Letter to amend and Paste Footer
Windows(Letter).Activate
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
Selection.WholeStory
Selection.Paste
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

End Sub
Any comments or help would be greatly appreciated - I have over 1000 documents that need amending.

Thanks

That works perfectly! Thank you so much - much better and quicker than what I was trying to do!

One last question - I want the macro to search for text in the documents and replace it;
Eg - if Tom is in the letter change it to Mike.

I have the code:
Set wdDocTgt = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
With wdDocTgt
'HERE
'Search letter for text and replace
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "Tom" 'Text to search for
.Replacement.Text = "Mike" 'Text to replace with
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
'HERE
.Close SaveChanges:=True
End With

Just recorded it so sorry for the beginners attempt.

Where would I enter this code into your macro to make it work?

Thanks again.

Tom

Thank you - are you able to help with my additional question please?

Thanks.

I have found a final issue with how the code runs - everything is perfect apart from:
Some of the footers have a simple page x of y. The footer is not copied over to these pages (which is correct) however for odd number pages the page x of y is removed. Is there anyway to add this?

Thanks

Hi,

I am trying to add the the above macro the ability to turn on 'Track Changes' and then before the document is saved change the view to 'Final' - this is to track the changes that the macro makes but the tracking is only visible IF the user changes the view.

My code is:
wdDocTgt.TrackRevisions = True
wdDocTgt.TrackMoves = True
wdDocTgt.TrackFormatting = True

I have entered this after the With wdDocTgt after it west the value of wdDocTgt near the top of the code.

The problem part I am having is changing the view of the code:
I have added:
With wdDocTgt.View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With

wdDocTgt.TrackRevisions = False
wdDocTgt.TrackMoves = False
wdDocTgt.TrackFormatting = False

Just before the End If's before the .close line.
The problem appears to be the .view object with the wdDocTgt variable.

Any suggestions on how to correct this please?

Thanks.