Put Excel Parameter into Word Macro

[PBAPaul]

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.

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"

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, [b]Copies:=1[/b], 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

 

[combo]

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


combo

 

[SkipVought]

Hi,

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

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:

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
[blue]
    oWord.Run("PrintCarryDoc", Cells(1, 1).Value)[/blue]
    oWord.ActiveDocument.Save
    oWord.Quit

    Set oWord = Nothing
End Sub

Sub PrintCarryDoc([blue]ByRef intNoOfCopies As Integer[/blue])
     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:=[blue]intNoOfCopies[/blue], 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.