Using VBA Code in .rtf documents

Hi Graham - thanks for replying

This is the code I have attached to a combobox within my word document (See code below).

However, whenever I try saving the word document as Rich Text Format, it warns me that all macros will be lost!  It works ok if I save it as .doc format.

Is it possible with .rtf?

Private Sub Document_Open()
 On Error GoTo Document_Open_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=C:\db\Names.mdb"
    rst.Open "SELECT DISTINCT [StaffNames] FROM tblNames ORDER BY [StaffNames];", _
             cnn, adOpenStatic
    rst.MoveFirst
    With Me.ComboBox1
        .Clear
        Do
            .AddItem rst![StaffNames].Value
            rst.MoveNext
        Loop Until rst.EOF
    End With
Document_Open_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
Document_Open_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume Document_Open_Exit
End Sub

The broad intention is that macro code stays in the template. When a new document is created the template is automatically attached and the template components such as styles, toolbars and macros are usable by the document.

If the document has to be moved for further editing, the template or a copy of the same name should be accessible.

Up to this point, rtf-saved files do not differ from those saved in standard format. You just can't add and keep code in the file itself.

I can understand what you're saying, but, how do I create the template, and how does that link into the .rtf document?  Because, as soon as I save my document as  "whatever.rtf", all the code gets deleted and is then blank and thus my combobox in the document can not access the external database names. (See code above).

If there are any, remove all the specialisations from your particular copy of the document. Save your document with the code as a template next to Normal.dot in the User templates folder or in the Workgroup templates folder.  See Tools/Options. File Locations tab for both folders.

You can then use File/New to create a new document from your template. Your new document will automatically be attached to the template and will have the macros available to it, even if it is saved in RTF format.

You can change the attached template of any existing document via Tools/Templates and Add-ins

Thanks Graham,

I got as far as savining the document as a template in the User Templates Folder and then going to File/New - and then wasn't quite sure what to do.  

So I tried creating a new document and pasted everything from the template to the new document and saved it as .rtf - that didn't work?

Ok, I understand a bit more now.  I have saved a copy of the document as a template in the User Template Directory.  I then saved the document as .rtf format (Tells me I'll lose all macros).
I then go into that document and into Tools/Templates and Addons and select the template I saved.
However,  the code is not being executed or attached and thus I can't use the external database from the combobox.

Am I doing something wrong?  

You should be able to find your template by clicking on the 'On my computer...' link in the 'New Document' task pane that opens on the right,  and choosing from the Templates dialogue that the link opens.

Cross-posted, but to emphasise, you won't need to attach a template to any new documents created from the template.

Ok, it seems to be copying the code across after creating a new document.rtf, but, it's not executing the code?  The code works fine in the .dot format.  It's just not working in the .rt format once it's been created?

Hi Graham,

Can I send you a copy of everything I have (document.dot, document.rtf and the database).  Perhaps you can work out the problem better than I can?

Sorry for the delay. Went out for the day (ended up in Brighton).

Normally you can attach documents here, but it sounds a bit more than usual.
If you want to email them, my address is in my profile.

Thanks Graham - I have e-mailed you.

I have used your files to test the situation.

In fact, as you have found, the Document_Open event only fires if the actual document or template that contains the code is opened.

However, if you put the code into the template's Document_New event, it will run and fill the combo at the time that a document is first created.

If you need it to change the user name later, and want it to run automatically when the document is opened, you can put the code in the AutoOpen macro,

Hi Graham,

Does that mean that users would have to open up the template .dot each time?  Because this wouldn't work with our system.  We can only SEE and open .rtf documents.

I'm sorry, but I've tried getting this to work when I open .rtf documents for the first time, but, it's just not working.  I've done everything you've suggested.  I must be doing something wrong.

Can you send me what you done?

Graham, you're an absolute Star.  It works, thank you very much.

But, the document keeps opening in design mode, even though my security settings are set to low?

It does with me too, even if it is saved in doc format. I'm not convinced that the Low security setting is the whole answer, but I don't have any more information, I'm afraid.