We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

WORD-OLE TROUBLE

Medium Priority
348 Views
Last Modified: 2013-11-25
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")
   
    Sheets("Sheet1").Select
   
    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"
        .ViewZoomWholePage
        .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"

        .ViewNormal
        .FileClose 2
        .AppClose
    End With
   
    AppActivate "Microsoft Excel"
    Set Word7 = Nothing
End Sub
Comment
Watch Question

The .FilePrint , , , , , , , "2" should be .FilePrint , , , , , , , 2Hope this helps, Chris

Author

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

Author

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

Author

Commented:
Adjusted points to 170
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.