• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:


I have written some Visual Basic code in a module sheet of an Excel 7.0 workbook.  Basically the code selects some data from the workbook then activates Word 7.0 before opening a document and inserting the data from the Excel workbook into it and then printing two (2) copies of the Word document - the problem is however only one copy of the document ever prints and I cannot work out why.  Below is the code I have used:

Sub PrintInvoice()
    Set Word7 = CreateObject("Word.Basic")
    If Selection.Rows.Count <> 1 Or Selection.Columns.Count <> 256 Then
      MsgBox "You have not made a valid selection!  Please select the row " _
            + "which contains the data for the invoice you want to print." _
            , 48, "Invalid Selection"
      Exit Sub
    End If
    RowRef = Selection.Row
    Variable1$ = Cells(RowRef, 1)
    Variable2$ = Cells(RowRef, 2)
    Application.ActivateMicrosoftApp xlMicrosoftWord
    With Word7
        .ScreenUpdating 0
        .AppMaximize 1
        .FileOpen "C:\INVOICE.DOC"
        .EditGoTo "BookMark1"
        .Insert Variable1$
        .EditGoTo "BookMark2"
        .Insert Variable2$
        .ScreenUpdating 1
        Answer = .MsgBox("Are you sure you want to print the invoice?", _
                    "Print Invoice", 36)
        If Answer = -1 Then .FilePrint , , , , , , , "2"

        .FileClose 2
    End With
    AppActivate "Microsoft Excel"
    Set Word7 = Nothing
End Sub
  • 3
  • 2
1 Solution
The .FilePrint , , , , , , , "2" should be .FilePrint , , , , , , , 2Hope this helps, Chris
GeoffroAuthor Commented:
When I use .FilePrint , , , , , , , 2 I get a Type mismatch error.  I know the WordBasic Help says that the NumCopies argument of the FilePrint statement is a number however I got a file called Position.hlp from Microsoft which says that when using the FilePrint statement through OLE the NumCopies argument is text.

In any case I still only get one page printed instead of two, and I would prefer not to use two (2) FilePrint statements.

Well, you just disqualified my next solution :)How about trying a named argument rather than all those commas?.filePrint copies:="2"should work.  (Can't test it on this machine, it's running office 97 and Word is now VBA.  Go figure)Chris
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

GeoffroAuthor Commented:
I tried .FilePrint NumCopies = "2" and .FilePrint Copies = "2" to no avail - Word still only printed one copy???????

GeoffroAuthor Commented:
Adjusted points to 170
I've recently written a contract generation system which uses VB 4.0 and word 7.0.  I used to use the following syntax to ensure I had no Type Mismatch Errors when I wanted to use multiple values.
note : Setup objword as a word basic object

With objword
  Call objword.FilePageSetup(SectionStart:=4, OddAndEvenPages:=1)
end with

Note the CALL the BRACKETS and the := ,

Good luck - Che

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now