Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel automating Word (Office XP SP-2)

    I'm trying to use an Excel worksheet to feed a Word document using Word's Find / Replace feature. I have referenced Word's object library from the VBA Editor in Excel. This macro bombs on the line .Replacement.Text=AcctNo line with the error mesage "The 'Replacement' method failed on the 'Find' object." Any suggestions greatly appreciated!

    Sub Ticket()
    Dim Name, AcctNo, Address As String
    Dim wdApp As Word.Application

    AcctNo = ActiveCell.Value
    Name = ActiveCell.Offset(0, 1).Value
    Address = ActiveCell.Offset(0, 2).Value

    Set wdApp = New Word.Application

    With wdApp
    .Visible = True
    .Documents.Open Filename:="Cocuments and SettingsKJMMy DocumentsTicket Select.doc"
    With .Selection.Find
    .ClearFormatting
    .Text = "Acct"
    .Replacement.Text = AcctNo
    .Replacement.ClearFormatting
    End With
    .Selection.Find.Execute Replace:=wdReplaceAll
    .Selection.HomeKey Unit:=wdStory
    .ActiveDocument.SaveAs ("Cocuments and SettingsKJMMy DocumentsTicket kjm.doc")
    .Quit
    End With
    Set wdApp = Nothing
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel automating Word (Office XP SP-2)

    This appears to be a known problem - see <!mskb=292744>Microsoft Knowledge Base Article 292744<!/mskb>. The recommended solution is to use late binding:

    Dim wdApp As Object ' instead of New Word.Application
    Set wdApp = CreateObject("Word.Application")

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel automating Word (Office XP SP-2)

    That did it! Thank you for searching for this Knowledgebase article.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel automating Word (Office XP SP-2)

    Do you still need help? I don't see an attachment.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel automating Word (Office XP SP-2)

    > Do you still need help? I don't see an attachment.

    After typing "I have no idea why it is suddenly failing.", I thought to reload Word - and behold and lo! the problem disappeared.
    Based on the earlier part of this thread I figured that a part of Word had dropped through a crack in RAM, and I got on with the other six things I'm doing today.

    Hans, I'm sorry to have led you down the garden path.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel automating Word (Office XP SP-2)

    (Posted For Your Information; this is the same error message but solely within Word, not Word/Excel)

    Hmmm. Oh Great Guru.

    Thanks for the MSKB feed. I have run into "method homekey of object selection failed" in a simple Word document.

    It's an application i run about every two weeks. I resurrected it, opened a document, and tried to jump to the start of the document with "Selection.HomeKey Unit:=wdStory". It failed!

    After moving the cursor, trying again, cursoring, i created an empty document and pasted some test. Same deal. I use "Selection.HomeKey Unit:=wdStory" all the time - at least I did until I found Range Objects & Collections.

    I have no idea why it is suddenly failing.

    Reloading Word seems to have cleared the problem.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel automating Word (Office XP SP-2)

    That's OK.

    When the cat has been prepared, the meal is ready

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel automating Word (Office XP SP-2)

    > When the cat has been prepared, the meal is ready

    Not sure whether this is a dangerously bad translation or a major culinary faux paw. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    When the yowling stops, the cat is sated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •