Create a text file from memo field data

[BobSullivan]

I am still working on this database.

There is table that has a memo field containing rtf codes and text. I have discovered that if I send the field to a word document, I get exactly what's in the field, which is mostly symbols and other stuff I don't want. However, if I perform the following sequence of steps, I get beautifully formatted text in a word document:
1. Copy the field to the clipboard
2. Open notepad
3. Paste the data into notepad
4. save the file as a text file
5. Open the word document
6. Insert the text file as an object into the word document. (insert, text from file)

I can open notepad by using the following code:

Call Shell("NOTEPAD.EXE", 1)

so that takes care of step 2. I can open the word document, because Hans geve me the code for that 2 weeks ago (thanks Hans!), so that takes care of step 5.

The field is me.quoteDocument. tried to copy the information to the clipboard using
RunCommand acCmdCopy but am getting an error 2046: the command or action "Copy" isn't available now.

I don't have to follow my logic, all I really need to know is how to get memo fields into text files, and how to insert text files into word documents.

Thanks for reading.

[HansV]

To save the contents of a memo field to a text file, you don't need Notepad. You can write directly to a text file:

Code: Select all

    Const strFile = "C:\Temp\Dummy.rtf" ' change as needed
    Dim f As Long
    f = FreeFile
    Open strFile For Output As #f
    Print #f, Me.memNotes
    Close #f

To insert this file into the Word document, you can use code like this (using the same variables as in your earlier thread):

Code: Select all

   Set rng = objword.ActiveDocument.Content
    rng.Collapse Direction:=wdCollapseEnd
    rng.InsertFile FileName:=strFile

And if you wish, you can delete the temporary file:

Code: Select all

    Kill strFile

[BobSullivan]

Thanks Hans!

the code works in that I am creating an rtf file. But I can't open it, and when the code gets to the insert file part, I'm getting a 5097 error, which the search engines tell me is a lack of memory error.

[HansV]

Try changing the extension to .txt:

Const strFile = "C:\Temp\Dummy.txt" ' change as needed

[BobSullivan]

I figured out the error of my ways on the 5097 errors.

Somehow, in one of my many tests and re-configurations, I changed the memo field with all the rtf codes in it to a text field, which truncated many of the fields. This caused Word to send the error message, because evidently Word refuses to accept the truncated rtf codes.

The thing is working well now, but only accepting one code at a time. I think I'm going to have to include the code above in a loop, inserting one rtf code at a time into my word document.

When this thing is finished, I'd like to post some of the routines someplace. I think that they would be helpful to others. This is by far the most complex access code I've ever tried to emulate. With everyone's help that I've received (much of it from Hans here), I believe I learned more Access in the past 2 months than in the 10 years prior to that.

The final step in all of this is to run some sort of mail merge. Some of the rtf codes contain what probably passed for mail merge field in some previous version of rtf. But now, all that happens is that the code appears in the finished document like this: Your Quote: {QUOTENUMBER} instead of this: Your Quote: 12345. , but honestly, I think I'd rather do some sort of search and replace in the codes than try to attempt a mail merge at this point.