How to make a word macro to execute only once?

Hi,

I've got this unusual problem to solve.

I have a Word Document Template (.dot) that is used by my co-workers
to prepare offers for customers. This adds on top and bottom of the
document our company logo and a few contact infos.

My co-workers asked me if I can show an alert when they close the
document, asking "Do you want to save this document in the 'offers'
directory? (Y/N)" and I made this through a macro, using the "close"
event of the document. There are a few problems, though:

- This prompt happens everytime the user closes the file
- This must not be displayed if the customer opens and closes it

My idea was to make it execute only once, so that the second time it
won't be displayed... but how do I do it? How can I set a flag or
something so that a macro is executed only once, or how can I delete a
macro from scripts in VBA?

Thanks in advance!

Jonathan
Wed Jun 18 04:06:33 PDT 2008


<Luca.albanesi@gmail.com> wrote in message
news:6ce86c93-0571-43f3-bab9-7ad6abbf364b@m44g2000hsc.googlegroups.com...
> Hi,
>
> I've got this unusual problem to solve.
>
> I have a Word Document Template (.dot) that is used by my co-workers
> to prepare offers for customers. This adds on top and bottom of the
> document our company logo and a few contact infos.
>
> My co-workers asked me if I can show an alert when they close the
> document, asking "Do you want to save this document in the 'offers'
> directory? (Y/N)" and I made this through a macro, using the "close"
> event of the document. There are a few problems, though:
>
> - This prompt happens everytime the user closes the file
> - This must not be displayed if the customer opens and closes it
>
> My idea was to make it execute only once, so that the second time it
> won't be displayed... but how do I do it? How can I set a flag or
> something so that a macro is executed only once, or how can I delete a
> macro from scripts in VBA?
>
> Thanks in advance!

In Word, whan you have VBA stored in the template and you create a document
based on the template, the VBA is not copied into the document. The VBA
remains available to you because the document includes a reference to the
template, so Word knows what template to look in for the VBA code.

When you email the document to a customer who does not have the template,
none of the VBA code is present, and therefore will not execute.

In short, you don't need to do anything.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

Luca
Wed Jun 18 06:03:51 PDT 2008

On 18 Giu, 13:06, "Jonathan West" <jw...@mvps.org> wrote:
> <Luca.alban...@gmail.com> wrote in message
>
> news:6ce86c93-0571-43f3-bab9-7ad6abbf364b@m44g2000hsc.googlegroups.com...
>
>
>
>
>
> > Hi,
>
> > I've got this unusual problem to solve.
>
> > I have a Word Document Template (.dot) that is used by my co-workers
> > to prepare offers for customers. This adds on top and bottom of the
> > document our company logo and a few contact infos.
>
> > My co-workers asked me if I can show an alert when they close the
> > document, asking "Do you want to save this document in the 'offers'
> > directory? (Y/N)" and I made this through a macro, using the "close"
> > event of the document. There are a few problems, though:
>
> > - This prompt happens everytime the user closes the file
> > - This must not be displayed if the customer opens and closes it
>
> > My idea was to make it execute only once, so that the second time it
> > won't be displayed... but how do I do it? How can I set a flag or
> > something so that a macro is executed only once, or how can I delete a
> > macro from scripts in VBA?
>
> > Thanks in advance!
>
> In Word, whan you have VBA stored in the template and you create a document
> based on the template, the VBA is not copied into the document. The VBA
> remains available to you because the document includes a reference to the
> template, so Word knows what template to look in for the VBA code.
>
> When you email the document to a customer who does not have the template,
> none of the VBA code is present, and therefore will not execute.
>
> In short, you don't need to do anything.
>
> --
> Regards
> Jonathan West - Word MVPwww.intelligentdocuments.co.uk
> Please reply to the newsgroup

Hi Johnatan,

thanks for your quick reply.

This would solve the customer side of the problem, but the Lan part
would still present itself.

The template is available to all my co-workers, so when they open and
close the document, the message would still present itself and they
want to avoid it (unless that's the first time they save the
document).

I was thinking a solution that includes a connection to an access or
MySQL database that stores the filename of the files so that the macro
can put the filename when they're stored on the disk: if a name
exists, then don't present the message. This is, if there's a smarter
and easier method on Word.

Thank you

Luca

Jonathan
Wed Jun 18 08:55:21 PDT 2008


<Luca.albanesi@gmail.com> wrote in message
news:fb7507c8-462d-4400-926a-cd4d5f130bd2@34g2000hsf.googlegroups.com...
> On 18 Giu, 13:06, "Jonathan West" <jw...@mvps.org> wrote:
>> <Luca.alban...@gmail.com> wrote in message
>>
>> news:6ce86c93-0571-43f3-bab9-7ad6abbf364b@m44g2000hsc.googlegroups.com...
>>
>>
>>
>>
>>
>> > Hi,
>>
>> > I've got this unusual problem to solve.
>>
>> > I have a Word Document Template (.dot) that is used by my co-workers
>> > to prepare offers for customers. This adds on top and bottom of the
>> > document our company logo and a few contact infos.
>>
>> > My co-workers asked me if I can show an alert when they close the
>> > document, asking "Do you want to save this document in the 'offers'
>> > directory? (Y/N)" and I made this through a macro, using the "close"
>> > event of the document. There are a few problems, though:
>>
>> > - This prompt happens everytime the user closes the file
>> > - This must not be displayed if the customer opens and closes it
>>
>> > My idea was to make it execute only once, so that the second time it
>> > won't be displayed... but how do I do it? How can I set a flag or
>> > something so that a macro is executed only once, or how can I delete a
>> > macro from scripts in VBA?
>>
>> > Thanks in advance!
>>
>> In Word, whan you have VBA stored in the template and you create a
>> document
>> based on the template, the VBA is not copied into the document. The VBA
>> remains available to you because the document includes a reference to the
>> template, so Word knows what template to look in for the VBA code.
>>
>> When you email the document to a customer who does not have the template,
>> none of the VBA code is present, and therefore will not execute.
>>
>> In short, you don't need to do anything.
>>
>> --
>> Regards
>> Jonathan West - Word MVPwww.intelligentdocuments.co.uk
>> Please reply to the newsgroup
>
> Hi Johnatan,
>
> thanks for your quick reply.
>
> This would solve the customer side of the problem, but the Lan part
> would still present itself.
>
> The template is available to all my co-workers, so when they open and
> close the document, the message would still present itself and they
> want to avoid it (unless that's the first time they save the
> document).
>
> I was thinking a solution that includes a connection to an access or
> MySQL database that stores the filename of the files so that the macro
> can put the filename when they're stored on the disk: if a name
> exists, then don't present the message. This is, if there's a smarter
> and easier method on Word.
>

There is an easier way. If you only want to display the dialog when the
document is first saved, then you can check the ActiveDocument.Path
property. If it is an empty string, then the document is newly-created and
has never been saved. If it isn't, then the document has been previously
saved. Your code can branch accordingly.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

Helmut
Wed Jun 18 09:20:05 PDT 2008

Hi Luca,

how about this one:

Sub Test400()
' for cleaning the document when testing
ActiveDocument.Variables("MyClose").Delete
End Sub
' ------------------------------------------------------
Sub Autoclose()
On Error GoTo next1
ActiveDocument.Variables.Add name:="MyClose", Value:=True
MsgBox "Where to save?"
Exit Sub
next1:
ActiveDocument.Save
On Error GoTo -1
On Error Resume Next
ActiveDocument.Close ' savechanges:=wdSaveChanges
End Sub

which applies to every document,
therefore a check of the document's name may be recommended.

I received all kinds of errors, therefore
On Error GoTo -1
On Error Resume Next
may look strange,
but nothing else worked, unfortunately.

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

gordon(dot)bentleymix(at)gmail(dot)com>
Wed Jun 18 16:47:00 PDT 2008

Luca,

Perhaps something like this would work:

Option Explicit

Sub AutoClose()
If fcnFindVar("SavedToOffers") = False Then
ActiveDocument.Variables.Add "SavedToOffers", "no"
End If
If LCase(ActiveDocument.Variables("SavedToOffers").Value) = "no" Then
Dim myResult As Integer
myResult = MsgBox("Do you want to save to the 'offers' folder?",
vbYesNo, "Save Document")
If myResult = 6 Then
Dim myFilePath As String
Dim myFileName As String
myFilePath = "X:\Offers\"
myFileName = ActiveDocument.BuiltInDocumentProperties("Title")
ActiveDocument.Variables("SavedToOffers").Value = "yes"
ActiveDocument.SaveAs myFilePath & myFileName
End If
End If
End Sub

Private Function fcnFindVar(VarName As String) As Boolean
Dim myVar As Variable
For Each myVar In ActiveDocument.Variables
If myVar.Name = VarName Then
fcnFindVar = True
Exit For
End If
Next myVar
End Function

There might be a bit more to do with how you actually execute the save to
ensure that you don't overwrite an existing document, etc. And of course you
would need to replace myFilePath with the path to your "offers" folder and
myFileName with something a bit more meaningful. However, this should give
you an idea of where to begin.
--
Cheers!
Gordon
The Kiwi Koder

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.


"Luca.albanesi@gmail.com" wrote:

> Hi,
>
> I've got this unusual problem to solve.
>
> I have a Word Document Template (.dot) that is used by my co-workers
> to prepare offers for customers. This adds on top and bottom of the
> document our company logo and a few contact infos.
>
> My co-workers asked me if I can show an alert when they close the
> document, asking "Do you want to save this document in the 'offers'
> directory? (Y/N)" and I made this through a macro, using the "close"
> event of the document. There are a few problems, though:
>
> - This prompt happens everytime the user closes the file
> - This must not be displayed if the customer opens and closes it
>
> My idea was to make it execute only once, so that the second time it
> won't be displayed... but how do I do it? How can I set a flag or
> something so that a macro is executed only once, or how can I delete a
> macro from scripts in VBA?
>
> Thanks in advance!
>