printing from a word document with an excel document in vba

davis_greene
davis_greene used Ask the Experts™
on
I am trying, in vain, to set up an excel macro that will print a word document as part of a larger excel document.  The problem i am having is in the collation of the files.  I need it to print excel 'sheet1' then the word document and then excel 'sheet2' in that order.  It will only print sheet1/sheet2/word.  thanks in advance for the help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
What code do you have so far?

Author

Commented:
My code follows, where "poop!k4" references the number of copies to print. "poop.doc" is the word file. "report 1 of 2" and "report 2 of 2" are the two excel sheets.

My aim is to have it print "poop!k4" number of copies in the order of 1) report 1 of 2
             2) poop.doc
             3) report 2 of 2

currently it is printing 1) report 1 of 2
                         2) report 2 of 2
                         3) poop.doc


Sub printing()
'
' printing Macro
' Macro recorded 1/3/2003 by Davis
'
' Keyboard Shortcut: Ctrl+p
'
   
    Dim i As Integer, w As New Word.Application, o As New Word.Document
    i = Int(Range("poop!k4").Value)
    Set o = w.Documents.Open("c:\windows\desktop\stuff\poop.doc")
    For i = 1 To Range("poop!k4").Value
      Sheets("report 1 of 2").Select
      ActiveWindow.SelectedSheets.PrintOut Copies:=1
      o.PrintOut
      Sheets("report 2 of 2").Select
      ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Next i
    w.Documents.Close
    Set w = Nothing
    Set o = Nothing
   
   
End Sub

Author

Commented:
My code follows, where "poop!k4" references the number of copies to print. "poop.doc" is the word file. "report 1 of 2" and "report 2 of 2" are the two excel sheets.

My aim is to have it print "poop!k4" number of copies in the order of 1) report 1 of 2
             2) poop.doc
             3) report 2 of 2

currently it is printing 1) report 1 of 2
                         2) report 2 of 2
                         3) poop.doc


Sub printing()
'
' printing Macro
' Macro recorded 1/3/2003 by Davis
'
' Keyboard Shortcut: Ctrl+p
'
   
    Dim i As Integer, w As New Word.Application, o As New Word.Document
    i = Int(Range("poop!k4").Value)
    Set o = w.Documents.Open("c:\windows\desktop\stuff\poop.doc")
    For i = 1 To Range("poop!k4").Value
      Sheets("report 1 of 2").Select
      ActiveWindow.SelectedSheets.PrintOut Copies:=1
      o.PrintOut
      Sheets("report 2 of 2").Select
      ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Next i
    w.Documents.Close
    Set w = Nothing
    Set o = Nothing
   
   
End Sub
Commented:
Try adding a pause inbetween the word print and the 2nd excel sheet print..

     o.PrintOut
     
'Add this 1 sec pause
     Dim lTimer As Long
     lTimer = Timer()
     Do
      DoEvents
     Loop Until Timer() - lTimer > 1
   

     Sheets("report 2 of 2").Select

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial