Open Template as a NEW Doc

[mvesaas]

:dunno Does anyone know how to open a NEW document based on a template, not the actual template itself?

I have been trying to figure out how to call the procedure from WORD to open a new document.doc based on my template, not the actual template.dot.

Thanks :whistle:

[fumei]

Record a macro doing the exact steps - File > New, and then selecting the template you want.

Then look at the code. It shows you exactly how to do it.

You can also attach a template to an existing document using the .AttachTemplate method. Look in Help.

[mvesaas]

Okay, there's a twist I didn't realize: In order for my project to work the way I want it to....

I have to be able to open the Word Template as a new Doc from EXCEL.

Any ideas? :doh:

[MOS MASTER]

Hi, :yes

Use office automation.

This is Early binding so make sure you reference Word from Excel!:
Option Explicit
Const sTemplate As String = "C:\Test.dot"
'Go to Tools | References and add reference to Microsoft Word ** Library
Sub OpenWordTemplate()
Dim oApp As Word.Application
On Error Resume Next
Set oApp = GetObject(, "Word.Application")

If Err <> 0 Then
Set oApp = New Word.Application
End If

With oApp
.Documents.Add Template:=sTemplate
.Visible = True
End With

Set oApp = Nothing
End Sub
HTH, :whistle:

[mvesaas]

:banghead: When I run this code, it is opening a new Excel workbook, and then running my macros from the Word Template on the Excel wkbk. Am I placing the macro in the wrong place? In my excel workbook, this is the code I have so far.... :help


Sub CopyQuote()
' CopyQuote Macro - Use to extract data for the QUOTE LETTER
ActiveWindow.SmallScroll Down:=135
Range("A1:O178").Select
Range("O178").Activate
Selection.Copy

Sub Goodbye()
vAnswer = MsgBox(Prompt:=" " & Chr(13) & _
" *** DATA COPIED *** " & Chr(13) & _
" ", Buttons:=vbOkay)
If vAnswer = 1 Then
Closeout
Else
Exit Sub
End If
End Sub

Sub Closeout()
'SAVE the MATRIX and CLOSE
Range("A1").Select
ActiveWorkbook.Save
'Go to WORD doc
OpenWord
End Sub

Sub OpenWord

'PLACE CODE HERE to open the word template as a new doc

End Sub

[MOS MASTER]

Ok totaly missing what you are doing here!

But you should automate that Excel app as well and run All code from that instance the way I showed you to do it for Word.

I think your design is very messy and I don't have time now to give you a proper example.

Please write down step by step what you want to from Word with Excel so we can help you automate it. :whistle:

[mvesaas]

:wot Okay, my design is messy looking because there are about 50 things going on in the background at the same time.

What I would like to do:

The project the user will be working in, is an Excel File. This is an elaborate system designed by our actuaries to review/price each client account. My job was to create a word letter that pulls from the elaborate spreadsheet, and depending on about 500 variables, creates a detailed 5 page quote letter to send to the client.

The user will be working in the original Excel file to price each account. I have added a "quote letter" tab to the file, and added a button for the user to click, which will begin the letter creating process. The first step, is to COPY the data from that workbook to another. This is a prep step that I have already automated on the click of the button.

NEXT With the same click of button, I would like to automatically open the Word template from the same Excel file. All of my quote letter code is in the WORD file. If I can't automate opening the word file, then the user will have to go manually upen Word to begin processing the letter. (This works, but it is not ideal, obviously)

Help! Right now, I have automated opening the word file, but when the Word template is opened from Excel, I am actually opening the .dot template - not a new .doc. :help

:doh: Sorry for all the confusion, right now I have the entire thing completed and automated except for this one thing. My quote letter, thanks to your help, processes in about 45 seconds, and comes out perfect each time!

[MOS MASTER]

Help! Right now, I have automated opening the word file, but when the Word template is opened from Excel, I am actually opening the .dot template - not a new .doc. :help

This is not the detail I was looking for but I'll try to post the code to get you started tomorrow.

Look at the code I previously posted. Check if you're using the Add method instead of Open!!

Otherwise post the code you're using to open a new instance of your Word template. :whistle:

[mvesaas]

:dunno

This is the code (in the excel file) that is working so far ....
but it is opening my actual template (obviously), when I would like it to open a new .doc based on the template.


Sub OpenWord()
' Paste it into an Excel module
' Reference to the Word-library
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim i As Integer
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
'Set wrdDoc = wrdApp.Documents.Add ' create a new document
' or
Set wrdDoc = wrdApp.Documents.Open("S:\Quote Letter MERGE v4.0.dot")
' open an existing document

End Sub

[MOS MASTER]

Read my comments again and tell me why this code is opening your template as template? (We have adviced you to use a other method) :*)


:dunno
Set wrdDoc = wrdApp.Documents.Open("S:\Quote Letter MERGE v4.0.dot")

[fumei]

Ooops Joost got there again.

mvesaas - you MUST organize your code. You MUST read the instructions you are asking your system to do.

Set wrdDoc = wrdApp.Documents.Open("S:\Quote Letter MERGE v4.0.dot")

is what you put as an instruction. Read it. Is it not fairly clear that Open...means "open", and the file that is OPENED is, in fact, the .DOT file.

So, OK what do you want to do? You DON'T want to open the .dot file.

Walk slowly, if your code is messy because there 50 things going on....it is BAD CODE, and will remain BAD CODE. There is no code that has 50 things going on in the background (OK, OK, you purists, yes you CAN have multi-tasking). Your code fires instructions. It fires then sequentially, one instruction followed by another. Your code should ALWAYS keep that in mind.

Write it out, step by step. I am not like Joost, I do not always hand out code. Sometimes I do, but not in this case. you have to get it.

Alright. You were told how to make an instance of Word from inside Excel. In the code you posted you have .Add commented out.....

sigh,.....

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add
wrdDoc.AttachedTemplate = "C:\test\XP_Templates\global.dot"

' do whatever else it is of the 50 things
' that is going on....

wrdDoc.SaveAs Filename:="c:\test\whatever.doc"

Set wrdDoc = Nothing
wrdApp.Quit
Set wrdApp = Nothing

Please read posts carefully.

1. make the instance of word
2. makes that instance visible
3. use that instance to make a new document
4. gee....uses the AttachTemplate method to, well attach a template....not open one.

Please look in Help, and make sure you properly Quit the instance of Word, and also destroy the app objects ( set = Nothing).

I know I sound annoyed, and I apologize. Let's walk through some more...

The user will be working in the original Excel file to price each account. I have added a "quote letter" tab to the file, and added a button for the user to click, which will begin the letter creating process. The first step, is to COPY the data from that workbook to another. This is a prep step that I have already automated on the click of the button.

NEXT With the same click of button, I would like to automatically open the Word template from the same Excel file. All of my quote letter code is in the WORD file. If I can't automate opening the word file, then the user will have to go manually upen Word to begin processing the letter. (This works, but it is not ideal, obviously)

1. i would like to see the code that does rather different things with "the same click of button". Do you mean the SAME button? Or...a different button.

2. You state the code is in the template ( the .DOT file). This WILL fire if it is in Document_New, AND (and a huge huge huge and!) if you make the new file the way I suggest in my first post. I did not post code, and I will not now. Put the code to create a new document FROM a template into the code that is using wrdApp.

The code above does not do this. It makes a new document, and ATTACHES the template. Attaching a template does NOT fire Document_New.

My suggestion in my first post on how to create a new document FROM a template is still valid.

Use it. You can attach the template and fire your template code from a module. It would not be automatic, and it can be creating a document from a template. you would have to instruct VBA to fire that code.

[mvesaas]

I found out why, when I used the first code you referenced in the beginning (see below), it was operating screwy and starting inside excel rather then word.

This was because my template started off as a mail merge doc, so when you called the Word file to open from an Excel macro, it also called its data source.xls. Excel and Word seemed to be getting bogged down in this "switching" back and forth process.

I changed my intial template, so that it opens as a regular doc, then wrote a macro to change it into a mail merge after it has been opened.

Thanks for your help :yes


Option Explicit
Const sTemplate As String = "C:\Test.dot"
'Go to Tools | References and add reference to Microsoft Word ** Library
Sub OpenWordTemplate()
Dim oApp As Word.Application
On Error Resume Next
Set oApp = GetObject(, "Word.Application")

If Err <> 0 Then
Set oApp = New Word.Application
End If

With oApp
.Documents.Add Template:=sTemplate
.Visible = True
End With

Set oApp = Nothing
End Sub

[MOS MASTER]

Ooops Joost got there again.


Meaning? :yes

[MOS MASTER]

Hi M, :yes

No problem.
I'm glad to see you're making progress in your App.

So sorry however that I haven't got a clue on how it works. But if it works for you it works.

Later..:whistle:

[fumei]

that you are too fast!

that you made the comment re: "messy"

and I am too fast too. I posted an cranky post with no reason. I am too tired for this. I am leaving now.

"Seeya" Joost.

mvesaas - glad you got it working. Sorry if I was kinda wonky...I should never post when glucose levels are low.

[MOS MASTER]

Hahaha...Ok Gerry take care buddy! :yes

I should be pausing for a while too because I'm getting a bit off too....:giggle