asked on

MS Access/Word VBA: How to remove blank lines created by blank MS Word bookmarks

Hello Team

I am currently creating a MS Word mail merge via MS Access vba.

I have the bookmarks set in a word document with the corresponding vba code running in MS Access by looping through a record set. For example:

If Not rs.EOF Then rs.MoveFirst

Do Until rs.EOF
wDoc.Bookmarks("CompanyName").Range.Text = Nz(rs!CNAME, "")
wDoc.Bookmarks("Line1").Range.Text = Nz(rs!Line1, "")
wDoc.Bookmarks("Line2").Range.Text = Nz(rs!Line2, "")
wDoc.Bookmarks("Line3").Range.Text = Nz(rs!Line3, "")....etc

All code is working correctly to produce each word and pdf document but what I require is, assistance with how to remove blank lines produced by blank fields passed from the recordset. For example, there are 6 bookmarks associated with the address detail but if bookmark 'Line3' is not populated then the address has a blank line space in between the other populated bookmarks.

Can you please advise on how to produce an address block without any blank bookmark line spaces appearing in between?

Thank you in advance.

Mark

crystal (strive4peace) - Microsoft MVP, Access

hi Mark (Dale?),

Rather than setting bookmarks for every possible field that might have a value, it is better to let Access do some combining.

For instance, if your lines are address lines and you have fields called Address1, Address2, and Address3 for the lines:

with rs
   sFullAddress = !Address1 & ( chr(13) + chr(10) + !Address2)  & ( chr(13) + chr(10) + !Address3)  & ""
end with
wDoc.Bookmarks("FullAddress").Range.Text =sFullAddress

Open in new window

chr(13) + chr(10) will make a new line. chr(13) is CR (carriage return) and chr(10) is LF (line-feed)

using + instead of & makes everything in parentheses Null if anything is Null ... so if Address2 is null, then it won't get the new line either

then, in Word, just use one bookmark for the Address. When Access pushes the values, it will include new lines where necessary

have an awesome day,
crystal

GrahamSkan

Word's Mail Merge does that automatically.

If you are designing your own mail merge functionality, I suggest that you replace your code lines that fill the relevant bookmarks with something like this

FillAddressLine wDoc, "CompanyName", Nz(rs!CNAME, "")

Open in new window

to use a procedure like this

Sub FillAddressLine(strBookMarkName As String, strAddressLineText As String)
    If Len(Trim(strAddressLine)) > 0 Then
        wDoc.Bookmarks(strBookMarkName).Range.Text = strAddressLineText
    Else
        wDoc.Bookmarks(strBookMarkName).Range.Paragraphs.First.Range.Delete
    End If
End Sub

Open in new window

crystal (strive4peace) - Microsoft MVP, Access

nice to know, Graham ~ I learn so much from you, thanks ~ crystal

Dale James

ASKER

Hello Crystal

Thank you for your response!

As suggested, I placed in the following code which does create a complete string of the concatenated fields but it is still resulting with a blank line in the address detail when one of the fields are blank in the recordset.

With rs
   sFullAddress = !CNAME & (Chr(13) + Chr(10) + !Line1) & (Chr(13) + Chr(10) + !Line2) & (Chr(13) + Chr(10) + !Line3) & (Chr(13) + Chr(10) + !CITY) & (Chr(13) + Chr(10) + !COUNTY) & (Chr(13) + Chr(10) + !POSTCD) & ""
End With
wDoc.Bookmarks("FullAddress").Range.Text = sFullAddress

Open in new window

I have removed all bookmarks which were associated with any part of the address fields and removed the vba code that was associated with the individual bookmarks, e.g.,

 wDoc.Bookmarks("CompanyName").Range.Text = Nz(rs!CNAME, "")

Open in new window

I rechecked field 'Line3' to see what value was being returned when running through the recordset and it displays:

wDoc.Bookmarks("Line3").Range.Text = Nz(rs!Line3, "")

Open in new window

Result of the above line is, rs!Line3 = ""

Any further suggestions would be appreciated.

Thank you in advance.

Mark

crystal (strive4peace) - Microsoft MVP, Access

hi Mark,

you're welcome

NZ will convert Null to "". If there is no data, then you could delete the bookmark (make sure the bookmark INCLUDES the paragraph marker? not done this myself so I don't know) as suggested by Graham.

   if Nz(rs!Line3, "") <> "" then    'or len(trim( Nz(rs!Line3, ""))) > 0
      wDoc.Bookmarks("Line3").Range.Text = rs!Line3
   else
      wDoc.Bookmarks("Line3").Range.Paragraphs.First.Range.Delete
   end if

Open in new window

Personally, I construct the whole address block using a query in Access and then write the whole block to Word into one bookmark. In that case, I would not include the paragraph marker in the bookmark definition

have an awesome day,
crystal

GrahamSkan

Crystal, I didn't actually suggest including the paragraph mark within the bookmark - I didn't want to complicate the message. However that would be an alternative solution.

John Tsioumpris

For this kind of issues the method i use is to turn on Macro recording on Word....record the Actions...stop..macro to VBA...read the VBA..transfer the functionality to Access...

Dale James

ASKER

Thank you everyone for your input. It has actually been a combination of your suggestions which has make it all work perfectly. As you can probably detect, I am not an expert in this area and there would be much better ways to achieve the desired result but the most important factor is that it has been achieved.

Thank you all once again.

Sincerely

Mark

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.