Solved: Disable Template Save prompt during Macro

[FhM]

I wrote some code in response my problem I had here with sanitizing the output of a 3rd party program.
http://www.vbaexpress.com/forum/showthread.php?t=28631

This is what I came up with:
Sub CleanUpReports()

Dim rng As Range
Dim MainDoc As Document
Dim strFile As String
Dim strOriginalLocation As String
Dim strNewFileLocation As String


strOriginalLocation = "X:\SIMS Reports\Year 10 IPDC\Originals\A\"
strNewFileLocation = "X:\SIMS Reports\Year 10 IPDC\Originals\A copied"

strFile = Dir$(strOriginalLocation & "*.xml")

Do Until strFile = ""


strFileLocation = strNewFileLocation + ActiveDocument.Name

Set MainDoc = Documents.Add(Template:="C:\MacroTemplate1.dot")

Set rng = MainDoc.Bookmarks("\EndOfDoc").Range


rng.InsertFile strOriginalLocation & strFile

strFile = Left(strFile, Len(strFile) - 4)
'MsgBox strFile
ChangeFileOpenDirectory "X:\SIMS Reports\Year 10 IPDC\Originals\A copied\"
ActiveDocument.SaveAs FileName:=strFile, FileFormat:=wdFormatDocument
ActiveDocument.Close SaveChanges:=False
strFile = Dir$()
Loop
End Sub

Which works great but every time it saves a file it asks to save the template as well which stops the code running.

I tried solutions like this:
Private Sub Document_Close()
On Error Resume Next
ActiveDocument.AttachedTemplate.Saved = True
End Sub

and have unticked all the options in word etc but it still asks. Can someone put me straight.

Thanks in advance.

[Tinbendr]

I tried solutions like this:
Private Sub Document_Close()
On Error Resume Next
ActiveDocument.AttachedTemplate.Saved = True
End SubThis is the 'official' way to do it, or so I've read.

Try

MainDoc.Save FileName:=strFile
MainDoc.Close SaveChanges:=False

[carrrnuttt]

If the document that contains the executing code is the template, then you should try

ThisDocument.Close SaveChanges:=wdDoNotSaveChanges

as opposed to using "ActiveDocument."

[FhM]

Ok thanks for your suggestions everyone I think I have managed to crack the problem the solution was fairly straight forward in the end.

Code changed from:
strFile = Left(strFile, Len(strFile) - 4)
'MsgBox strFile
ChangeFileOpenDirectory "X:\SIMS Reports\Year 10 IPDC\Originals\A copied\"
ActiveDocument.SaveAs FileName:=strFile, FileFormat:=wdFormatDocument
ActiveDocument.Close SaveChanges:=False
strFile = Dir$()


to
strFile = Left(strFile, Len(strFile) - 4)
'MsgBox strFile
ChangeFileOpenDirectory "X:\SIMS Reports\Year 10 IPDC\Originals\A copied\"
ActiveDocument.AttachedTemplate.Saved = True
ActiveDocument.SaveAs FileName:=strFile, FileFormat:=wdFormatDocument
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
strFile = Dir$()

I noticed when stepping through the code that the close code is called after the prompt to save the template so that was never going to work. Thanks again , all input greatly appreciated.

[FhM]

Ok I have hit a snag. Although this works as intended I wanted it to be portable. If the documents are opened on a laptop that isn't connected to the network, then they don't have access to the macros as they are derived from that template. How do I get the macros to be part of the document and not just looked up from the template?

[FhM]

I am currently thinking that I will make sure the template is installed on all the machines unless someone has an easier solution?

[fumei]

"I am currently thinking that I will make sure the template is installed on all the machines unless someone has an easier solution?"

Yes, making sure the template is available is one route, actually the "normal" route. However...

"How do I get the macros to be part of the document and not just looked up from the template?"

Here is how.

1. Export the module as a file. Right click the module in Project Explorer and select Export. Give it a name. It does NOT have to be the existing name of the module.

E.g. I have (in the .DOT template file) a module using the default name of Module1. I can export it as:

c:\zzz\Modules\Test1.bas

Now that module is a separate file.

2. Import that file as part of the Document_New event of the template. Example:
Private Sub Document_New()
ActiveDocument.VBProject.VBComponents.Import _
"c:\zzz\Modules\Test1.bas"
End Sub


Now when the template is used to clone a new document - the Document_New events fires - it imports the VBA code module into that document. That is, the document itself will now have that module with all procedures.

Therefore the document does not need to call to the template file...the code module is actually in the document now.

[geekgirlau]

I haven't seen this technique used before Gerry. Do you need to allow trusted access to the VBE for this to work?

[FhM]

"I am currently thinking that I will make sure the template is installed on all the machines unless someone has an easier solution?"

Yes, making sure the template is available is one route, actually the "normal" route. However...

"How do I get the macros to be part of the document and not just looked up from the template?"

Here is how.

1. Export the module as a file. Right click the module in Project Explorer and select Export. Give it a name. It does NOT have to be the existing name of the module.

E.g. I have (in the .DOT template file) a module using the default name of Module1. I can export it as:

c:\zzz\Modules\Test1.bas

Now that module is a separate file.

2. Import that file as part of the Document_New event of the template. Example:
Private Sub Document_New()
ActiveDocument.VBProject.VBComponents.Import _
"c:\zzz\Modules\Test1.bas"
End Sub

Now when the template is used to clone a new document - the Document_New events fires - it imports the VBA code module into that document. That is, the document itself will now have that module with all procedures.

Therefore the document does not need to call to the template file...the code module is actually in the document now.

Thank you :cloud9: That is exactly the kind of thing I was looking for. I assume I can use the same command to import my form as well?

[FhM]

I haven't seen this technique used before Gerry. Do you need to allow trusted access to the VBE for this to work?

You mean in case you get a run time error '6068'

Programmatic access to Visual Basic Project is not trusted.

Then yes I have just come up against that. http://support.microsoft.com/kb/282830

I have tried the code and took the document away to a laptop not connected to the network. I looked in the editor and found all the code and the form and I thought great. I fired up the form and up it popped, I made my selection and then error!!!!!!! It is saying that a library is missing grrr.

Private Sub cmdOK_Click()
Dim aRange As Range
Dim strTeacherSig As String
Dim intNumBookmarks As Integer
Dim objBookmark As Bookmark
Dim objSelectedBookmark As String
intNumBookmarks = ActiveDocument.Bookmarks.Count
If intNumBookmarks >= 5 Then
Dim ctl As Control
For Each ctl In fraChoices.Controls
If ctl = True Then
For Each objBookmark In ActiveDocument.Bookmarks()
If objBookmark.Name <> _
Right(ctl.Name, Len(ctl.Name) - 3) Then
objBookmark.Range.Delete
Else
objSelectedBookmark = objBookmark.Name


End If
Next
Exit For
End If
Next
End If
Unload Me
If intNumBookmarks < 5 Then
MsgBox "It looks like you have already made a selection " & vbCrLf & "or there is a problem."
End If
' Deal with signature
For Each ctl In titleChoices.Controls
If ctl = True Then
strTeacherSig = Right(ctl.Name, Len(ctl.Name) - 6) & Chr(32) & TextBoxSurname.Value
MsgBox strTeacherSig & objSelectedBookmark
ActiveDocument.Bookmarks(objSelectedBookmark).Range.InsertAfter vbTab & strTeacherSig
End If
Next
End Sub

The line in bold is the error. This code works perfectly as I have tested it. If I make sure the template is available it then works. So basically I have come full circle back to my original error. Very frustrating.

[fumei]

1. Please use the underscore character to break up code lines. Your code is difficult to read.

2. Are you saying you are getting a 6068 error for that line (Dim ctl As Control )?

3. It is better to be explicit.
Dim ctl As Control
For Each ctl In fraChoices.Controls
If ctl = True Then
if ctl is - say - a Label (or a textbox), then ctl = True is meaningless. The syntax ctl = True evaluates the default property of the object - ctl.

4. Dim ctl As Control requires that Microsoft Forms 2.0 Object Library be a checked (available) Reference.

5. Yes, there most certainly can be an issue with Trusted, if you are making calls to the VBE Object model itself...like Controls on a userform. However, there has been NO previous mention of a userform in this thread. The question was about macros (procedures) being ported over to a document so procedures can be executed without the availability of the template (.DOT) file.

And they can be. The very fact that the modules were present in the document opened on the laptop proves this, as well as the fact that the userform could be opened (requiring an executed .Show).

In other words, yes, you can import (as .BAS files) code modules into a document created from a template. Further, procedures (macros) in those code modules can be executed, without the availability of the .DOT file template.

BUT...as the 6068 error clearly indicates, there IS a problem if the code attempts to manipulate the VBE environment, or Object Model.

[fumei]

BTW: with the following:
' other stuff
Next
End If
Unload Me
If intNumBookmarks < 5 Then
How do you expect If intNumBookmarks < 5 Then to execute...if you have already unloaded the form?

[FhM]

I will post a sanitised version up to give a full picture. The majority of the code in there was from an example given to me on here http://www.vbaexpress.com/forum/attachment.php?attachmentid=1926&d=1254421176

The control in question is a radio button to select 1 to 5 for the letter selection. I will post the full thing tommorow so things are clearer.
As for the intNumBookmarks, it does seem to work for me and it is assigned higher up so it keeps it's value. I will look at moving it though if it's not best placed.

[FhM]

I found an example so I will post it up.

2 Docs:

Not working: This is the document produced from the template with the import routine for the VBA. Once you press ok on the form you get an error.

Working: I made this one fresh, but I did export the module and form from the Not Working document. They should be identical. This one works fine :banghead:

Hmm not working posted below.

[FhM]

The Not Working file.

[FhM]

2. Are you saying you are getting a 6068 error for that line (Dim ctl As Control )?


No I fixed that, that is why I posted the link for others reference. The error is missing reference or Library. If you try the files you should get the same error with the "Not Working" file.

[FhM]

Right after some more testing I have found the error goes away and it works perfectly if I untick the Reference to the missing template. This is the same problem as what I had originally when trying to use the output from the 3 rd party program I was using (See link in first post). What I find odd is this doesn't seem to be a problem for other people.
I am going to try to build the files from the template using a local path tommorow to see if that makes any difference as the template location is currently pointing to a network (DFS) path. I doubt it will but you never know.
Anyone come across anything like this before?
If I cant fix it I am going to look at this as suggested in the other post


References are a collection, and can be accessed via code.

VBE.ActiveVBProject.References

This sounds very messy though.

and try and remove the reference.

[fumei]

You are correct, the error comes from the MISSING Reference (your ReportSelectionTemplate.dot).

If you look in the File > Properties it shows the attached template being ReportSelectionTemplate.dot, and obviously if that file is not available any reference to it will be MISSING.

Unfortunately, to work with the References you need...a Reference - Microsoft Visual Basic for Application Extensibility. Try putting that in your template file so it will be a reference in the document produced by that template.

However, unfortunately, that still will not work as VBA can not use it (to remove the MISSING reference) because there is a MISSING. A sort of chicken and the egg paradigm.

What to do? Since the purpose to to have a document that is NOT attached to ReportSelectionTemplate.dot, then you will need to remove that attachment. Thus, it will not be MISSING.

So...........

Use ReportSelectionTemplate.dot to create the document, import the code modules/form, and then use a procedure to change the AttachedTemplate to not be ReportSelectionTemplate.dot. Make it Normal.dot. Like this:
Private Sub Document_New()
Dim strNormalPath As String
' make the string the "normal" path to Normal.dot
strNormalPath = Options.DefaultFilePath(wdUserTemplatesPath) & _
"\normal.dot"

' import the code module and userform
ActiveDocument.VBProject.VBComponents.Import _
"c:\zzz\Mods\modTesting.bas"
ActiveDocument.VBProject.VBComponents.Import _
"c:\zzz\Mods\frmTest.frm"

' UNattach the template by attaching Normal
ActiveDocument.AttachedTemplate = strNormalPath
End Sub


This will work to import the userform/modules, and will NOT have any issues with MISSING references - assuming of course that normal.dot is in the normal place...

However, you still have a problem.

Say, for example, in the creating template (ReportSelectionTemplate.dot) you have either a shortcut key, or even a menu icon to fire the procedure to Show the userform.

These will NOT - repeat NOT - be there.

If you notice you have a comment that Alt-6 executes the .Show of your userform. In the created document no, it does not. That shortcut key remains with the template.

[FhM]

Thank you for the thorough answer. I had tried unsucessfuly today to use code to remove the reference but I obviously ran into the the problem you describe. I found it did work but only if you let it error first.
I have got some code to add my button to the top so I am hoping that will work fine under the context of the Document_New() event. The keyboard shortcut is left over from the original example that I have modified so I dont have to worry about that.
Thanks for sticking with me through this. Hopefully tommorow I will have a big smile when eveything is perfect :D

[FhM]

Right the changing of the template works great :cloud9:. My main routine works fine.
For the icing on the cake I need to get my button in place. I have the code:

Sub AddMenuButton()
Dim cbcb As CommandBarControl
Set cbcb = CommandBars("Menu Bar"). _
Controls.Add(Type:=msoControlButton)
With cbcb
.Style = msoButtonCaption
.Caption = "MakeYourSelection"
.Visible = True
.OnAction = "macro1"
End With
End Sub
Which works fine but it targets the template file rather than the new file and as a result I now have 50 buttons on the template file:rotlaugh:.
Now I know by looking at the code you have already supplied me that it will have something to do with using

.ActiveDocument
But I can't seem to get that right. I did try:

Sub AddMenuButton()
Dim cbcb As CommandBarControl
Set cbcb = ActiveDocument.CommandBars("Menu Bar"). _
Controls.Add(Type:=msoControlButton)
With cbcb
.Style = msoButtonCaption
.Caption = "MakeYourSelection"
.Visible = True
.OnAction = "macro1"
End With
End Sub
But that still seems to target the template.

I have other ideas such as running the macro on a document_open event but since these events seem to have to be under the context of "ThisDocument" and you can't seem to import code into them I am a bit stuck. I was going to enumerate the buttons or if needed of use a document variable, which I read about last night but that is a non starter as I can't put the document_open in the right place.

I am so close now and I am very grateful for all your help :beerchug:

[fumei]

"I have other ideas such as running the macro on a document_open event but since these events seem to have to be under the context of "ThisDocument" and you can't seem to import code into them I am a bit stuck. "

But you can write to ThisDocument.

1. Write your code for the button creation (the code lines that are going to go into ThisDocument) into a plain-text file - say, MakeButtonString.txt.

2. save that file into the same place (although technically it is called by a PATH, so technically it does not have to be in the same place...but let's try to make it simple, yes?

3. Get rid of your procedure to actually .Show your userform. It will be in the plain-text file you are going to put into ThisDocument. Here is the text that will be in the text file.

Sub Document_Open()
Dim myBar As CommandBar
Dim myControl As CommandBarControl
CustomizationContext = AttachedTemplate
Set myBar = CommandBars("Menu Bar")
Set myControl = myBar.Controls _
.Add(Type:=msoControlButton)
With myControl
.Style = msoButtonCaption
.DescriptionText = "Display Selection userform"
.Caption = "Make Your Selection"
.Visible = True
.OnAction = "ShowUserform"
End With
End Sub

Sub ShowUserform()
frmTest.Show
End Sub
Remember though, while this shows above as if it is code, for the moment it is NOT...it is text in a plain-text file ("MakeButtonString.txt"). Although of course you have it be whatever name you like.

Review: write the button creation code into a Document_Open Sub, plus the userform .Show procedure as a plain-text file.

OK?

Now, in your template Document_New procedure, add the following:
ActiveDocument.VBProject.VBComponents("ThisDocument") _
.CodeModule.AddFromFile _
"c:\zzz\Mods\MakeButtonString.txt"
Can you follow this? This adds the text from MakeButtonString.txt into the ThisDocument module. Here is the full Document_New for the template.


Private Sub Document_New()
Dim strNormalPath As String
' make the string the "normal" path to Normal.dot
strNormalPath = Options.DefaultFilePath(wdUserTemplatesPath) & _
"\normal.dot"

With ActiveDocument ' the newly created document
' import the code module and userform
.VBProject.VBComponents.Import _
"c:\zzz\Mods\modTesting.bas"
.VBProject.VBComponents.Import _
"c:\zzz\Mods\frmTest.frm"

' UNattach the template by attaching Normal
.AttachedTemplate = strNormalPath

' add the text that becomes the code in ThisDocument
.VBProject.VBComponents("ThisDocument").CodeModule.AddFromFile _
"c:\zzz\Mods\MakeButtonString.txt"
End With
End Sub
NOW, as it stands, the button is not there in the document. But when it is saved (it isn't yet) and is opened its Document_Open will create the button.

Problems? Yes. Because the button creation code is in Document_Open anytime it is opened, a button will be created. A new button. Open it five times...there will be five buttons.

Can something be done about this? Yes. You could create a DOCVARIABLE for the document. Say, AlreadyDone. Do this from the Document_New of the template.
ActiveDocument.Variables.Add _
Name:="AlreadyDone", Value:="Nope"
Now the newly created document has a DOCVARIABLE ("AlreadyDone"), with a Value of "Nope".

In the plain-text file that will go into ThisDocument, you can change the Document_Open procedure to:
Sub Document_Open()
Dim myBar As CommandBar
Dim myControl As CommandBarControl
If ActiveDocument.Variables("AlreadyDone") _
.Value = "Nope" Then ' the following has NOT fired
CustomizationContext = AttachedTemplate
Set myBar = CommandBars("Menu Bar")
Set myControl = myBar.Controls _
.Add(Type:=msoControlButton)
With myControl
.Style = msoButtonCaption
.DescriptionText = "Display Selection userform"
.Caption = "Make Your Selection"
.Visible = True
.OnAction = "ShowUserform"
End With
' reset variable Value
ActiveDocument.Variables("AlreadyDone") _
.Value = "Done"
End If
End Sub
So.....the first time it is opened, it creates the button, and sets the AlreadyDone variable to "Done".

The second (or third, fourth.....) time it is opened, it checks the Value of AlreadyDone, which is now "Done", not "Nope", so it does NOT create the button.

Problems? Sorry...but yes there is still a problem. Notice that in the button creation code there is:
CustomizationContext = AttachedTemplate
THAT means the button is created...in Normal.dot. In other words, it will be there for ALL documents.

And unfortunately, there is nothing you can do about that - because you ARE attaching normal.dot as the template, and then doing the CustomizationContext as the attached template. Thus, the button goes into normal.dot, thus into ALL documents that use normal.dot.

All this crap stems from the fact of how Word works. ALL documents have some template attached. This is why the best (normal) thing is to have explicit templates and have them available.





Are we having fun?

[FhM]

Once again thank you for the thorough answer. Who would have thought a little thing like that would be so complicated!
Can the variable be stored in normal.dot? Because if I am following you right every time I use this code on a different report it is going to add another button as the variable is stored in a particular document. This would only be feasable if there was the one document but there is going to be thousands because there is one for every student in every subject.
Would it be possible to enumerate the command bar and look for the caption of my button to get around this for my multiple docs?
As an alternative I have been looking at fields codes to run the macros. These could be present during editing and they go to another phase where seperate reports are collated together ie all 1 page word docs for one student are collated together into a single word doc for all subjects.
That code is already written so would it be straight forward to add a clean up routine to get rid of all these macro field codes?
I know you are thinking this is a lot of work just to avoid the template but for me in the end it will be less work if these documents can be loaded onto anything and edited without trouble. The people editing these docs have a very low level of competancy so it needs to be idiot proof :)

[fumei]

1. "Can the variable be stored in normal.dot? "

It is a document variable.

2. "Because if I am following you right every time I use this code on a different report it is going to add another button as the variable is stored in a particular document. "

Huh? I do not follow that at all. What do you mean use this code on a different report?????

You are using a template - which makes a new document. One template, I assume.


BTW: one should avoid, as much as possible, storing ANYTHING in normal.dot.

[FhM]

1. "Can the variable be stored in normal.dot? "

It is a document variable.

2. "Because if I am following you right every time I use this code on a different report it is going to add another button as the variable is stored in a particular document. "

Huh? I do not follow that at all. What do you mean use this code on a different report?????

You are using a template - which makes a new document. One template, I assume.


BTW: one should avoid, as much as possible, storing ANYTHING in normal.dot.

Imagine this scenario. A report is written for each student in all 16 subjects.
There is 16 word documents per pupil.
Each document is produced by a 3 rd party program.
I take those documents and copy the contents into a new file using the template we have been working on. I use this template for all the documents.
Now I open subject A and it makes a button and stores the variable in that document so if I open it again the subject A document it will not make another button.
Now if I open the next report for subject B there will not be a document variable as it was set in the subject A report, therefore it will create a new button and there will be 2 buttons..... and so on and so on.

Is this correct?