Poor Word Wrapping When Exporting Report to Rich Text File

[caki2112]

Hello,



I have a report that includes a free form comment stored as a memo field. This comment sometimes makes a record run onto a second page, which isn't acceptable for our purposes. Therefore, I have to export the report to a Rich Text File so it can be edited after the report has been run. Unfortunately, in the Rich Text File, when the comment wraps at the end of a line, if the space between the last word on that line and the first word on the next line doesn't fit on the upper line, it is carried over to the next line, resulting in a space at the beginning of that line. In other words, instead of getting

"This endowment exists to support students
"in the nursing program

you get

"This endowment exists to support students
" in the nursing program

if the width is such that there is no room for a space after "students". (Ironically, I had to put the quotes in to get the leading space, because this editor automatically gets rid of it.)

Is there a way to fix this? I tried setting the memo field's Text Format property to Rich Text instead of Plain Text and it didn't help. Thanks!

[rpeare]

When your field gets exported it's reserving the same number of characters that your original field has, if you get rid of that space at the beginning of a line when you put the string back into your databse you'd get an unintended compound word of 'studentsin'. This is assuming after you've made your edits you put it back into your database. If you don't care about being able to transport it back into your database you could likely do some edits after the RTF has been exported to process the rtf and TRIM each line.

I do not believe you'll be able to display it the way you want without actually modifying the rtf itself.

[caki2112]

Thanks for your response. The data doesn't need to go back into the database, so getting rid of the space won't hurt me there. Is there a way to globally edit the RTF to get rid of those spaces? Currently, we go through and manually remove each one. Not particularly efficient or foolproof.

[rpeare]

there is but there's one more issue I didn't think about.

Let's say you have two lines

This is a tremendously short
 line of text.

where there's a space on the second line.

If you blindly remove that space you'll likely end up with

This is a tremendously
shortline of text.

so it's likely that you would have to read the entire line as a string and break it up by the number of characters per line.

What you want to look at is the filesystemobject set of commands.

So after you have created your RTF file open the file using the filesystemobject method and loop through each line of your text. I suspect that your memo field is going to be read as one huge string which you'll have to break up. if it doesn't read it as one huge string (use the debug.print command after you read each line to see how it's cycling through the file) it may be easier to deal with. If you can supply an example of your final RTF (not just the memo field unless that's the only field in the RTF) I can suggest a way to approach it.

[caki2112]

I have attached a sample of the rich text file I am producing. The problem comes in the Endowment History section, demonstrated on the first page. Some pages don't appear to have an issue, demonstrated on the other two pages. The full document would have many more pages. I did substitute false data on this document, but I did not touch anything in the Endowment History sections.

rptEndowmentRecipients.zip

[rpeare]

Try this:

    Dim objDoc, objFile, objFSO, objWord, strFile, strHTML, myfile
    Dim iWordCount As Long
    Dim i
    Dim sWord As String
    Dim sline As String

    myfile = CurrentProject.Path & "\rptEndowmentRecipients.rtf"
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set objWord = CreateObject("Word.Application")

    With objWord
        .Visible = True
        .Documents.Open myfile
        Set objDoc = .ActiveDocument
        iWordCount = objDoc.Words.Count
        For i = 1 To iWordCount - 2
            sWord = objDoc.Words(i)
            If Len(Trim(objDoc.Words(i))) = 0 Then
                objDoc.Words(i) = Trim(objDoc.Words(i))
            End If
        Next
        objDoc.Save
        objDoc.Close
        .Quit
    End With

this code assumes the rtf file is in the same folder as the access application

[caki2112]

Thanks for the code. It gets the job done, but appears to run into trouble its last time through. The line "sWord = objDoc.Words(i)" generates the following error:

Run-time error 5941:
The requested member of the collection does not exist.


If I understand your code correctly, it shouldn't be advancing beyond the end of the document, so do you have any idea what might be causing this error?

[rpeare]

In the code you see I go to the 3rd to last 'word' because some special characters (end of file, carriage returns, line feeds etc) may be in there, try subtracting 1 from the total words and run it again, keep going until you find the cutoff

[caki2112]

Would it make sense for the number of special characters to vary as the records populating on the report vary? I limited my query to only a few endowments when I was searching for the cutoff so that I wouldn't have to wait for it to go through the entire document to find out if I was subtracting the right number. I found the cutoff, but when I eliminated my criteria in the query and ran it again, I got the same error. Is there a way to test the 'word' to determine that it's a special character before trying to do anything with it?

Also, just a question as I try to figure out what each line of the code you sent is actually doing. I noticed that you set a string (sWord) equal to the value of the word that is being checked, but you don't appear to use sWord anywhere. What is the purpose of this line?

[rpeare]

the sWord is a leftover from when I was testing code and can be ignored.

As far as the variable number of special footers can you test to see if there's a pattern. I suspect that there *may* be a footer you have to ignore for each record of the data set running the report but, honestly, that's a guess, because I don't deal with rtf's very often. The code that actually does the formatting etc is much, much longer than the actual text of your report and I didn't spend any time trying to decipher it. If it's as simple as counting the number of records in your data set and subtracting that from your from the total your problem could be solved fairly easily. Here's what I would try, to get that code to work I had to subtract 2, and there were 3 different endowments on the reports, so try running the code with 1 endowment and see if the code will work with -0 (minus zero) then try it with say 5 records and see if you can get it with -4.

If you can you can count the number of records in the recordset after it's done printing to an RTF and use that to determine how many 'words' to subtract.

[caki2112]

There didn't seem to be a pattern for subtracting words, but I found another way around it. Basically, instead of using a count to control when I stop going through the loop, I used the error itself. I created a boolean set to true that indicates if it is on a real word. When it encounters the error, I have it set the boolean to false so that it stops going through the loop. If no error is encountered, it proceeds as usual.

Dim objDoc, objFile, objFSO, objWord, strFile, strHTML, myfile
        Dim iWordCount As Long
        Dim i
        Dim sWord As String
        Dim sline As String
        Dim dtmStartTime As Date
        Dim boolLegitWord As Boolean
        
        boolLegitWord = True
        i = 1
        
        myfile = CurrentProject.Path & "\rptEndowmentRecipients.rtf"
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objWord = CreateObject("Word.Application")
        With objWord
            .Visible = True
            .Documents.Open myfile
            Set objDoc = .ActiveDocument
            iWordCount = objDoc.Words.Count
            
            While boolLegitWord = True
                On Error Resume Next
                sWord = objDoc.Words(i)
                    
                If Err.Number <> 0 Then
                    boolLegitWord = False
                    
                ElseIf Len(Trim(objDoc.Words(intCount))) = 0 Then
                    objDoc.Words(i) = Trim(objDoc.Words(i))
                
                End If
                i = i + 1
            Wend
                
            objDoc.Save
            objDoc.Close
            .Quit
        End With

[rpeare]

Where there's a will there's a way!