1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Run Multiple Macros

Discussion in 'Business Applications' started by OfficeLady, Jan 17, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. OfficeLady

    OfficeLady Thread Starter

    Joined:
    Jan 17, 2011
    Messages:
    2
    I have 3 Word documents that pull information from a single Excel spreadsheet via Mail Merge. Each of the Word documents has its own macro to then print the merged letters. Each letter prints differently, eg. one is duplex, another is stapled. I would like to create a macro that would run all 3 macros with one click of a button, and place that one button in either the Excel spreadsheet if possible, or a Word document. The goal is to have a single button to click that would merge and print all letters.
    I have very limited VB knowledge, but am apt at recording macros.
     
  2. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    You can easily call (run) one macro from within another macro. The basic syntax is like this:

    Code:
    Sub Main_Macro()
         
         Call Sub_Macro_1 ' Runs first sub macro
         Call Sub_Macro_2 ' Runs second sub macro
         Call Sub_Macro_3 ' Runs third sub macro
    
    End Sub
    
    It can get tricky when jumping from document to document. I generally like to run all the macros from a master document and just specify within each macro which document(s) I want it to run on, but it is also possible to call a macro in one document from a macro in another document.

    I'll be happy to give more info, if you need it.
     
  3. OfficeLady

    OfficeLady Thread Starter

    Joined:
    Jan 17, 2011
    Messages:
    2
    Thank you for your quick reply. I copied and pasted your code and tried it just as you have it and then again editing where it says "Macro_1" replacing that portion with the name of my first macro and so. For example: Call Sub_PrintApptLetter ' Runs first sub marco
    Each way caused the following error:
    Compile Error: Sub or Function not defined.

    I created this macro in Word.
    Any further help you could offer would be greatly appreciated.
     
  4. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    Yeah, that's that "tricky" part to which I referred :)

    If the macro being called and the macro doing the calling are not in the same module or document, you have to tell Word or Excel where to look for the macro.

    Example:

    Call Module2.Sub_PrintApptLetter

    I do way more programming in Excel than word, so I'll need to go refresh my memory on the exact syntax for accessing different documents in Word, but I'm guessing the module is the issue.

    I'll get back to you in a few.
     
  5. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    This article gives a very basic explanation of how to access macros in other documents (the Word section is toward the bottom): http://support.microsoft.com/kb/177760

    This doesn't begin to cover all the issues that can, and most likely will, come up. You have to account for variables like whether or not the target document is already open, etc. I can help you tailor something for your needs, but I'll probably need to see your code to give any really good, specific guidance.

    Also, I am curious about your approach to your scenario. I am wondering whether all three of these Word documents contain distinctly different information, or if they contain the same info, just formatted and mailed differently.

    Ideally, what I'd shoot for in this scenario, as I understand it, is one Word Doc that contains all of the macros. That master document could pull the data from Excel, format it however you want, print and/or save a copy of the document, then either reformat the data for the next mailing, or clear out the data and pull the next set of data from Excel. Wash, Rinse, Repeat.

    The above method can be a little confusing to set up, but, ultimately, you end up with a much easier to manage program. All your code stays in one file and you eliminate many variables, like whether or not one of the documents is already open.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975266

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice