Geoffro
asked on
WORD-OLE TROUBLE
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.ActivateMicros oftApp 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
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.ActivateMicros
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
The .FilePrint , , , , , , , "2" should be .FilePrint , , , , , , , 2Hope this helps, Chris
ASKER
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.
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
ASKER
I tried .FilePrint NumCopies = "2" and .FilePrint Copies = "2" to no avail - Word still only printed one copy???????
ASKER
Adjusted points to 170
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.