Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Executing a Word97 Find from Excel

    Hi! I've been trying to control Word97 from Excel through a macro. Basically, I start Word, make it visible, then open the document - so far so good. But when I try to execute a wildcard find/replace on the document, nothing happens! At least, it *works* but no replacements are made! If I try the same thing directly in Word, of course that's fine! The code I use in Excel is below:

    Set objWd = New Word.Application
    objWd.Visible = True
    On Error Resume Next
    strFile = strDirectory & "" & strName & ".HDF"
    objWd.Documents.Open (strFile)
    If Err.Number = 0 Or 438 Then
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find.Execute
    .MatchWildcards = True
    .Text = "!E?LIB"
    .Replacement.Text = "!E0LIB"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    objDoc.Close (wdSaveChanges)
    End If
    objWd.Quit

    Has anyone any idea why this find/replace is not working? I can assure you that the find text (!ExLIB where x is any character) is definately in the file (I can replace it easily directly in Word). And has anyone any idea why I have to allow error code 438 as well as zero?

    TIA

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Executing a Word97 Find from Excel

    Hi Keith,

    I haven't had a chance to test this code, but one or two things stick out:

    Toward the end, you have "objDoc.Close", but there doesn't seem to be any mention of it before that - so I'm surprised that isn't causing an error.

    If you try adding

    Dim objDoc As Word.Document

    and then

    Set objDoc = objWd.Documents.Open (strFile)
    With objDoc.Contents.Find
    'etc.

    - this might help ensure the find is running in the right place.

    Another thing would be to try stepping through the code and try to see what is happening when it gets to the Word doc.

    Just a couple of small things - hope these help.

    Gary

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing a Word97 Find from Excel

    Do you have a reference set in Word to the Word object library? (Tool,s References, Microsot Word xx Object Library)?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing a Word97 Find from Excel

    Hi Gary,
    Thanks for the reply. I'm sorry if I wasn't too clear in my original post. I have declared objDoc correctly and as I said, it is read correctly, and closes nicely too. It's just the Find/Replace that doesn't work! I'm looking for a wildcarded text of '!E?LIB' (no quotes), which I think means any text of the format '!ExLIB' (again no quotes) where x is any character, replacing this with a standard '!E0LIB' (no quotes) - but nothing happens! If I stop just after the failed Excel-controlled Replace, and try it directly in the Word application, it works correctly! So my problem is *just* with the Excel-controlled Selection.Find.Execute! Any ideas?

    TIA again

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing a Word97 Find from Excel

    Hi Geoff,
    Yes I do have a reference set to the Word library, and the document is referenced properly, and reads in without problem. It's simply that the Selection.Find.Execute (controlled from Excel) doesn't work, even though I can stop the run just after that and perform Find/Replace directly in Word without a problem. I wondered of it had something to do with wildcards, as a straight find/replace works fine. The find text of '!E?LIB' (no quotes) is meant to be text of the format '!ExLIB' (again no quotes) where x is any character, but this doesn't work at all. So is it wildcard-specification problems, or the fact that it's being controlled from Excel, or what? Just to restate, there are *no* errors reported, it's simply that the Selection.Find.Execute has no effect!

    TIA again

  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Executing a Word97 Find from Excel

    Hi Keith,

    From the code posted, it's impossible to tell that the objDoc is being assigned to anything - so you did throw us a curve there!<g>.

    Wildcards are a weak spot for me so not much help, sorry. The first (and only) thing I'd suggest is: first try the identical code, but with a regular rather than a wildcard search. This will help narrow down whether the issue is with the wildcards or with the search itself.

    Gary

  7. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Executing a Word97 Find from Excel

    I lied; here's a second suggestion: you might try qualifying all of the Selection.Find code with objWd - i.e. "objWd.Selection.Find" etc. - because both the Excel and Word applications have Selection objects, this further qualification may be necessary.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing a Word97 Find from Excel

    Wooh! That did it! Now, I'm not entirely sure why, but it's certainly made THE difference! I had initially written
    Set objDoc = ObjWd.ActiveDocument, followed by
    objDoc.Selection.Find etc. but this was what wasn't working. However, now I've omitted the objDoc level and just coded objWd.Selection.Find and so on, it's fine!

    Thanks Gary! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  9. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Executing a Word97 Find from Excel

    Great!
    The reason it made a difference is that Selection is a child of the application object (Word), and not the document object.

    I had to make a guess because you didn't post the relevant parts of your code. So next time don't leave us guessing - don't edit out parts of the procedure - they may turn out, as in this case, to be the key parts. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing a Word97 Find from Excel

    What did I leave out? My original code showed me setting up a Word application object (objWd), and then I just said Selection.Find etc. The cure was to put objWd in front of the Selection, that's all, i.e. objWd.Selection.Find etc. The only confusion may have been to show the reference to the document object, objDoc, in the Close statement further down, surely?

    Anyway, it's still thanks for a good suggestion!

  11. #11
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Executing a Word97 Find from Excel

    Keith,

    Sorry - didn't mean to make you feel you were on the firing line! <img src=/S/dragon.gif border=0 alt=dragon width=17 height=15>

    It was the mention of the
    <hr>"I had initially written
    Set objDoc = ObjWd.ActiveDocument, followed by
    objDoc.Selection.Find etc." <hr>
    - which was pertinent, and not in the previous posts: how can we call 'em when we can't see 'em? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Glad it worked out though,
    Gary

  12. #12
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing a Word97 Find from Excel

    Hi Gary,

    Oh, I see now where I confused you! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I had initially posted code that didn't actually mention objDoc except for the residual Close. So when I said in a later post that I had written 'objDoc.Selection.Find', I was lying! That was an even earlier incarnation that didn't work either! (If you follow me!) Not to worry, no offense taken or meant, and you still managed to spot the problem! Thanks again! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Executing a Word97 Find from Excel

    > didn't mean to make you feel you were on the firing line!
    That's MY job ......(g!)

Posting Permissions

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