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

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

david
Tue Jun 26 08:42:00 CDT 2007

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

--
Windows XP, Word 2000


"MarkC" wrote:

>
> 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

MarkC
Mon Jul 02 02:22:00 CDT 2007

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

"Bear" wrote:

> 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
>
> --
> Windows XP, Word 2000
>
>
> "MarkC" wrote:
>
> >
> > 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

david
Mon Jul 02 08:06:02 CDT 2007

Marc:

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

Bear
--
Windows XP, Word 2000

MarkC
Wed Jul 04 07:42:01 CDT 2007

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

"Bear" wrote:

> 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
>
> --
> Windows XP, Word 2000
>
>
> "MarkC" wrote:
>
> >
> > 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

Russ
Wed Jul 04 19:10:01 CDT 2007

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


> 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
>
> "Bear" wrote:
>
>> 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
>>
>> --
>> Windows XP, Word 2000
>>
>>
>> "MarkC" wrote:
>>
>>>
>>> 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

--
Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

david
Thu Jul 05 09:14:23 CDT 2007

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
--
Windows XP, Word 2000

MarkC
Wed Jul 11 02:26:03 CDT 2007

perfect - Thank you very much for your help

"Bear" wrote:

> 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
> --
> Windows XP, Word 2000
>
>

david
Wed Jul 11 12:34:03 CDT 2007

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

Bear

--
Windows XP, Word 2000