ronaldj
asked on
Prevent Word macro from running on open
Background: I have a program which takes user responses from an email, creates a main client folder and appropriate sub-folders...then opens the appropriate Word template and populates/saves to appropriate sub-folder...way cool, so far so good...
Dilema: Client askled last PM if I can create a utility off the UserForm to view the 4 classes of .doc in question...created a UserForm with 4 list boxes...they get populated correctly and when I click "Retrieve" button, lo and behold, there's the document...KINDA SORTA...what's happening is the document fields are getting updated with the current respondents answers rather than just giving me the .doc on file...obviously it's triggering the onOpen macro in Word...if I go to Windows Explorer and click the .doc in question, it opens with the "enable macros?" dialogue box...answer no and the .doc appears as I wanted...
Can I either vba "turn off" the macro on open or at the very lest get the dialogue box to open so the user can say No? I found a reference to the following line of code:
'WordBasic.DisableAutoMacr
...when I run this I get "the remote server machine does not exist or is unavailable"...too bad, sounded like what I needed!
Code to open the Word doc is as follows:
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Applica
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Open("" & ClientSelect & "\" & fullname & "IA.doc")
wdApp.Visible = True
Thanks, Ron
Dilema: Client askled last PM if I can create a utility off the UserForm to view the 4 classes of .doc in question...created a UserForm with 4 list boxes...they get populated correctly and when I click "Retrieve" button, lo and behold, there's the document...KINDA SORTA...what's happening is the document fields are getting updated with the current respondents answers rather than just giving me the .doc on file...obviously it's triggering the onOpen macro in Word...if I go to Windows Explorer and click the .doc in question, it opens with the "enable macros?" dialogue box...answer no and the .doc appears as I wanted...
Can I either vba "turn off" the macro on open or at the very lest get the dialogue box to open so the user can say No? I found a reference to the following line of code:
'WordBasic.DisableAutoMacr
...when I run this I get "the remote server machine does not exist or is unavailable"...too bad, sounded like what I needed!
Code to open the Word doc is as follows:
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Applica
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Open("" & ClientSelect & "\" & fullname & "IA.doc")
wdApp.Visible = True
Thanks, Ron
ASKER
Hey dragontooth,
You may have solved my enigma Iwent into one of the more recent templates (more efficient code than the ones created 6 months ago!) and "shut off" the onOpen trigger and cut n'pasted into trigger onNew...gloryoskee, it seems to have worked...went into one of the dinosaur templates, did the same (bit more complicated) and it too seems to do the trick.
I have to take my father-in-law for dialysis in about 15 minutes. When I return I'd like to make sure that the template creating a new document works correctly (i.e., fields update). Will then try and retrieve and see if it comes up correctly (i.e., not updated). Don't have any doubts, but...
Looks like we have a winner. Will award points after my "exercise." Stay tuned.
Thanks,
Ron
You may have solved my enigma Iwent into one of the more recent templates (more efficient code than the ones created 6 months ago!) and "shut off" the onOpen trigger and cut n'pasted into trigger onNew...gloryoskee, it seems to have worked...went into one of the dinosaur templates, did the same (bit more complicated) and it too seems to do the trick.
I have to take my father-in-law for dialysis in about 15 minutes. When I return I'd like to make sure that the template creating a new document works correctly (i.e., fields update). Will then try and retrieve and see if it comes up correctly (i.e., not updated). Don't have any doubts, but...
Looks like we have a winner. Will award points after my "exercise." Stay tuned.
Thanks,
Ron
ASKER
Hey dragontooth,
Need a bit more help. here's what I did...
with the word template open I have, under project explorer the following:
Template Project (IntermediaryDD)
Need a bit more help. here's what I did...
with the word template open I have, under project explorer the following:
Template Project (IntermediaryDD)
What is it?
At first glance it looks like a project named IntermediaryDD. Can you open it?
dragontooth
At first glance it looks like a project named IntermediaryDD. Can you open it?
dragontooth
ASKER
Sorry about the erroneous "Enter"...
with the word template open I have, under Project Explorer, the following:
Template Project (IntermediaryDD)
MicrosoftWordObjects
This Document
Private Sub Document_Open
call UpdateAll
end sub
so when the excel program "grabs" the template it correctly populates the form and is saved with the correct name in the correct folder...so far so good...
I thought that if I did the following the newly created document would NOT trigger the macro when called up:
Normal
MicrosoftWordObjects
This Document
Private Sub Document_New
call UpdateAll
end sub
Well, bad guess on my part. How the heck do I structure this so that the new doc created from the template does not trigger the UpdateAll macro when visited?
Thanks and confused,
Ron
with the word template open I have, under Project Explorer, the following:
Template Project (IntermediaryDD)
MicrosoftWordObjects
This Document
Private Sub Document_Open
call UpdateAll
end sub
so when the excel program "grabs" the template it correctly populates the form and is saved with the correct name in the correct folder...so far so good...
I thought that if I did the following the newly created document would NOT trigger the macro when called up:
Normal
MicrosoftWordObjects
This Document
Private Sub Document_New
call UpdateAll
end sub
Well, bad guess on my part. How the heck do I structure this so that the new doc created from the template does not trigger the UpdateAll macro when visited?
Thanks and confused,
Ron
Sorry I took so long to respond I was in a meeting :(
I would put the call to the macro in the template itself under Document_New, not in Normal. I personally do not like anything in Normal, that is just me though, the reason is most of the time it causes problems later that is hard to track down.
Umm are these macro's going to be used after the doc is created? If not you could just delete all macros in the document close event, so when the doc is opened later the macros are gone and no problem.
Still here :)
dragontooth
I would put the call to the macro in the template itself under Document_New, not in Normal. I personally do not like anything in Normal, that is just me though, the reason is most of the time it causes problems later that is hard to track down.
Umm are these macro's going to be used after the doc is created? If not you could just delete all macros in the document close event, so when the doc is opened later the macros are gone and no problem.
Still here :)
dragontooth
ASKER
Hmmm...intersting thought re: delete all macros. The way the Word portion of the program is called is as follows:
If UserForm1.ListBox2.Selecte
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open( _
"" & drive & ":\BCGprograms\" & filename & ".dot", , False, False)
wdApp.Visible = True
wdApp.ActiveDocument.SaveA
End If
So it first opens the template (.dot), fields are updated and the template is then saved as .doc. I NEVER want the .doc to update again, just keep what's there...so where do I put the close event? I'm guessing NOT the template itself, so that leaves either Normal or Document1...this is where I'm confused.
ron
If UserForm1.ListBox2.Selecte
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open( _
"" & drive & ":\BCGprograms\" & filename & ".dot", , False, False)
wdApp.Visible = True
wdApp.ActiveDocument.SaveA
End If
So it first opens the template (.dot), fields are updated and the template is then saved as .doc. I NEVER want the .doc to update again, just keep what's there...so where do I put the close event? I'm guessing NOT the template itself, so that leaves either Normal or Document1...this is where I'm confused.
ron
Yes in the template. The template is just that, a template, it starts you out. So when the doc is saved (or you cold put the delete stuff in the save) it (template) becomes a document carring all properties and methods with it. So in the doc new put the call to the macro. In the doc save put this code and we will not have to worry about it.
BTW I butchered a KB article from mvidas, it was originally to delete specific macros from an excel workbook.
Public Sub delCode()
Dim WB As Document
Dim VBC, i As Integer, VBCM, j As Integer
If WB Is Nothing Then Set WB = ThisDocument
For Each VBC In WB.VBProject.VBComponents
Set VBCM = VBC.CodeModule
If VBCM.CountOfLines > 0 Then
i = 1
j = VBCM.CountOfLines
VBCM.DeleteLines i, j
End If
Next VBC
End Sub
dragontooth
BTW I butchered a KB article from mvidas, it was originally to delete specific macros from an excel workbook.
Public Sub delCode()
Dim WB As Document
Dim VBC, i As Integer, VBCM, j As Integer
If WB Is Nothing Then Set WB = ThisDocument
For Each VBC In WB.VBProject.VBComponents
Set VBCM = VBC.CodeModule
If VBCM.CountOfLines > 0 Then
i = 1
j = VBCM.CountOfLines
VBCM.DeleteLines i, j
End If
Next VBC
End Sub
dragontooth
UserForm1 that is in Word I don't know why but I though you were in VB and calling Word. If that is the case the macro will kill all macros in ThisDocument, it may cause you more trouble so please back up before testing or you will lose it all.
dragontooth
dragontooth
ASKER
UserForm1 is in XL and the code "If UserForm1.ListBox2..." is triggered when a button on another UserForm (also XL) is clicked...
ASKER
hey dragontooth,
frustrating to say the least...can't be this hard! I tried putting your code in the TemplateProject, ThisDocument, on_Close
Dim WB As Document
Dim VBC, i As Integer, VBCM, j As Integer
If WB Is Nothing Then Set WB = ThisDocument
For Each VBC In WB.VBProject.VBComponents
Set VBCM = VBC.CodeModule
If VBCM.CountOfLines > 0 Then
i = 1
j = VBCM.CountOfLines
VBCM.DeleteLines i, j
End If
Next VBC
it blows up with the message "programmatic access to VB project is not trusted" and highlights the line "For Each VBC..."
Thoughts?
Ron
frustrating to say the least...can't be this hard! I tried putting your code in the TemplateProject, ThisDocument, on_Close
Dim WB As Document
Dim VBC, i As Integer, VBCM, j As Integer
If WB Is Nothing Then Set WB = ThisDocument
For Each VBC In WB.VBProject.VBComponents
Set VBCM = VBC.CodeModule
If VBCM.CountOfLines > 0 Then
i = 1
j = VBCM.CountOfLines
VBCM.DeleteLines i, j
End If
Next VBC
it blows up with the message "programmatic access to VB project is not trusted" and highlights the line "For Each VBC..."
Thoughts?
Ron
Well since that one is getting upset let's do it a different way. In the template that has the macro you want to run, add a check, when the template is opened as a new Document the name by default is "Document" + a number so in the Document_New event add:
Private Sub Document_New()
If InStr(1, ActiveDocument.Name, "Document") = 1 Then
UpdateAll
End If
End Sub
What this will do is when the template is opened the sub will fire and we check to see if the name is default "Document" is there, if so run the macro otherwise do not.
This will work yet :)
dragontooth
Private Sub Document_New()
If InStr(1, ActiveDocument.Name, "Document") = 1 Then
UpdateAll
End If
End Sub
What this will do is when the template is opened the sub will fire and we check to see if the name is default "Document" is there, if so run the macro otherwise do not.
This will work yet :)
dragontooth
ASKER
Hey dragontooth,
Need to run a quick errand BUT I may have "stumbled" across a solution...to fully test I will DK all the docs created by the template, re-create same, then try and access through the control panel...believe the Word macro (UpdateAll) does NOT trigger and life may be good...should be back in ~ 1 hour...stay tuned...
fingers crossed,
Ron
PS thanks again for all your help.
Need to run a quick errand BUT I may have "stumbled" across a solution...to fully test I will DK all the docs created by the template, re-create same, then try and access through the control panel...believe the Word macro (UpdateAll) does NOT trigger and life may be good...should be back in ~ 1 hour...stay tuned...
fingers crossed,
Ron
PS thanks again for all your help.
I'll be here.
ASKER
dragontooth...
here's what I did...
1) Dk'd all docs created by the template
2) used the XL control panel (UserForm1) to create the docs deleted above...
all docs were created correctly AND saved in the appropriate folders...so far so
good...
3) used the XL ControlPanel (UserForm 3) to retrieve the doc on file...
doc that appears is the doc "desired" i.e., it DOES NOT update with current fields in
XL speadsheet...
Not to be too optimistic but looks like all is OK...
If you review my first post, I tried incorporating the following line in my XL code:
Word.Application.WordBasic
After much "surfing" last PM and several Budwiesers, decided this AM to try the following:
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Applica
End If
On Error GoTo 0
wdApp.WordBasic.DisableAut
Set wdDoc = wdApp.Documents.Open("" & ClientSelect & "\" & fullname & "DI.doc")
wdApp.Visible = True
wdApp.WordBasic.DisableAut
This little beast "seems" to have done the trick...obviously me error was the original way I tried to invoke the "DisableAutoMacros" command...
Will "press the envelope" with a few other tests but believe life is once again good!...please give me some type of response so I can award points...thanks for all your help and "making me think"...
Ron
here's what I did...
1) Dk'd all docs created by the template
2) used the XL control panel (UserForm1) to create the docs deleted above...
all docs were created correctly AND saved in the appropriate folders...so far so
good...
3) used the XL ControlPanel (UserForm 3) to retrieve the doc on file...
doc that appears is the doc "desired" i.e., it DOES NOT update with current fields in
XL speadsheet...
Not to be too optimistic but looks like all is OK...
If you review my first post, I tried incorporating the following line in my XL code:
Word.Application.WordBasic
After much "surfing" last PM and several Budwiesers, decided this AM to try the following:
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Applica
End If
On Error GoTo 0
wdApp.WordBasic.DisableAut
Set wdDoc = wdApp.Documents.Open("" & ClientSelect & "\" & fullname & "DI.doc")
wdApp.Visible = True
wdApp.WordBasic.DisableAut
This little beast "seems" to have done the trick...obviously me error was the original way I tried to invoke the "DisableAutoMacros" command...
Will "press the envelope" with a few other tests but believe life is once again good!...please give me some type of response so I can award points...thanks for all your help and "making me think"...
Ron
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
LOL
Thanks!
These docs aren't going to be opened without the use of your program are they? If they are the automacros will run and of course complain.
Also I have found that adding DoEvents will help reduce the 429's.
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
DoEvents
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Applica
DoEvents
End If
On Error GoTo 0
Once again Thanks
Tommy
Thanks!
These docs aren't going to be opened without the use of your program are they? If they are the automacros will run and of course complain.
Also I have found that adding DoEvents will help reduce the 429's.
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
DoEvents
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Applica
DoEvents
End If
On Error GoTo 0
Once again Thanks
Tommy
ASKER
Hey Tommy,
Well, the whole reason I was "instructed" to write this utility is to make it easy for the girls running the program to access the files. Without casting dispersions on peoples abilities, right now there are ~150 main client folders with 2-12 sub-foilders, so there would be a bunch to surf through. I would know where to go but I'm not in the office, so...ergo the utility. If you were to go to WindowsExplorer and 2x click the file in question, and answer "Disable Macros," the file stays in tact and this would be an acceptable (though cumbersome) alternative. I will, however, keep the DoEvents idea in mind.
Thanks again,
Ron
Well, the whole reason I was "instructed" to write this utility is to make it easy for the girls running the program to access the files. Without casting dispersions on peoples abilities, right now there are ~150 main client folders with 2-12 sub-foilders, so there would be a bunch to surf through. I would know where to go but I'm not in the office, so...ergo the utility. If you were to go to WindowsExplorer and 2x click the file in question, and answer "Disable Macros," the file stays in tact and this would be an acceptable (though cumbersome) alternative. I will, however, keep the DoEvents idea in mind.
Thanks again,
Ron
Just wanted to make sure it didn't bite you later.
I used the DoEvents because I had some trouble. If it was VB5/VB6 or Word95/97/2000 I really don't know, could be also the fact that the doc is a meg and on slower computers it takes a while, but it has made my life a lot easier and we can all use some of that :)
Tommy
I used the DoEvents because I had some trouble. If it was VB5/VB6 or Word95/97/2000 I really don't know, could be also the fact that the doc is a meg and on slower computers it takes a while, but it has made my life a lot easier and we can all use some of that :)
Tommy
If I understand you correctly, you want to run the macro on the new file creation, not on open?
I would move the macro from the
Private Sub Document_Open()
to
Private Sub Document_New()
HTH
dragontooth