Inserting a template into a document and running the attached macro

[grannyM]

When a macro creates a document, I want it to go to the end of that document and insert a form and then run the macro that goes with the new form. I've tried:

Selection.GoTo what:=wdGoToBookmark, Name:="plip"
Documents.Add Template:="10-122test.dot"

This works, but it creates a second document. I want it to insert this second document into the first.

I've also tried:

 Selection.InsertFile FileName:="10-122test.dot"

This inserts the template, but doesn't run the attached macro.

Can anyone help me please?

 

[PHV]

Have you tried the macro recorder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grannyM]

yes, but it does the same thing - either creates a second document, or inserts the file without running the macro.

 

[TonyJollans]

Hi grannyM,

I don't understand what you are doing; can you give a few more details please.

You want to add content from a template to an existing (created earlier) document and then add a form (including code) to that document and then run a macro - from the template, or the code you've just created - I'm not clear which. Sounds over-complicated.

Can you not just open / create a new document BASED ON the template - your code in the template will run then. Or do you really need to add this form independently from the rest of the process?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[grannyM]

Thanks you Tony for your response, I'll try to clarify.
The request I've been given is to have a macro create a form pulling info from our database. Once the first form is created, there is another form that also needs to pull info from the database that they want inserted at the end of the first form - making one continuous document. I can easily call the second form and have 2 documents open on their desktop. Or, I can create both documents, then insert the second into the first, but the formating doesn't always hold. Or I could combine the 2 forms into 1 and write the macro so that it fills out both forms, but I don't want 2 versions of the 2nd form on the system. I was hoping someone else would have a solution I hadn't thought of.

Thanks,
Granny

 

[fumei]

Yes, I am not sure you have a clear understanding of how templates are supposed to work. Templates are never to be inserted into an existing file. Templates are cloned to make a file.

Here is what I suggest - that is if I understand what you are trying to do, which of course I may have totally incorrect. I will first describe what I believe you want to happen.

1. You have an existing file.
2. You want to go to the end and insert some new content.
3. The content is adjusted as the result of user input.
4. This input is determined by user manipulation of a "form"

OK. Unfortunately the term "form" is a very loosy-goosey one. It would help if you described exactly what you mean.

A form could be a UserForm - an independent floating form that gathers information, and upon the user clicking an "OK" button (or something like one), takes the information and inserts it into a document,

OR;

A form could be a document with form fields that are entered by the user.

In any case, a possible solution is ONE template that clones into the "first" file, and when the user is ready, they can click a toolbar button, and the second "form" could appear and be completed. The loading of the second form could easily be automated, depending on design. if you use form fields, the last one could fire the next events. In any case, once complete, the information is appended to the "first" file. The point being though is that it is really ONE file, but with the second part flexible and dependent on user input.

An alternative solution is have your first file, when ready, open a document, NOT a template. THAT second file has a Document_Open macro that does whatever it is you need - gather information...whatever. In the Document_Close of that second file, it makes a copy of itself and moves it over to the "first" file. It then closes without saving changes. The first file now has the contents of the second file appended to the end.

These are DESIGN issues, and if you can describe what you need a wee bit more I am sure we can describe a viable solution.


Gerry

 

[grannyM]

We have a macro that creates a Notice. This notice pulls client info from a database and fills in certain fields in the notice. Then a box pops up and the user fills in more information that is inserted into the notice. Then we have a second macro that creates an Issue Paper dependent on the type of client. This macro only looks at the database to find out what type of client we need the paper for. They want them as two separate macros because they don't always need both, but they would like them as 1 document when they do need both.

Does this help?

 

[TonyJollans]

Hi GrannyM,

I've tried a couple of times to phrase a response and keep getting tied up because I'm afraid I still don't understand what you want here. One of us, probably me, is getting confused between the means and the end.

What is your desired End Result? A Form to be filled in later, or a document containing data? If the latter, is there any user input directly into the document - or is it all into 'boxes that pop up'?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[grannyM]

Tony, my desired end result is a document to be sent to a client that contains data. I've managed to accomplish what I wanted by putting the following code at the end of the first macro:

 Set doc = ActiveDocument
System.ProfileString("MacroVariables", "LoanNotice") = "true"
System.ProfileString("MacroVariables", "PlanType") = PlanType$

Application.StatusBar = "Opening form 10-122..."
Selection.GoTo what:=wdGoToBookmark, Name:="plip"
Documents.Add Template:="10-122test.dot"
System.ProfileString("MacroVariables", "LoanNotice") = "false"
    
    Selection.HomeKey Unit:=wdStory
    ActiveDocument.SaveAs FileName:="i:\tempNotice"
    ActiveDocument.Close
doc.Activate
    Selection.InsertFile FileName:="i:\tempNotice"

However, I'd prefer to be able to do this without saving the 2nd file if possible.

Thanks,
Granny

 

[fumei]

This is a very odd way of doing things.

1. You are accessing the Registry in order to run a macro?

2. You are adding a new document, and not only that a .dot template file - this is very bad form - then saving it as a temp file;

3. then inserting that saved temp file.

As Tony mentioned there is confusion re: means and end.

I will repeat. Using a .dot template that is OPENED to users is not the purpose of .dot files. In fact, using a .dot file this way is absolutely pointless. You may as well simply use a .doc file, because that is what you are using it for. The fact it is, a .dot file becomes completely irrelevant if you open the file itself.

I asked about the actual steps you wish to take, with no solid response.

Make the whole thing ONE .dot file and build everything into it, including whether you need form 2, or not. As you do not specify what your real specs are - do they ALWAYS need the contents of "10-122test.dot"?? You do not state if this is so.

If they DO always need the contents, and form, within 10-122test.dot, then build the whole thing into ONE template. If they do NOT, but you need to give them either a choice (their choice), or there is LOGIC that requires 10-122test.dot, then build that into the ONE template.

You do not state what fires, or needs to fire the form within 10-122test.dot. You do not state why there is some design need to keep these documents separate. Is there a real need?? If not, then again, make them ONE document. The contents can be adjusted however you need them to be.

I can not stress how this is a DESIGN issue.

For example, I work with documents that require that they be in both English and French. Some documents have five "chunks" (or actually 10 chunks, as each needs to be both English and French). Each chunk has its entire text - some them 5 pages long) as actual bookmarks. There is logic with the Document_Open event that determines, by user choice, which chunk is valid for the current purpose. After confirmation (of course!), the macro totally removes the unneeded chunks.

It is a financial document. If the user selects to act on a Journal Voucher document, the Document_Open macro removes, totally, the other irrelevant parts (Cost Recovery , or parts) before continuing. In other words, the full template is some 25 pages, but after the users selects what type of financial tranction, the document (cloned from a .dot file) reconfigures itself into, oh, maybe a 4 page document. Sometimes, it is much easier to remove things, than add things.

This is, again DESIGN. You could have all the 10-122test.dot macro capabilities WITHIN the original file, and if required, these will fire. No need to go outside, or make a temporary file you save and bring back in. Totally unneeded. Also unneeded is to go out to the registry to fire a macro in another file.

But hey, if it works for you....

Gerry

 

[grannyM]

Gerry, I'm sorry I'm not being very clear. I'm not very good at putting my thoughts down, I'd much rather talk it out with someone! But I'll try again. Thanks so much for your patience.

There are pages of code from both macros that I didn't include. The main problem is that these 2 macros have been around separately for a couple of years as separate entities used by different departments. Now one of the departments wants to be able to run just one macro to get both forms, but of course the other department wants to keep them separate. We do not want to have 2 versions of the same form in 2 different templates, because that would mean you'd have to make sure both were updated. I could combine the 2 forms into one, but that means retraining a bunch of users and I was trying to avoid that. If I was creating both of these from scratch, yes the best option would be to put both in the same template and have the macro delete what isn't needed.

Here's how what I have now works.

The user goes to File/New and selects a template that initiates the macro when they double-click on the icon (ours are all numbered so this would be 10-121.dot) 10-121.dot has an autonew module that creates a document. The macro then pulls a lot of information from our database and asks a lot of questions thru the use of dialog boxes. The answers to these questions determine and the information pulled from the database determine which parts of the form are applicable. (Just as you say, we have everything in the form and we delete what isn't needed).

The new part I added at the end (and noted above) was I sat a variable in the registry that basically says that the first macro is still running. The "Documents.Add Template:="10-122test.dot" line runs the macro in 10-122test.dot, it doesn't open the template. The code in the second template checks first to see if the variable I mentioned above has been set to true. If not, it knows it needs to ask the user some of the same questions from the 1st macro and pull the info from the database, but if it is true, it doesn't need to ask the user again.

After creating the second document, I saved it as a temp document, then inserted it into the 1st document. Again because we don't want 2 versions of the same form out on the system. I didn't like this method of creating a temp document, but couldn't figure out how else I could insert the document created into the first document.

Does this help? Thanks again for your patience.

 

[fumei]

OK, I see your problem, and frankly, considering the restraints there is not much else you can do.

However, I do have a question.

"The code in the second template checks first to see if the variable I mentioned above has been set to true. If not, it knows it needs to ask the user some of the same questions from the 1st macro and pull the info from the database, but if it is true, it doesn't need to ask the user again. "

IF variable = False - how does it "know" to ask the same questions? This doesn't make sense to me.

If the first macro is "still running", WHAT is firing the second???? I do not see an IF or anything else checking the status. I see there are setting status as true, and false, but none checking to see if the first macro is "still running". I am not following that.

If not, it knows it needs to ask the user some of the same questions from the 1st macro and pull the info from the database, but if it is true, it doesn't need to ask the user again.

OK, if it IS true, it doesn't need to ask the user again. But then.....what DOES it do? Nothing? Does it do something else that ahs nothing to do with the first part of the document, or the first macro.

What a mess.... I applaud your patience getting it working as well as it seems to. As I am still unclear what the second file is really doing, I'm not sure what I can suggest. You can of course RUN a procedure in another module, in another file. Maybe you can just run the other macro from the first one.

Shrug.

Gerry