Print Word doc and Excel Worksheet during same printing sequ

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Print Word doc and Excel Worksheet during same printing sequ

Post by ABabeNChrist »

Is it possible to print a Word document and an Excel Worksheet during same print sequence using Check Boxes from an Excel UserForm.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Print Word doc and Excel Worksheet during same printing sequ

Post by HansV »

You can print a Word document from Excel, but it will always be in a separate print job.

See your thread Excel userform to print document in the Windows Secrets Lounge for the code needed to print a Word document from Excel.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Print Word doc and Excel Worksheet during same printing sequ

Post by ABabeNChrist »

Thank you HansV
I wasnt sure if it was possible to print both doc and sheet during same sequence.

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Print Word doc and Excel Worksheet during same printing sequ

Post by ABabeNChrist »

I was able to put this together, it seems to work ok as long as I print the sheets first then word doc and while using my PDF creator as the printer.
It may be a train wreak, I'm still learning the does and dont's.

Code: Select all

If CheckBox1.Value = True Then
 Worksheets("Sheet1").Visible = xlSheetVisible
 Worksheets("Sheet1").Select
    Range("SHEET1").PrintOut Copies:=1, Collate:=True
    Worksheets("Sheet1").Visible = xlSheetHidden
    Application.Wait Now + TimeSerial(0, 0, 5)
    End If

If CheckBox2.Value = True Then
 Worksheets("Sheet2").Visible = xlSheetVisible
 Worksheets("Sheet2").Select
    Range("SHEET2").PrintOut Copies:=1, Collate:=True
    Worksheets("Sheet2").Visible = xlSheetHidden
    Application.Wait Now + TimeSerial(0, 0, 5)
    End If

    Set wdapp = CreateObject("word.application")    ' open Word
    MyPath = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, "Folder Name\")
    If Dir(MyPath, vbDirectory) = "" Then
        MsgBox "Folder  " & MyPath & "  not found", vbCritical, "Error": Exit Sub
    End If
 
    For x = 3 To 4
        If Controls("checkbox" & x) Then
            Filename = MyPath & GetWordDocFileName(x)
            If Dir(Filename) <> "" Then    ' file exists
                Debug.Print "Printing file  " & Filename
                Set wdDoc = wdapp.Documents.Open(Filename)
                wdDoc.PrintOut    'Default print
                wdDoc.Close False    'Close without saving
            End If
        End If
    Next x
    wdapp.Quit    ' close Word
End Sub
 
Function GetWordDocFileName(ByVal index As Integer) As String
    Select Case index
        Case 3: GetWordDocFileName = "Part 1.docm"
        Case 4: GetWordDocFileName = "Part 2.docm"
        
    End Select

End Function