INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Put Excel Parameter into Word Macro

Put Excel Parameter into Word Macro

(OP)
Hi All
I am sorry that this might be a simple question but unfortunately I am in hospital away from my usual environment of reference books, files and seemingly at the moment – Brains!

I have a complex Excel workbook (Medications Control.xlsm) that controls a number of internal and external Excel worksheets and workbooks. It also calls up Word documents that are controlled by Word Macros and these are generally printing instructions.

An example of this is that I have a macro "PrintWordDoc" in Excel which opens an existing Word document and then gives it instructions to operate a macro which in turn controls the printing.

CODE --> VBA

Sub PrintWordDoc()
    Dim oWord As Word.Application
    Dim oDoc As Word.Document

    Set oWord = CreateObject("Word.Application")
    Set oDoc = oWord.Documents.Open("D:\Paul's Files\Carry List.docm")
    oWord.Visible = True

    oWord.Run MacroName:="PrintCarryDoc"
    oWord.ActiveDocument.Save
    oWord.Quit

    Set oWord = Nothing
End Sub 

A simple Excel Macro that selects a Word File and then operates the Word Macro "PrintCarryDoc"

CODE --> VBA

Sub PrintCarryDoc()
     With ActiveDocument.PageSetup
        .LineNumbering.Active = False

'General Printing Control

        .BookFoldPrinting = False
        .BookFoldRevPrinting = False
        .BookFoldPrintingSheets = 1
        .GutterPos = wdGutterPosLeft
    End With
    Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentWithMarkup, Copies:=1, Pages:="", PageType:= _
        wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
        PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
        WriteData_PagesPrinted
End Sub 
What I wish to do is get a variable for the number of copies needed, from Excel, and put that figure into the Word macro changing Copies:= 1 to that required. Possibly in the form PrintWordDoc 4 (from Excel) and PrintCarryDoc (X as integer) to be received by Word thereby generating 4 copies.

I am sorry but, at the moment, my brain will not allow me to work it out

RE: Put Excel Parameter into Word Macro

The Run method in word has optional arguments for passing procedure arguments. they are variant.
So in word you need Sub PrintCarryDoc(X as Variant) and Copies:=X (it's variant too).
Call in excel: oWord.Run MacroName:="PrintCarryDoc", varg1:=4

combo

RE: Put Excel Parameter into Word Macro

Hi,

CODE

Sub PrintCarryDoc(Optional Copeez=1 As Variant)
'...
   , Copies=Copeez, 
'...
End Sub

Sub PtintWordDoc(Optional Cpz=1 As Variant)
'...
   oWord.Run.MacroName:= "PrintCarryDoc" Cpz
'...
End Sub

Sub YourExcelMain()
'...
   oWord.Run.MacroName:= "PrintWordDoc" [SomeCellWithNumberOfCopies]
'...
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Put Excel Parameter into Word Macro

If you have in cell A1 in your Excel the number of copies you want to print from Word, based on this example your code would look like:

CODE

Sub PrintWordDoc()
    Dim oWord As Word.Application
    Dim oDoc As Word.Document

    Set oWord = CreateObject("Word.Application")
    Set oDoc = oWord.Documents.Open("D:\Paul's Files\Carry List.docm")
    oWord.Visible = True

    oWord.Run("PrintCarryDoc", Cells(1, 1).Value)
    oWord.ActiveDocument.Save
    oWord.Quit

    Set oWord = Nothing
End Sub 

CODE

Sub PrintCarryDoc(ByRef intNoOfCopies As Integer)
     With ActiveDocument.PageSetup
        .LineNumbering.Active = False

'General Printing Control

        .BookFoldPrinting = False
        .BookFoldRevPrinting = False
        .BookFoldPrintingSheets = 1
        .GutterPos = wdGutterPosLeft
    End With
    Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentWithMarkup, Copies:=intNoOfCopies, Pages:="", PageType:= _
        wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
        PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
        WriteData_PagesPrinted
End Sub 

Code not tested.

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close