Creating and saving raw Rich Text into a field

[Kronix]

Creating and saving raw Rich Text source code into a field

I'm using an ERP software that stores Rich Text product descriptions in a single Long Text field. The text is simply the source code of Rich Text files. For example, if I open an RTF file in Notepad and copy all of it into the field, it works.

I want to automate the process (via VBA) of creating a Rich Text block of text (typically several pages long), then pasting the source into the Long Text field in the table that is linked via ODBC in Access.

The text will be created from MS Word Template files with bookmarks, that I will fill in from Access tables via VBA code. Then the text must then be converted to RTF format. And finally the RTF source code must be copied directly into the field.

After creating the template files using MS Word, I want everything to run from the Access program via VBA. So the process I have planned is:

1. Open the template, fill in the bookmarks using the Access data, then save the filled in file. This should be straightforward using the Word.Application object.

2. Open the filled in file, and save it in RTF format. Is this just simply using the SaveAs function and specifying RTF format, and making sure the filename has the RTF extension? NOTE: I know that MS Word RTF files are always bigger than RTF files saved in Wordpad. Is there a Wordpad object I can use in VBA so it saves smaller? Or some other way to save smaller RTF files?

3. Open the RTF format file, and copy the entire source into the Long Text field. I'm guessing I need to read the RTF file as a plain text file in VBA (same as Notepad does) using the Input command.

So does this sound good, or is there a simpler way? Furthermore is the issue of all the temporary files being used (template Word doc, filled in Word doc, RTF file). Is there any way to not have all these files being created? Furthermore, can I store all the templates in Access instead of separately, perhaps with a button to update the template if I change it in the future?

And finally, if I need to store files separately, what is the best way to access template files that are stored in the same folder, but can have different absolute paths since the Access program will used on different computers in different folders.

 

[isladogs]

If I'm reading this correctly, you want to export data to Word just so you can convert it to Rich Text / HTML then import it back into Access

The Word files are only needed as a place to hold the data whilst you convert it

Why don't you just convert from plain text to rich text in Access
or better still, use a Memo field and save the data as rich text in the first place.

... or am I missing something here?

 

[Kronix]

If I'm reading this correctly, you want to export data to Word just so you can convert it to Rich Text / HTML then import it back into Access

The Word files are only needed as a place to hold the data whilst you convert it

Why don't you just convert from plain text to rich text in Access
or better still, use a Memo field and save the data as rich text in the first place.

... or am I missing something here?

Two things I think you are missing, though correct me if I'm wrong:

1) If I copy the rich text into the Long Text field that is being used by the ERP program, it will be copied as plain text. But that field is supposed to have the HTML markers for Rich Text. It will look unreadable when viewed directly in the table, but the ERP software converts it to Rich Text somehow -- perhaps data type incompatibility caused the lowest common denominator (raw unformatted text) to be shown when using ODBC. In any case, copying the illegible text that you see when opening a RTF in Notepad into that field results in the text being shown with proper formatting in the ERP software.

I suppose the question is, is it possible to convert a Rich Text field in Access into raw text with the HTML markers? The only way I know of so far is either with Notepad (which I don't think I can use in Access) or treating the RTF file as a raw text file in Access.

2) I need a Word template with bookmarks to start off with. The Word template contains common text already, and the bookmarks are placed to fill in the variable blanks with Access data. The template contains formatting which will be carried over when it is converted into RTF. I can't use the Word document directly because the ERP software reads RTF formatting, not Word formatting.

Is there a way to create a RTF (in or out of Access) with bookmarks/forms that can later be filled in by Access? And as mentioned in my first post, it is a hassle to keep track of several separate template files (each for different categories of products), so I'd like them to be part of the database file.

 

[isladogs]

If I copy the rich text into the Long Text field that is being used by the ERP program, it will be copied as plain text

Will it? Why?

Is there a way to create a RTF (in or out of Access) with bookmarks/forms that can later be filled in by Access?

Yes but I haven't done it for over 10 years and it was an utter pain to setup
After all this time, I can't advise you on how.

Hopefully someone else can answer or try a Google search

One site that may be useful for transferring data between Access & Word is:
http://www.helenfeddema.com/Code%20Samples.htm

Good luck

 

[Kronix]

Will it? Why?
Good luck

The Long Text field in the ERP's database is regular text, not rich text. The Rich Text HTML markers which have been copied as standard text are only transformed into Rich Text when shown in the ERP program.

Anyway, I've pretty much accomplished it in the meantime. One problem I had was that I had accidentally left a Long Text form field I was using as an intermediary to store and test the raw RTF source data as a Rich Text field. This resulted in multiple formating errors when trying to open the RTF file it was copied into. I have to remember to always save Rich Text markers as standard text, not as Rich Text

 

[isladogs]

Glad you're getting somewhere.

Just a thought - You do know about PlainText option? It may help

 

[Kronix]

I just looked up PlainText. Not sure how that would help here. If you mean for the Rich Text form text field that was invalidating my raw source stream, I'm not sure what the problem there was anyway, unless Rich Text form boxes automatically alter the plain text inserted into them? So maybe PlainText would revert that, although I didn't need that Rich Text box anyway.

 

[isladogs]

OK one last thought ...
I have an app with a detailed help system which can be viewed either as rich formatted text or as HTML including images & hyperlinks

To do this, the table includes 2 memo fields :

Here is the form where the editing is done

If you think this may be of use to you, let me know
Otherwise I'll drop out of this thread and leave you to it.

 

[Kronix]

I've noticed if I open a Word document to fill in the bookmarks, I can't do so if the file is opened or read only even if I want to save the document to a different file name. How would I go about editing a document in VBA that is read only to save under a different filename? Do I have to save the file twice, once before the editing commands and once after?

Also, can anybody give me advice for incorporating the Word files (templates) directly into the Access database file so I don't have to worry about copying the Word files to every computer that uses it?

Btw ridders, both of those columns look like HTML to me, not Rich Text.

 

[isladogs]

Btw ridders, both of those columns look like HTML to me, not Rich Text.

Point accepted - I was using the phrase rich text carelessly though they are often used interchangeably

The purpose is as follows
a) text in left column is a memo field displayed as rich text in a standard Access textbox

b) right column data is used in a web browser control and can therefore include images, videos & hyperlinks to external documents

Good luck with your project