Solved

WORD-OLE TROUBLE

Posted on 1997-04-28
6
313 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
0
Comment
Question by:Geoffro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:ChrisLewis
ID: 1425790
The .FilePrint , , , , , , , "2" should be .FilePrint , , , , , , , 2Hope this helps, Chris
0
 

Author Comment

by:Geoffro
ID: 1425791
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.

0
 
LVL 3

Expert Comment

by:ChrisLewis
ID: 1425792
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

0
 

Author Comment

by:Geoffro
ID: 1425794
Adjusted points to 170
0
 

Accepted Solution

by:
fire earned 170 total points
ID: 1425795
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question