Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Running Word VBA From Excel (VBA/Excel-Word/2000)

    I need to collect data from Word documents and save it in an Excel workbook. The approach taken was to identify the Word document filename in one of the workbook cells then run an embedded Excel macro which in turn runs the Word macro on the identified document. This is only the second time that I have ventured into cross-application coding, so I am a tad hesitant. Nonetheless everything seemed to go well until we encountered Word documents that didn't conform to our expectations (a problem that would be easily overcome with a Find activity). Or so I thought.
    The .Selection.Find.ClearFormatting line of code causes Excel to crash. I have provided thee offending snippet of code with some surrounding context, and attached the complete module in case the problem isn't obvious to those more astute than me.
    <pre>' Switch to Word
    On Error Resume Next
    Set wrdApp = GetObject(, "Word.Application")
    On Error GoTo 0

    If wrdApp Is Nothing Then
    MsgBox "Exiting, Word is not open"
    End
    End If

    Dim oDoc As Document
    Dim RevFileOpen As Boolean
    Dim StPt As Long ' Start of any blocked text or the insertion point if none blocked
    With wrdApp
    ' |
    ' |
    ' More code
    ' |
    ' |
    If Para = "" Then ' the para no is hard coded
    ' The following line of code invariably crashes Excel
    .Selection.Find.ClearFormatting
    With .Selection.Find
    .Text = "^w"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    .Selection.Find.Execute
    ' |
    ' |
    ' More code
    ' |
    ' |
    End With
    Set wrdApp = Nothing
    Call Populate_Report
    End Sub

    </pre>


    Any guidance will be greatly appreciated.
    Regards
    Don

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

    Re: Running Word VBA From Excel (VBA/Excel-Word/2000)

    There are some references to Word objects that are not related to the wrdApp object. This is dangerous - sometimes, VBA resolves such references correctly, but it may cause problems.

    <table border=1><td>Your code</td><td>Should be</td><td><code>For Each oDoc In Documents</code></td><td><code>For Each oDoc In .Documents</code></td><td><code>If ActiveDocument.Name <> ReviewFile Then</code></td><td><code>If .ActiveDocument.Name <> ReviewFile Then</code></td></table>
    I don't know if this causes the crash on .Selection.Find.ClearFormatting, but you should correct this first.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running Word VBA From Excel (VBA/Excel-Word/2000)

    Thanks Hans
    That didn't do the trick, but at least I'm a little more astute (less dumb?), I can see why the code corrections you recommended are best. I think I see why I was able to get away with the missing dots.
    It looks as if I'm must take an approach other than the Find. As Grandma used to say, "To kill a cat, it's not necessary to bore it to death".
    I intend moving the cursor one character at a time and testing the character immediately to the right for white space. By the way, are you aware of an equivalent command/character string to the "^w" used in the Word search for "white space"?
    Thanks again for the insight,
    Regards
    Don

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

    Re: Running Word VBA From Excel (VBA/Excel-Word/2000)

    The Range and Selection objects in Word have methods to move to the next word - perhaps you can use those. Look up Move and MoveRight in the VBA help for Word.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running Word VBA From Excel (VBA/Excel-Word/2000)

    Hans
    I expressed myself poorly. I'm looking for an alternative to:

    <UL><LI>If Asc(Selection) = 9 Or Asc(Selection) = 32 Or Asc(Selection) = ... Then [/list] In other words, I wonder if there's a VBA equivalent to Word's ^w. I'm just concerned that though I believe I have anticipated all possible ways that an author can introduce whitespace, the one I miss will catch me out at the least acceptable time.

    Thanks again.
    Regards
    Don

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

    Re: Running Word VBA From Excel (VBA/Excel-Word/2000)

    I don't think there is a VBA equivalent for ^w. That's why I suggested letting Word figure out where the next word begins.

    By the way, don't forget Asc(Selection) = 160 (non-breaking space)

  7. #7
    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: Running Word VBA From Excel (Excel-Word/2000)

    A small shortcut: if you assemble a string of all the whitespace characters (concatenate them together) you can use InStr to test whether the Selection.Text is among them.

    You also could use the Scripting library's RegEx (regular expressions) object.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running Word VBA From Excel (VBA/Excel-Word/2000)

    Thanks Hans
    I certainly will use 160, and any others I can ferret out. I cannot use the next word capability because I will be trying to jump over paragraph numbers in the form of "3.2.1" and I expect the periods will be considered words.
    Regards
    Don

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

    Re: Running Word VBA From Excel (VBA/Excel-Word/2000)

    If you mean numbering applied by Word's Bullets and Numbering dialog, you won't "see" it as you move from character to character; if the numbering has been typed in manually, you will, and then the periods would be interpreted as words indeed.

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running Word VBA From Excel (Excel-Word/2000)

    Thanks Jefferson
    I need to learn more about the Scripting Library.
    Regards
    Don

Posting Permissions

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