Get me started programming and debugging Microsoft Office automation

Question

I'm using Microsoft Office 2003 and creating a bunch of template documents to standardize some tasks. I asked this on Superuser.com and got no response so I'm thinking it's too program-y and hoping I'll have better luck here.

I need to automate a work flow that uses a bunch of Office (mostly Word) templates. What I want is to have "My Template Foo.dot" and "My Template Bar.dot", etc. in the "My Foo Bar Stuff" on a shared drive and have users double click on a template to create a new Foo or Bar.

What's I'd really like is for the user to double-click on the Foo template and be prompted for a couple of items related to their task (e.g., a project number) and have a script in the template change the name that Save will default to something like "Foo for Project 1234.doc".

I asked on Google Groups and got an answer that worked....for a while. Then my AutoNew macro stopped kicking in when I created a new document by double-clicking on the template. I have no idea why or how to debug it.

In Class Modules/This Application, I have:

Sub AutoNew()
    Dim Project As String
    Project = InputBox("Enter the Project Number")
    ActiveDocument.SaveAs "Project " & Project & " Notes.doc"
End Sub

In Microsoft Word Objects/ThisDocument, I have:

Private Sub Document_New()

End Sub

I really have no idea why or where that came from.

In Tools/Macro Security... I have Security Level set to "Low".

I'm a software engineering with 25+ years of experience but a complete Office automation noob. Specific solutions and pointers to "this is how to automate Word" FAQs are welcome. Thanks.

---

Update: If I create a new template (New..., Blank Document, Save As "My New Template.dot"), and insert the AutoNew() macro, it works. So what's inhibiting it from working on my existing template?

Update 2: Removing the module and function from my old template and adding it back works, too.

Answer 1

Check if the macro is still contained in your template. This sounds stupid but it happended to me, too, in Word 2003 under the following circumstances:

1. Create a template containing macro, everything fine
2. Create a new document file based on the macro, macro kicks in
3. Notice I could improve the template here and there a bit, I do it in the file created in 2) and SaveAs .DOT
4. Macro in .DOT GONE!

Why? Because the code stored in the .DOT doesn't go over to the doc file.

Answer 2

You can attach a template to a saved document in ordre to access the macros contained in the template in question.

You can do this with the AttachedTemplate property of a Document object (i.e. ActiveDocument). Please note that I did not try this myself.

Sub AutoNew()
   Dim Project As String
   Project = InputBox("Enter the Project Number")
   ActiveDocument.SaveAs "Project " & Project & " Notes.doc"

   ActiveDocument.AttachedTemplate = "\\path\to\templates\My Template Foo.dot"

End Sub

See MSDN - Word 2003 VBA Language Reference - AttachedTemplate Property

Hope that helps.