Macro help (open doc from template - then close template)

[MarkC]

Hi all, Can anyone help please. I have the following macro which runs based
on a selection of 'Y' or 'N' in an excel spreadsheet which opens a word doc
from a word template. My question is how can this be modified to open the
word doc based on the template, but close the actual template once the new
doc is openend. Currently when you save the new document, it also asks if
you wish to save ythe template(basically overwrite), which leave the document
template open to be overtyped!!


Sub GetWordDocument()
Dim oWordApp As Object
Dim oDoc As Object
Dim sFilename As String

Set oWordApp = CreateObject("Word.Application")
If Range("G3").Value = "Y" Then
sFilename = "M:\001_Quality\Manual advice Notes\Template\ST011AFO
Issue 1 Advice note AND Customs invoice.dot"
Else
sFilename = "M:\001_Quality\Manual advice Notes\Template\ST011FO
Issue 4 Manual Advice Note.dot"
End If
Set oDoc = oWordApp.Documents.Add(sFilename)
oWordApp.Visible = True
Set oDoc = Nothing
Set oWordApp = Nothing
End Sub


Private Sub CommandButton1_Click()

End Sub

THANKS ALL

 

[Bear]

Mark:

It's necessary for the attached template to be open while the document is
open.

You should determine what's revising the template and prevent that (if
possible). It you must revise the template (say changing the face of a
button) then you can tell Word that the template wasn't changed by saying:

ThisDocument.Saved = True

in your template code just after the change is made.

A second strategy would be to create the document, then attach a different
template to it. This would close the first template, but leave a desired
template, such as Normal.dot open.

Bear

 

[MarkC]

Thank you for your responce, where do i add the comment into the macro (the
macro was written for me), thank you

 

[Bear]

Marc:

Please say where your macro is stored. Is it in the document template? Or
somewhere else?

Bear

 

[MarkC]

sorry, its in the excel workboot. We basically use a spreadsheet to allocate
a number to a text box on the word form & the word template is selected the
'Y' or else part of the macro. The macro then creates a word doc form the
template, but when we save the word doc it also asks if we want to save
changes to the template

 

[Russ]

Sounds like you are opening the template, when you should be opening a new
blank document, based on the template.

You can open a blank document and attach a template with this code and as
long as the macros in the template are not self-modifying the template (i.e.
Storing a variable value) the new document should close cleanly.

Word.Application.Documents.Add Template:=myFullPathToTemplate

 

[Bear]

Mark:

I've exercised the macro and have a better idea what you're doing now. When
that checkbox is Y, your user sees Word with an unnamed copy of one or the
other note documents. The user then saves the doc and names it, and
eventuallly closes the doc and exits from Word.

If you're getting messages that the template needs saving, that means that
simply opening the template in the process of creating the document is
causing it (the template) to change (most likely a link or field that's being
updated). Here's how I'd ammend the code.

Sub GetWordDocument()

Dim oWordApp As Object
Dim oDoc As Object
Dim sFilename As String

Set oWordApp = CreateObject("Word.Application")

If Range("G3").Value = "Y" Then
sFilename = "M:\001_Quality\Manual advice Notes\Template\ST011AFO Issue
1 Advice note AND Customs invoice.dot"
Else
sFilename = "M:\001_Quality\Manual advice Notes\Template\ST011FO Issue 4
Manual Advice Note.dot"
End If

Set oDoc = oWordApp.documents.Add(Template:=sFilename)
oDoc.attachedtemplate.Saved = True

oWordApp.Visible = True

Set oDoc = Nothing
Set oWordApp = Nothing

End Sub

The parameter name "Template" makes it clear that you're specifying which
template to use, and not a document path or name. The
oDoc.attachedtemplate.Saved = True statement is a little white lie you tell
to Word. You're changing the flag Word sets to indicate the template was
changed and needs to be saved.

Bear

 

[MarkC]

perfect - Thank you very much for your help

 

[Bear]

Glad I could help out. And thanks for rating the post!

Bear