Writing Data from Excel to Word

Question

• I want to use Excel to store "tag names" in column A and their associated "replacement text" in Column B. When the code runs, it needs to collect each tag, one at a time (row by row), search an entire Word document for those words, and replace them with their corresponding replacements.
• I noticed the special tags in the headers and footers weren't being replaced. I turned to this article (http://word.mvps.org/faqs/customization/ReplaceAnywhere.htm) and found that working with a range of ranges (or cycling through all available Story Ranges in the document) I was able to do this.
• I improved my code, as recommended in the link above and it worked, so long as my code was embedded in my "Normal" Word file, thereby using my VBA code from Word to operate on another Word document. However, the goal is to use VBA Excel to operate the replacements while reading an Excel file.
• When I moved the code to Excel, I'm getting hung up on an Automation error which reads,

"Run-time error '-2147319779 (8002801d)': Automation error Library not registered.".

• I've looked for answers from reviewing the Registry to using "Word.Application.12" in place of "Word.Application".

I have a Windows 7, 64-Bit machine, with Microsoft Office 2007. I have the following libraries selected:

• Excel:

  • Visual Basic For Applications
  • Microsoft Excel 12.0 Object Library
  • OLE Automation
  • Microsoft Access 12.0 Object Library
  • Microsoft Outlook 12.0 Object Library
  • Microsoft Word 12.0 Object Library
  • Microsoft Forms 2.0 Object Library
  • Microsoft Office 14.0 Object Library

• Word:

  • Visual Basic For Applications
  • Microsoft Word 12.0 Object Library
  • OLE Automation
  • Microsoft Office 12.0 Object Library

I have no issues with operating inside of Excel with regard to VBA. Normally, I will be passing a set of strings to this function, but for now, I have embedded the strings inside of the function, as if I am only planning on swapping one string (for any number of instances), with another predetermined string.

Function Story_Test()
Dim File As String
Dim Tag As String
Dim ReplacementString As String

Dim a As Integer

Dim WordObj As Object
Dim WordDoc As Object
Dim StoryRange As Word.Range
Dim Junk As Long

Dim BaseFile As String

'Normally, these lines would be strings which get passed in
File = "Z:\File.docx"
Tag = "{{Prepared_By}}"
ReplacementString = "Joe Somebody"

'Review currently open documents, and Set WordDoc to the correct one
'Don't worry, I already have error handling in place for the more complex code
Set WordObj = GetObject(, "Word.Application")
BaseFile = Basename(File)
For a = 1 To WordObj.Documents.Count
    If WordObj.Documents(a).Name = BaseFile Then
        Set WordDoc = WordObj.Documents(a)
        Exit For
    End If
Next a

'This is a fix provided to fix the skipped blank Header/Footer problem
Junk = WordDoc.Sections(1).Headers(1).Range.StoryType


'Okay, this is the line where we can see the error.
'When this code is run from Excel VBA, problem.  From Word VBA, no problem.
'Anyone known why this is???
'***********************************************************************
For Each StoryRange In WordObj.Documents(a).StoryRanges
'***********************************************************************
    Do
        'All you need to know about the following function call is
        ' that I have a function that works to replace strings.
        'It works fine provided it has valid strings and a valid StoryRange.
        Call SearchAndReplaceInStory_ForVariants(StoryRange, Tag, _
          ReplacementString, PreAdditive, FinalAdditive)
        Set StoryRange = StoryRange.NextStoryRange
    Loop Until StoryRange Is Nothing
Next StoryRange

Set WordObj = Nothing
Set WordDoc = Nothing

End Function

Answer 1

For Each StoryRange In WordObj.Documents(a).StoryRanges

should probably be

For Each StoryRange In WordDoc.StoryRanges

since you just assigned that in the loop above.

Answer 2

For now, I will have to conclude, as I don't have the possibility of testing the contrary, that there is a difference between using Microsoft Office 12 Object Library in one VBA environment, and Microsoft Office 14 Object Library in another. I don't have the means/authorizations to change either, so I must conclude, for now that is, that the difference between the two is the culprit. So, if I was to go forward and expect different results, I would assume Microsoft Office 12 Object Library to be the correct library, where 14 has a few differences that I am not aware of.

Thank you to all who provided input. If you have any other suggestions, we can discuss and forward. Thanks!

Answer 3

This is to update a bunch of links spread over body & Headers footers. I didn't write this only from memory made a bunch of fixes, inclusions and tweaks. It shows you how to cover all the different sections and can easily be modified to work within your parameters. Please post your final code once done.

Public Sub UpdateAllFields()
Dim doc As Document
Dim wnd As Window
Dim lngMain As Long
Dim lngSplit As Long
Dim lngActPane As Long
Dim rngStory As Range
Dim TOC As TableOfContents
Dim TOA As TableOfAuthorities
Dim TOF As TableOfFigures
Dim shp As Shape
Dim sctn As Section
Dim Hdr As HeaderFooter
Dim Ftr As HeaderFooter

' Set Objects
Set doc = ActiveDocument
Set wnd = ActiveDocument.ActiveWindow

' get Active Pane Number
lngActPane = wnd.ActivePane.Index

' Hold View Type of Main pane
lngMain = wnd.Panes(1).View.Type

' Hold SplitSpecial
lngSplit = wnd.View.SplitSpecial

' Get Rid of any split
wnd.View.SplitSpecial = wdPaneNone

' Set View to Normal
wnd.View.Type = wdNormalView

' Loop through each story in doc to update
For Each rngStory In doc.StoryRanges
    If rngStory.StoryType = wdCommentsStory Then
        Application.DisplayAlerts = wdAlertsNone
        ' Update fields
        rngStory.Fields.Update
        Application.DisplayAlerts = wdAlertsAll
    Else
        ' Update fields
        rngStory.Fields.Update
    End If
Next

'Loop through text boxes and update
For Each shp In doc.Shapes
    With shp.TextFrame
        If .HasText Then
            shp.TextFrame.TextRange.Fields.Update
        End If
    End With
Next

' Loop through TOC and update
For Each TOC In doc.TablesOfContents
    TOC.Update
Next

' Loop through TOA and update
For Each TOA In doc.TablesOfAuthorities
    TOA.Update
Next

' Loop through TOF and update
For Each TOF In doc.TablesOfFigures
    TOF.Update
Next

For Each sctn In doc.Sections
    For Each Hdr In sctn.Headers
        Hdr.Range.Fields.Update
        For Each shp In Hdr.Shapes
            With shp.TextFrame
                If .HasText Then
                    shp.TextFrame.TextRange.Fields.Update
                End If
            End With
        Next shp
    Next Hdr
    For Each Ftr In sctn.Footers
        Ftr.Range.Fields.Update
        For Each shp In Ftr.Shapes
            With shp.TextFrame
                If .HasText Then
                    shp.TextFrame.TextRange.Fields.Update
                End If
            End With
        Next shp
    Next Ftr
Next sctn

' Return Split to original state
wnd.View.SplitSpecial = lngSplit

' Return main pane to original state
wnd.Panes(1).View.Type = lngMain

' Active proper pane
wnd.Panes(lngActPane).Activate

' Close and release all pointers
Set wnd = Nothing
Set doc = Nothing
End Sub