Preventing macro from copying over from template to document

[chris_huh]

I have made a template which just puts in a date range then autosaves
the file to a particular name when you close it. This create a new
document, but all the macro coding from the template file is copied
into the document. How can you set it so that the coding from the
template isn't copied into the document?

I would also prefer if the file isn't saved on close, but if the name
and location of the document is just set by the macro (so when you go
file>save it still lets you change the name and location if you want).
But i coulnd't get it to go to the right directory.

This is the code i have:

Private Function WeekStart() As Date
Dim wday As Byte
wday = Weekday(Date)
Select Case wday
Case 1
WeekStart = Date
Case 2
WeekStart = Date - 1
Case 3
WeekStart = Date - 2
Case 4
WeekStart = Date - 3
Case 5
WeekStart = Date - 4
Case 6
WeekStart = Date - 5
Case 7
WeekStart = Date - 6
End Select
End Function

Private Sub Document_Close()
Sunday = Format(WeekStart() + 7, "d")
Saturday = Format(WeekStart() + 13, "d MMM yy")

FileTitle = Sunday & " - " & Saturday & ".doc"

ActiveDocument.SaveAs "J:\Editorial\Magellan\Sickness Absence\Afghan\"
& FileTitle
End Sub

Private Sub Document_New()
Selection.GoTo What:=wdGoToBookmark, Name:="DateRange"
Selection.InsertBefore Format(WeekStart() + 7, "dddd, d MMMM")
Selection.InsertAfter " to "
Selection.InsertAfter Format(WeekStart() + 13, "dddd, d MMMM yyyy")
End Sub

Thats all in the ThisDocument module.

Thanks

 

[Jonathan West]

I have made a template which just puts in a date range then autosaves
the file to a particular name when you close it. This create a new
document, but all the macro coding from the template file is copied
into the document. How can you set it so that the coding from the
template isn't copied into the document?

You are mistaken. Unless you are doing something quite different from what
you describe, the document does not contain the code. It contains a
reference to the template, and therefore the code in the template is
available when you open the document. If you move the document to a PC which
does not have the template, the macro will no longer run.

I would also prefer if the file isn't saved on close, but if the name
and location of the document is just set by the macro (so when you go
file>save it still lets you change the name and location if you want).
But i coulnd't get it to go to the right directory.

Change this line of code

ActiveDocument.SaveAs "J:\Editorial\Magellan\Sickness Absence\Afghan\"
& FileTitle

To this

With Dialogs(wdDialogFileSaveAs)
.Name = "J:\Editorial\Magellan\Sickness Absence\Afghan\" & FileTitle
.Show
End With

Then you can cancel the save or change the folder and filename if you want
to.

 

[chris_huh]

You are mistaken. Unless you are doing something quite different from what
you describe, the document does not contain the code. It contains a
reference to the template, and therefore the code in the template is
available when you open the document. If you move the document to a PC which
does not have the template, the macro will no longer run.




Change this line of code


To this

With Dialogs(wdDialogFileSaveAs)
    .Name = "J:\Editorial\Magellan\Sickness Absence\Afghan\" & FileTitle
    .Show
End With

Then you can cancel the save or change the folder and filename if you want
to.

Ah yes i see. In the projects box (top left) it lists the template and
the document. The ThisDocument thing under the document is blank. I
think you can see what i'm going to ask next. Is there a way to remove
this template reference so when you open that document there is no
Enable Macros request?

I also changed that code for the saving. That now allows you to change
the location and name (great, thanks) but is there a way for that to
happen when you click Save, rather than close. I guess you woudl take
that bit out of the Document_Close sub, but what would i put it in?

Thanks so much

 

[Jonathan West]

<<Ah yes i see. In the projects box (top left) it lists the template and
the document. The ThisDocument thing under the document is blank. I
think you can see what i'm going to ask next. Is there a way to remove
this template reference so when you open that document there is no
Enable Macros request?>>

Yes. Add the following line to your macro at some suitable point.

ActiveDocument.AttachedTemplate = NormalTemplate

Then the document will be attached to Normal.dot, not to the template.

Alternatively, digitally sign your template so that it is regarded as being
trusted, and the warning will no longer appear. If it is just for local use,
you can create your own certificate. On the Windows Start menu, go to
Microsoft Office, then Microsoft Office Tools. In that submenu you should
see "Digital Certificate for VBA Projects". Click that and follow the
instructions.


<<I also changed that code for the saving. That now allows you to change
the location and name (great, thanks) but is there a way for that to
happen when you click Save, rather than close. I guess you woudl take
that bit out of the Document_Close sub, but what would i put it in?>>

Create a new module, and create a routine in it called FileSave Put the code
I provided into that routine.

 

[chris_huh]

<<Ah yes i see. In the projects box (top left) it lists the template and
the document. The ThisDocument thing under the document is blank. I
think you can see what i'm going to ask next. Is there a way to remove
this template reference so when you open that document there is no
Enable Macros request?>>

Yes. Add the following line to your macro at some suitable point.

ActiveDocument.AttachedTemplate = NormalTemplate

Then the document will be attached to Normal.dot, not to the template.

Alternatively, digitally sign your template so that it is regarded as being
trusted, and the warning will no longer appear. If it is just for local use,
you can create your own certificate. On the Windows Start menu, go to
Microsoft Office, then Microsoft Office Tools. In that submenu you should
see "Digital Certificate for VBA Projects". Click that and follow the
instructions.

<<I also changed that code for the saving. That now allows you to change
the location and name (great, thanks) but is there a way for that to
happen when you click Save, rather than close. I guess you woudl take
that bit out of the Document_Close sub, but what would i put it in?>>

Create a new module, and create a routine in it called FileSave Put the code
I provided into that routine.

Thats got rid of the Enable Macros dialog, thanks.

I am not too proficient with macros and vba. Is a routine different to
a sub, etc. How would i do that. I made a new module (Module1 under
the Modules heading) and then put in:

Sub FileSave()

Sunday = Format(WeekStart() + 7, "d")
Saturday = Format(WeekStart() + 13, "d MMM yy")

FileTitle = Sunday & " - " & Saturday & ".doc"

With Dialogs(wdDialogFileSaveAs)
.Name = "J:\Editorial\Magellan\Sickness Absence\Afghan\" &
FileTitle
.Show
End With

End Sub

That didn't do anything.

 

[Jonathan West]

Thats got rid of the Enable Macros dialog, thanks.

I am not too proficient with macros and vba. Is a routine different to
a sub, etc. How would i do that. I made a new module (Module1 under
the Modules heading) and then put in:

Sub FileSave()

Sunday = Format(WeekStart() + 7, "d")
Saturday = Format(WeekStart() + 13, "d MMM yy")

FileTitle = Sunday & " - " & Saturday & ".doc"

With Dialogs(wdDialogFileSaveAs)
.Name = "J:\Editorial\Magellan\Sickness Absence\Afghan\" &
FileTitle
.Show
End With

End Sub

That didn't do anything.

What is should do (and as far as I can see, you have done it right) is
display the Save dialog predefined when you press Ctrl-S. However, it won't
do that if you have already unlinked the document from the template, as the
template containg the macro is no longer attached to the document!

Therefore, perhaps you need to move the AttachedTemplate command from where
it is and add it to the start of the FileSave macro, so that the template
remains attached until you first save the the document.

 

[chris_huh]

What is should do (and as far as I can see, you have done it right) is
display the Save dialog predefined when you press Ctrl-S. However, it won't
do that if you have already unlinked the document from the template, as the
template containg the macro is no longer attached to the document!

Therefore, perhaps you need to move the AttachedTemplate command from where
it is and add it to the start of the FileSave macro, so that the template
remains attached until you first save the the document.

Ah, yes maybe that is it. I noticed earlier the placing of the
AttachedTemplate made a difference to something else.

I will try that tomorrow. Thanks a lot

 

[chris_huh]

Ah, yes maybe that is it. I noticed earlier the placing of the
AttachedTemplate made a difference to something else.

I will try that tomorrow. Thanks a lot- Hide quoted text -

- Show quoted text -

I moved the AttachedTemplate code to be after the filesave info and it
works now. I have now got all of the code in the ThisDocument thing:

Private Function WeekStart() As Date
Dim wday As Byte
wday = Weekday(Date)
Select Case wday
Case 1
WeekStart = Date
Case 2
WeekStart = Date - 1
Case 3
WeekStart = Date - 2
Case 4
WeekStart = Date - 3
Case 5
WeekStart = Date - 4
Case 6
WeekStart = Date - 5
Case 7
WeekStart = Date - 6
End Select
End Function


Private Sub Document_New()
Selection.GoTo What:=wdGoToBookmark, Name:="DateRange"
Selection.InsertBefore Format(WeekStart() + 7, "dddd, d MMMM")
Selection.InsertAfter " to "
Selection.InsertAfter Format(WeekStart() + 13, "dddd, d MMMM yyyy")
End Sub

Sub FileSave()

Sunday = Format(WeekStart() + 7, "d")
Saturday = Format(WeekStart() + 13, "d MMM yy")

FileTitle = Sunday & " - " & Saturday & ".doc"

With Dialogs(wdDialogFileSaveAs)
.Name = "J:\Editorial\Magellan\Sickness Absence\Afghan\" &
FileTitle
.Show
End With

ActiveDocument.AttachedTemplate = NormalTemplate

End Sub

That works so that when you go file save (or close it and say yes i
want to save) it opens the dialog box with the title already inserted.
Which is perfect. If you don't save it (ie click cancel) then go back
to save it later it has lost the filename data. That isn't really a
problem, i was just curious why it did this.

The other thing that may be nice would be to have it so that if the
date range covers two months, or two years it puts in the names of
those months or years. ie at the moment it says 29 - 5 July 08.doc as
the filename. How woudl i get it to say 29 June - 5 July 08.doc (but
only if it crosses two months). The same goes for a year.

Thanks so much for everythign already though

 

[chris_huh]

I moved the AttachedTemplate code to be after the filesave info and it
works now. I have now got all of the code in the ThisDocument thing:

Private Function WeekStart() As Date
 Dim wday As Byte
 wday = Weekday(Date)
 Select Case wday
  Case 1
   WeekStart = Date
  Case 2
   WeekStart = Date - 1
  Case 3
   WeekStart = Date - 2
  Case 4
   WeekStart = Date - 3
  Case 5
   WeekStart = Date - 4
  Case 6
   WeekStart = Date - 5
  Case 7
   WeekStart = Date - 6
  End Select
End Function

Private Sub Document_New()
Selection.GoTo What:=wdGoToBookmark, Name:="DateRange"
Selection.InsertBefore Format(WeekStart() + 7, "dddd, d MMMM")
Selection.InsertAfter " to "
Selection.InsertAfter Format(WeekStart() + 13, "dddd, d MMMM yyyy")
End Sub

Sub FileSave()

Sunday = Format(WeekStart() + 7, "d")
Saturday = Format(WeekStart() + 13, "d MMM yy")

FileTitle = Sunday & " - " & Saturday & ".doc"

With Dialogs(wdDialogFileSaveAs)
    .Name = "J:\Editorial\Magellan\Sickness Absence\Afghan\" &
FileTitle
    .Show
End With

ActiveDocument.AttachedTemplate = NormalTemplate

End Sub

That works so that when you go file save (or close it and say yes i
want to save) it opens the dialog box with the title already inserted.
Which is perfect. If you don't save it (ie click cancel) then go back
to save it later it has lost the filename data. That isn't really a
problem, i was just curious why it did this.

The other thing that may be nice would be to have it so that if the
date range covers two months, or two years it puts in the names of
those months or years. ie at the moment it says 29 - 5 July 08.doc as
the filename. How woudl i get it to say 29 June - 5 July 08.doc (but
only if it crosses two months). The same goes for a year.

Thanks so much for everythign already though- Hide quoted text -

- Show quoted text -

Actually it works better having the AttachedTemplate at the beginning
of the FileSave sub, like you said. I just forgot. Otherwise it comes
up with the enable macros dialog for the document as before.