Disable/remove macros during SaveAs?

[Ed]

Jezeble responded when I asked this a few days ago, and I realized I was
incorrect in my description of the situation. Most likely, I have also set
things up incorrectly, and there is a better way to do this.

I run a macro in Excel that calls a Word document. This document has an
Autpen macro that pastes in the information from Excel, does some things,
and then does a SaveAs. I realized that a new document based on this
"First" doc (I called it my "template") will still have the Autpen macro,
and if I don't disable it, it screws up my new document.

The question, then, is: Can I disable or remove the macro when doing a
SaveAs? Or is there a way to do this so the macro will not pass on to the
new document?

Thanks.
Ed

 

[Jezebel]

First, try to design your application as a whole so you have code only in
one place. Do you need code at the Word end at all? Why not do all the work
in your Excel macro? Trying to get code in one place to enable/disable code
somewhere else is not a good approach.

Second (as an alternative), follow the advice that someone else posted: use
a proper template (not a document); and since the code should run only when
the document is created and not every time it is opened, use an Auto_New
macro rather than Autpen.

 

[Doug Robbins - Word MVP]

I second Jezabel's advice.

See the article “Control Word from Excel” at:

http://www.mvps.org/word/FAQs/InterDev/ControlWordFromXL.htm

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Ed]

Thank you for your advice. I think the second alternative is what I need.
The Word code pastes in the Excel, then applies Word Table formats, splits
the big table into several smaller ones, and applies som text and table
formatting to each one. I'm not sure how I'd do all that from Excel -
that's why I did it in Word. The Autpen was a very convenient way to run
the macros automatically, rather than exit Excel and get into Word to
manually run them.

I've never used separate templates, so I need to explore this. But if I
understand you right, when I call for a new Word document from my Excel
code, I need to create it based on my custom template, and an Auto_New macro
in that template will run my code with the creation of a new document? I'll
study on this. Thank you again.

Ed

 

[Ed]

Thank you, Doug. I'm trying to wrap my mind around Jezebel's advice; I've
never used separate templates before. Time to learn, it seems.

The MVPS article was interesting. One thing I did not see, thoguh - or if I
did, I didn't know what I was seeing! - was how to call a Word macro (vice a
Word command) from within the Excel code. Can you help me with the syntax
for that?

Ed

 

[Jonathan West]

Calling a Word macro from Excel is done using the Run method of Word's
Application object.

 

[Jay Freedman]

Hi, Ed,

The idea is right but the syntax is slightly off -- remove the underscore
from AutoOpen or AutoNew. Better yet, go read
http://www.mvps.org/word/FAQs/MacrosVBA/PseudoAutoMacros.htm for an improved
version.

Responding to another of your questions in this thread, you don't need to
explicitly call these macros from Excel. Word automatically executes them
when the corresponding event (opening a document or creating a new document)
occurs. Your code in Excel would need only to start Word and open or create
a document in it.

 

[Ed]

Jay, once again I owe you big thanks.

Ed

Hi, Ed,

The idea is right but the syntax is slightly off -- remove the underscore
from AutoOpen or AutoNew. Better yet, go read
http://www.mvps.org/word/FAQs/MacrosVBA/PseudoAutoMacros.htm for an improved
version.

Responding to another of your questions in this thread, you don't need to
explicitly call these macros from Excel. Word automatically executes them
when the corresponding event (opening a document or creating a new document)
occurs. Your code in Excel would need only to start Word and open or create
a document in it.