asked on

How NOT to copy Macros in 2003

Ok heres what I,ve got.
I have a document (*.doc) which I use as a template. The document is used to generate invoices. So when I open the document it automatically asks me do I want to Update the fields as I have a marco for the AutoOpen. I fill out the fields as I have 'ASK' fields and the fields are all updated, price, date, invoice total etc. Then when I close the doc it uses the AutoClose to save the doc as Invoice + Date... eg Invoice 20080126.
Ok now the issue. When I send the Invoice20080126 to someone and they open it the marcos get fired again and I dont want them to. They seem to get copied to the new document. I have tried saving the orginal template.doc as a .DOT (template) and creating a document based on it. I changed the autoopen macro to only get fired on a new event and thought this would work but it does.
So how do I open my template and create a doc without copying over the macros so I can send it to another user and they can open it without any macros getting fired of, when they open or close the document.
PS... anyone know of any good sites that explain the proper use of templates in Word. I,m fairly decent on the VBA but never really got my head on what happens with these templates

best regards Mick

LuckyLuke57

Hello,

You can:
- Create doc from dot (template)
- Save the doc "invoice1.doc"
- Close the doc
- Set your macro security "very high" (tools / options / security / macro-security)
- Open your "invoice1.doc" and save it. It will be saved without macro's.

Luke

P.S. In fact what I tested was
- Open your "invoice1.doc" and save it as "invoice2.doc". It will be saved without macro's, but I think you can save it under the same name.

GrahamSkan

Macros do stay in the template and do not get copied. This is often a nasty shock to new developers who want to post the resulting documents around.

However, the template will remain attached to the document, so if the template can be found when the document is opened, an AutoOpen macro in the template will run. Try attaching the Normal template (Tools/Templates and Add-ins, Attach... button)

Hi Luke :)

LuckyLuke57

Hello Graham,

Always nice to see you "putting the dots on the i".
But I tested my solution, and the resulting document was very clean ;-)

CU later,

Luke

MickyMc

ASKER

Hi folks... thanks for the feed back but I,m trying to automate this as much as possible... so when I open the template it prompts me for the values and when I exit word it saves it automatically under the correct name. Here is the code I use...

Sub AutoOpen()
'
' AutoNew Macro
' Macro recorded 19/01/2004 by M
'
    If MsgBox("Update Fields?", vbYesNo + vbExclamation, "Update Invoice") = vbYes Then
        Selection.WholeStory
        Selection.Fields.Update
        Selection.HomeKey Unit:=wdStory
    End If
    
    ChDir ActiveDocument.Path  'PS THIS DOESNT WORK WHEN SAVED AS A TEMPLATE
End Sub
 
Sub AutoClose()
Dim sDate As String
 
    If MsgBox("Save Invoice?", vbYesNo + vbExclamation, "Save Invoice") = vbYes Then
        sDate = ActiveDocument.Bookmarks("INVOICE_DATE").Range.Text
        ActiveDocument.SaveAs ActiveDocument.Path() & "\Invoice_MB_" & Format(sDate, "MMM_YY"), wdFormatDocument, , , , , True
    End If
End Sub

Open in new window

GrahamSkan

Hi Luke
I don't think that there is any contradiction between what you tried, and what I said. Setting the security level excessively high will stop any macros from running, but won't actually remove them.

MickyMc

ASKER

thanks Graham but isnt the security settings applciation based and not document based. ie if I have set them high on my machine and someone sets them low on theres, then the macro will still run... regards Mick

LuckyLuke57

Indeed, macro security is the status of Word at a certain moment, not a property of the document.

What I ment was to set the macro security high, and then open the document (so that all macro's were disabled), and then saving the document without macro's.

My opinion was that the document then is saved without macro's, but Graham corrected that.

Luke

GrahamSkan

I appreciate that.
However, I am not sure that macros are your problem (entirely).
It is true that the Ask field work each time that it is updated and that your AutoOpen macro will provoke it, but of course it will happen if the if the fields get updated forat any other time (e.g. when printed).

This snippet is one way of avoiding the update on re-opened documents

Sub AutoOpen()
if activedocument.name = activedocument.fullname then
   activedocument.Fields.Update
end if
End Sub

Open in new window

GrahamSkan

"I appreciate that" referred to Mick's comment (20831054).

I have doubts about my Autopen macro, because I am not sure exactly when you want it to run. It will only run when the document is created, making it the same as AutoNew. Since fields are updated automatically at creation time, it is thus entirely superflous.

I still don't understand how your users are getting the macros. Is the template on a network share accesible by everyone?

MickyMc

ASKER

ah we are getting close folks. To sum up the points made by Graham and Luke..

1) The document works fine...when opened and fine when closed as it saves itself under a filename and date.
2) The macros are in the orginal .doc and when that doc is closed it, its saves the changes as a new doc.. invoice20040201 but it seems that the macros are saved in the newly created doc and not in any templates.
3) When a new user opens the doc (invoice20050202) the macros get fired again.

I thought there was a difference in the AutoOPen and the AutoNew in that the Autonew only got called when you create a document from a template but I,m not sure....
So what I need is when the new doc (invoice20040202) is saved it has not macros in it at all.

regards Mic

GrahamSkan

Point 2
Are you actually using a document as a template by copying it, then?
That might be where the confision lies. Rename it as .dot and save it alongside the Normal.dot. Then use File/New to create a new document, selecting your template.

cquinn

Change your Autopen macro and call it AutoNew - this will only fire when the document is created but not when it is subsequently opened

MickyMc

ASKER

ok.. sorry for the delay but I nearly have this and I,m right back where I started. The problem was that a copy of the macro was in the normal.dot aahahhahh. Anyway heres where I,m at.

I have created the template and called it InvoiceTemplate.dot
I have two macros in there, AutoNew and AutoClose.
All works well except one thing... when I go to save, it cant work out where I opened the template from. I have the template in an invoice directory where I keep all my invoices and dont want them created in the word template directory. The command I used where
chdir thisdocument.path and chdir activedocument.path.

So how do I get where the template was opened from so I can save it.

PS... they newly create docuement does not have the macros in it, however it does reference the .dot template file that was used to create it. When I send the file to someone else there are no marcos and I,m sorted. If I open it on my own machine, it can find the template but the AutoNew doesnt get called and I,ve removed the AutoOpen. Only the close gets called which is fine as I can save any changes...

regards Mick

GrahamSkan

If you look in Tools/Templates and Add-ins, you will see the attached template in the Document template box.

You can change it by browsing for another template file, including Normal.dot

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MickyMc

ASKER

thanks Graham, but remember I,m trying to do this using VBA. I've just worked it out. Word defaults to the marcos template directory. When you open a template in the AutoNew it cant resolve the ActiveDocument.Path but on the save you can use this

chdir ActiveDocument.AttachedTemplate.Path

so I,m going to half the points to you Graham for sticking with it... and thanks Luke and cquin for all your help

MickyMc

ASKER

oh... you just bet me to it Graham... nice once...regards

GrahamSkan

I had to check the syntax. I couldn't remeber if the AttachedTemplate propert was a string (it is) or an object.