[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Prevent Word macro from running on open

Posted on 2005-05-11
19
Medium Priority
?
943 Views
Last Modified: 2012-05-05
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.DisableAutoMacros 1   'Disables auto macros

...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.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open("" & ClientSelect & "\" & fullname & "IA.doc")
wdApp.Visible = True

Thanks, Ron
0
Comment
Question by:ronaldj
  • 10
  • 9
19 Comments
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13977654
Hi ronaldj,

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


0
 

Author Comment

by:ronaldj
ID: 13978073
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
0
 

Author Comment

by:ronaldj
ID: 13981555
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)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13981576
What is it?
At first glance it looks like a project named IntermediaryDD. Can you open it?

dragontooth

0
 

Author Comment

by:ronaldj
ID: 13981603
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
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13982110
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

0
 

Author Comment

by:ronaldj
ID: 13982215
Hmmm...intersting thought re: delete all macros. The way the Word portion of the program is called is as follows:

If UserForm1.ListBox2.Selected(4) = True Then
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open( _
            "" & drive & ":\BCGprograms\" & filename & ".dot", , False, False)
wdApp.Visible = True
wdApp.ActiveDocument.SaveAs ("" & drive & ":\ClientDocs\" & fullname & "\" & fullname & "IntermediaryDD\" & fullname & "DI.doc")
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
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13982301
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

0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13982330
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

0
 

Author Comment

by:ronaldj
ID: 13982393
UserForm1 is in XL and the code "If UserForm1.ListBox2..." is triggered when a button on another UserForm (also XL) is clicked...
0
 

Author Comment

by:ronaldj
ID: 13985509
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
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13986055
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

0
 

Author Comment

by:ronaldj
ID: 13986516
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.
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13987641
I'll be here.
0
 

Author Comment

by:ronaldj
ID: 13988151
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.DisableAutoMacros 1

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.Application")
End If
On Error GoTo 0
wdApp.WordBasic.DisableAutoMacros (1)'Disables auto macros
Set wdDoc = wdApp.Documents.Open("" & ClientSelect & "\" & fullname & "DI.doc")
wdApp.Visible = True
wdApp.WordBasic.DisableAutoMacros (0)

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


0
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 2000 total points
ID: 13988295
LOL didn't mean to make your head hurt with all that thinking. :) I am glad you figured it out, at the very least we found a few more ways to skin the cat. :)

dragontooth

0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13989013
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.Application")
DoEvents
End If
On Error GoTo 0

Once again Thanks
Tommy

0
 

Author Comment

by:ronaldj
ID: 13989452
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
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13989562
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Introduction to Processes

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question