PDA

View Full Version : Excel Print Specific word document page



bryVA
05-08-2009, 10:28 AM
Hello all,

I have setup a userform in Excel 2000 that allows the user to click on a checkbox to select which page of a word document they want to print. The following is my code"

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim strFPath As String
Dim oWord As Object
Dim oDoc As Document
strFPath = ThisWorkbook.Path & Application.PathSeparator
On Error Resume Next
Set oWord = GetObject(Class:="Word.Application")
With oWord
.Application.Visible = False
Set oDoc = .Documents.Add(Template:=strFPath & "FNOD docs.dot")
End With

Set objword = CreateObject("Word.Application")
On Error Resume Next
Dim e1, e2, e3, e4, e5, e6, e7, e8, e9 As Integer
Dim c1, c2, c3, c4, c5, c6, c7, c8 As String
Dim epage As String
e1 = Empty
e2 = Empty
e3 = Empty
e4 = Empty
e5 = Empty
e6 = Empty
e7 = Empty
e8 = Empty
e9 = Empty
c1 = ""
c2 = ""
c3 = ""
c4 = ""
c5 = ""
c6 = ""
c7 = ""
c8 = ""
If CheckBox1 = True Then e1 = "1"
If CheckBox2 = True Then e2 = "2"
If CheckBox3 = True Then e3 = "3"
If CheckBox4 = True Then e4 = "4"
If CheckBox5 = True Then e5 = "5"
If CheckBox6 = True Then e6 = "6"
If CheckBox7 = True Then e7 = "7"
If CheckBox8 = True Then e8 = "8"
If CheckBox9 = True Then e9 = "9"
If CheckBox1 = True Then c1 = ", "
If CheckBox2 = True Then c2 = ", "
If CheckBox3 = True Then c3 = ", "
If CheckBox4 = True Then c4 = ", "
If CheckBox5 = True Then c5 = ", "
If CheckBox6 = True Then c6 = ", "
If CheckBox7 = True Then c7 = ", "
If CheckBox8 = True Then c8 = ", "
epage = e1 & c1 & e2 & c2 & e3 & c3 & e4 & c4 & e5 & c5 & e6 & c6 & e7 & c7 & e8 & c8 & e9
On Error GoTo errhandler
With Dialogs(wdDialogFilePrint)
.Range = wdPrintRangeOfPages
.Pages = epage
.Execute
End With
With oDoc
.Close
End With

With oDoc
.Close
End With

oWord.Close
Unload Me
Exit Sub
errhandler:
MsgBox ("A error occurred. Please manually print the needed documentation.")
End Sub

I can't get this to work. I orginially had it in a word document module which it opened the other word document and printed the pages. It worked but when I have andjusted it for excel it doesn't work. What can I do to correct this? I know that there is a better way to do this but I diffenently don't know VBA as well as I would like to. Please educate me.

Thanks all,

Bryan

mdmackillop
05-08-2009, 12:52 PM
Try this
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim strFPath As String
Dim oWord As Object
Dim oDoc As Document
Dim objWord As Object
Dim c As Control
Dim epage As String

strFPath = ThisWorkbook.Path & Application.PathSeparator
On Error Resume Next
Set oWord = GetObject(Class:="Word.Application")
With oWord
.Application.Visible = False
Set oDoc = .Documents.Add(Template:=strFPath & "FNOD docs.dot")
End With

Set objWord = CreateObject("Word.Application")
On Error Resume Next



'OK for 9 checkboxes

For Each c In Me.Controls
If LCase(Left(c.Name, 8)) = "checkbox" Then
If c = True Then
epage = epage & Right(c.Name, 1) & ","
End If
End If
Next
epage = Left(epage, Len(epage) - 1)

On Error GoTo errhandler
With oWord.Dialogs(wdDialogFilePrint)
.Range = wdPrintRangeOfPages
.Pages = epage
.Execute
End With
With oDoc
.Close
End With

oWord.Quit
Unload Me
Exit Sub
errhandler:
MsgBox ("A error occurred. Please manually print the needed documentation.")
End Sub

bryVA
05-08-2009, 01:05 PM
Thank you mdmackillop. However I get an error that states the Object Variable or with block variable not set and it comes up on this line of the code:
With oWord.Dialogs(wdDialogFilePrint)
What am I doing wrong?

Thank you for your help,

Bryan

mdmackillop
05-08-2009, 01:19 PM
Obviously works for me. What version of office are you on? Only thing I can think is to check Word for the numeric value of wdDialogFilePrint and insert that instead.

bryVA
05-08-2009, 01:22 PM
I am using Excel 2000. What would the numeric value be?

mdmackillop
05-08-2009, 01:26 PM
Try 88