Printing a Word doc from Excel

  • Thread starter Thread starter Danny
  • Start date Start date
D

Danny

I wish to create a spreadsheet which will have a series of
buttons on it linking to a collection of Word documents.
What would the code be to print a Word document from an
Excel macro. Ideally i would like the Print dialogue
window to appear so that i can specify the number of
copies before printing.

Any help would be greatly appreciated.
 
Danny,

I don't know how to put up the word dialog to set the number of
copies, but the code below will do the same thing: set your file path
and name where obvious. The code requires that you set a reference to
Word in your project - set your reference to the oldest version of
Word that your users will be using, since references only
automatically update from older to newer versions but not newer to
older.

HTH,
Bernie

Sub PrintWordDocument()
Dim oWord As Word.Application

Set oWord = CreateObject("word.application")

oWord.Documents.Open "C:\folder\Word file name.doc"
oWord.PrintOut Copies:=Application.InputBox _
("Number of Copies?", , , , , , , 1)

oWord.Application.Quit (False)
End Sub
 
Try this

Set WD = CreateObject("Word.Application")
WD.Documents.Open ("C:\ron.doc")
WD.ActiveDocument.PrintOut
WD.Quit
Set WD = Nothing
 
Tried the method below and could not get it to work. I
have very limited VBA knowledge and i could not work out
where i tell it that i want it to work on Word 97 and
above? Any help would be appreciated.

Danny.
 
Thanks for this. The code worked and printed out the
document. Is there not a way i can get the print dialogue
box up before printing this way the user can enter how
many copies they want? Alternatively a simple window
asking for a user input for the number of copies would be
even better.

How do i put a delay on closing Word cause i currently get
the quitting word will cancel all print jobs message?

Thanks a lot,


Danny.

-----Original Message-----
Dim WD As Object

I forgot this line

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Danny" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Hi Danny

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Word? Object Library
? is the Excel version number
 
Use the example from Bernie for the copies
How do i put a delay on closing Word cause i currently get
the quitting word will cancel all print jobs message?
Click to expand...

Check out DisplayAlerts in the VBA help for th
 
The code runs now and the printer comes up in the taskbar
as if it is printing, but then nothing gets through to the
printer?

Here is my code -


Sub Rectangle2_Click()
Dim oWord As Word.Application

Set oWord = CreateObject("word.application")

oWord.Documents.Open "C:\Test.doc"
oWord.PrintOut Copies:=Application.InputBox _
("Number of Copies?", , , , , , , 1)

oWord.Application.Quit (False)

End Sub

I can follow most of the code but don't understand how the
input box is telling the printer how many copies it wants.
This code seems too simple for this complex task? Could
someone explain how this is working and why my document
never actually gets printed?

Thanks Danny.

-----Original Message-----
Hi Danny

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Word? Object Library
? is the Excel version number

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Danny" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Hi

Is working for me

Try this

Sub test()
Dim WD As Object
Set WD = CreateObject("Word.Application")
WD.Documents.Open ("C:\ron.doc")
WD.ActiveDocument.PrintOut Copies:=Application.InputBox _
("Number of Copies?", , , , , , , 1)
WD.Quit
Set WD = Nothing
End Sub

Copies is a part of the PrintOut code
See the VBA help for PrintOut
 
Thanks for this it is now working when I try your code.

One problem is that the Quit statement seems to happen too
quickly and i get the message about quitting Word will
cancel all pending jobs - is there a delay statement for
10 seconds which i can put in?

Also is there a way of getting the file loction to be
looked up from a Cell - i.e in cell A1 I type in
C:\Test.doc and then simply reference cell A1 in the code.
This way it will be easier for the user to update the path
of the document without having to dive into lots of code.

Thanks for all your help,

Danny.

-----Original Message-----
Hi

Is working for me

Try this

Sub test()
Dim WD As Object
Set WD = CreateObject("Word.Application")
WD.Documents.Open ("C:\ron.doc")
WD.ActiveDocument.PrintOut Copies:=Application.InputBox _
("Number of Copies?", , , , , , , 1)
WD.Quit
Set WD = Nothing
End Sub

Copies is a part of the PrintOut code
See the VBA help for PrintOut

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Danny" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Try this

Sub test()
Dim WD As Object
Dim Fname As String
Fname = Range("A1").Value
Set WD = CreateObject("Word.Application")
WD.Documents.Open Fname
WD.ActiveDocument.PrintOut Copies:=Application.InputBox _
("Number of Copies?", , , , , , , 1)
DoEvents
Application.Wait (Now + TimeValue("0:00:10"))
WD.Quit
Set WD = Nothing
End Sub
 
Danny,

I took a Word document with some boilerplate text and inserted a bookmark
that I named "Bookmark1" I saved it as a Word template rather than a Word
document. The macro below will open Word if it isn't already open, load the
template, insert whatever I have in cell A1 on sheet 1 immediately after the
bookmark, bring up the print dialog, execute the print after I set the
number of copies and it will close word without warnings or saving the new
document. See if you can modify it to fit your needs.

Sub PrintFromTemplate()
Dim bStarted As Boolean
Dim oApp As Word.Application
Dim str1 As String

str1 = Sheets("Sheet1").Cells(1, 1).Value

On Error Resume Next
Set oApp = GetObject(, "Word.Application")
'Get the running instance of Word, if there is no instance
'create a new one:
If Err <> 0 Then
bStarted = True
Set oApp = CreateObject("Word.Application")
End If

oApp.Activate
oApp.Visible = True
oApp.Documents.Add Template:="Test.dot"

oApp.ActiveDocument.Bookmarks("Bookmark1").Range.InsertAfter str1

oApp.Dialogs(wdDialogFilePrint).Show
oApp.ActiveDocument.PrintOut Background:=False, Copies:=0

oApp.ActiveDocument.Close wdDoNotSaveChanges

'Quit only when Word was not running when we started this code
If bStarted Then
oApp.Quit
End If


Set oApp = Nothing
End Sub


Steve
 
Thanks again for this.

This has cured the problem of Word closing too soon, but I
now have a problem in that in the background I get a
message box asking me if i want to save the word document.
I have not changed it as i have just printed it. Is it
possible to include in the code a 'Yes' to save the
document if the prompt appears?

I know this sounds a bit trivial but the idea of this
application is that my users can print all their documents
from an easy to use Excel menu system rather than having
to go via Windows Explorer to find all their Word docs. I
don't want them to come out of the Excel menu to have to
click yes to save boxes.

Thanks for your help.
 
Danny,

Right above the WD.Quit line, insert a line

WD.ActiveDocument.Close SaveChanges:=wdSaveChanges

This assumes you opened an existing document, as in Ron's example.
Otherwise, your user will get the SaveAs message.

The technique Ron shows where you insert a pause to allow the print job to
be sent will work but might waste a bit of time and if you shorten the time
span you might cause errors. An alternate approach is to set the Background
parameter of the PrintOut method to false (turns off background printing for
the active document). If background printing is turned off, the next line
of code doesn't execute until the print job has been sent to the printer.
Word may shut down before printing is complete but you will be sure the
entire print job was sent and will be printed.

Steve
 
Ron,

I think only Word has the parameter in its PrintOut method. It is kind of
handy.

Steve
 
This background print parameter sounds like a better
solution to the time delay as this will sometimes fall
over depending on the size of the document i am printing.
What is the code for turning off the background print
parameter. Here is what i have so far -

Dim WD As Object
Dim Fname As String
Fname = Range("Hyperlinks!B4").Value
Set WD = CreateObject("Word.Application")
WD.Documents.Open Fname
WD.ActiveDocument.PrintOut
Copies:=Application.InputBox _
("Number of Copies?", , , , , , , 1)
DoEvents
Application.Wait (Now + TimeValue("0:00:10"))
WD.ActiveDocument.Close SaveChanges:=wdSaveChanges
WD.Quit
Set WD = Nothing

Thanks,

Danny
 
Back
Top