• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

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
0
MickyMc
Asked:
MickyMc
  • 8
  • 6
  • 3
  • +1
1 Solution
 
LuckyLuke57Commented:
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.
0
 
GrahamSkanRetiredCommented:
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 :)
0
 
LuckyLuke57Commented:
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
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
MickyMcAuthor Commented:
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

0
 
GrahamSkanRetiredCommented:
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.
0
 
MickyMcAuthor Commented:
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
0
 
LuckyLuke57Commented:
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
0
 
GrahamSkanRetiredCommented:
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

0
 
GrahamSkanRetiredCommented:
"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?
0
 
MickyMcAuthor Commented:
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
0
 
GrahamSkanRetiredCommented:
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.
0
 
cquinnCommented:
Change your Autopen macro and call it AutoNew - this will only fire when the document is created but not when it is subsequently opened
0
 
MickyMcAuthor Commented:
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
0
 
GrahamSkanRetiredCommented:
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
0
 
GrahamSkanRetiredCommented:
You can change the template programatically, e.g.:

 
  ActiveDocument.AttachedTemplate = "C:\Documents and Settings\User Name\Application Data\Microsoft\Templates\Normal.dot"

Open in new window

0
 
MickyMcAuthor Commented:
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
0
 
MickyMcAuthor Commented:
oh... you just bet me to it Graham... nice once...regards
0
 
GrahamSkanRetiredCommented:
I had to check the syntax. I couldn't remeber if the AttachedTemplate propert was a string (it is) or an object.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 8
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now