Print Word doc and Excel Worksheet during same printing sequ
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Print Word doc and Excel Worksheet during same printing sequ
Is it possible to print a Word document and an Excel Worksheet during same print sequence using Check Boxes from an Excel UserForm.
-
- 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
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.
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Print Word doc and Excel Worksheet during same printing sequ
Thank you HansV
I wasnt sure if it was possible to print both doc and sheet during same sequence.
I wasnt sure if it was possible to print both doc and sheet during same sequence.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Print Word doc and Excel Worksheet during same printing sequ
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.
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