Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SpaceBefore in VBA (Word 2000 SP2)

    I created a report in Access that would list participant's contact information and set it to hide information that the participant doesn't want to show. I.e; if the participant wants to show only name and Email address, then the report would "hide" other contact information. Unfortunately, for some reason, it would "shrink" the section but leave a wide gap between name and Email address. Since I needed to "publish" the report to Word for further editing (for various formats not supported by Access), I had an idea that I could somehow create a visual basic code that would automatically run when the document is open and it would do this:

    If ActiveDocument.Paragraphs.SpaceBefore = 16.1 Then
    ActiveDocument.Paragraphs.SpaceBefore = 4.3
    End If

    I wanted to check each paragraph and try to catch those "gaps" between name and other contact information. I discovered that the "gap" was exactly 16.1 points apart between name and Email address (as an example). I wanted the VBA code to find all paragraphs with 16.1 in it and change it to 4.3 points. However, it didn't seem to work, although if I changed to > 16.1, it would then convert ALL paragraphs to 4.3, which also changes those I want to keep (spacing between two names).

    Any idea why my code didn't work?

    Thanks in advance.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: SpaceBefore in VBA (Word 2000 SP2)

    I'm guessing that the paragraph spacing is not exactly 16.1 but something close enough that it gets displayed that way. Maybe casting the net a little wider would catch those fish
    <pre>For each aPara in ActiveDocument.Paragraphs
    If aPara.SpaceBefore > 16 and aPara.SpaceBefore < 17 then
    aPara.SpaceBefore = 4.3
    End If
    Next aPara</pre>



    Another possibility is that you are using variables defined as the wrong type. This is a guess as your code posted isn't using variables.

    If the file has to hit Word anyway can you use mail merge to bring the report up in Word without using the Access Report at all.
    You would get a lot more formatting control that way.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    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: SpaceBefore in VBA (Word 2000 SP2)

    <UL>If ActiveDocument.Paragraphs.SpaceBefore = 16.1 Then
    ActiveDocument.Paragraphs.SpaceBefore = 4.3
    End If[/list]You cannot reliably update an entire collection this way. Instead, do this:
    <UL>Dim para as Paragraph
    For Each para in Activedocument.Paragraphs
    if para.SpaceBefore = 16.1 Then
    para.SpaceBefore = 4.3
    End If
    Next[/list]to check each paragraph in the document individually. Hope this helps. (P.S., I just typed that without testing it, but it should be really close!)

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SpaceBefore in VBA (Word 2000 SP2)

    You both guys gave me the answer I needed! Grin. While I was busy trying to describe the problem, I competely forgot to mention that the problem I was actually having was that it would fix the first paragraph but then skip the rest of the paragraphs. Now, I know I need to add "Next Para" command line. I'll check it out via Word help files for specifics... <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

Posting Permissions

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