Solved Printing to PDF (part macro including)

March 27, 2017 at 05:13:59
Specs: Windows 7
i have a excel with a macro that can print te selected pages:

*
Sub testenpdfprinten()
'
' testenpdfprinten Macro
'

Dim ab As Worksheet


If Range("r2") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=1, To:=1
End If
If Range("r3") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=2, To:=2
End If
If Range("r4") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=3, To:=3
End If
If Range("r5") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=4, To:=4
End If
If Range("r6") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=5, To:=5
End If
If Range("r7") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=6, To:=6
End If
If Range("r8") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=7, To:=7
End If
If Range("r9") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=8, To:=8
End If
If Range("r10") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=9, To:=9
End If
If Range("r11") = "Onwaar" Then
Else
Sheets("Certificaat").PrintOut from:=10, To:=10
End If

'
End Sub
*

first of all this macro works fine, but it print is with the last used printer. and i need it to print to a PDF.

but if i select pdf and than klik the macro than i every page as a single PDF instead of together.

does anybody know a solution?

Ps, sorry for the bad English :(


See More: Printing to PDF (part macro including)

Report •

#1
March 27, 2017 at 07:02:27
Before I work on your printer issue, I want to comment on your macro. If I understand its use correctly, you are printing out specific pages of the Certificaat sheet whenever a cell in Column R does not contain Onwaar. If that is correct, then the following code should accomplish the same goal.

Instead of using individual If-Then-Else instructions for each Row, it uses a variable to loop through the Rows in Column R and print the specified page. Because the number of the page that is printed is always 1 less the Row being currently tested, we can use the same variable for both the Row and the Page. We simply subtract 1 from the Row number.

Let me know if you have any questions. Meanwhile, I will work on your PDF question.

Sub testenpdfprinten_V2()
' Shorter Version Of testenpdfprinten Macro
' Loop through Column R, Rows 2:11
' Print page when cell not equal to Onwaar

Dim rw_page As Long

 For rw_page = 2 To 11
  If Range("R" & rw_page) <> "Onwaar" Then
   Sheets("Certificaat").PrintOut from:=rw_page - 1, To:=rw_page - 1
  End If
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 27, 2017 at 11:38:38
✔ Best Answer
The reason you are getting individual PDF's is because each printout that you do is a single print job. VBA isn't building a single print job from the multiple PrintOut instructions, it is simply doing what the code tells it to do, e.g. PrintOut Page x.

Sheets("Certificaat").PrintOut from:=4, To:=4
Sheets("Certificaat").PrintOut from:=6, To:=6

You may not "see" that when you do a hard copy print because the pages just keep coming out of the printer, but it really is a bunch of individual print jobs.

If you want to create a single PDF, you need to create a single print job to send to the printer. That can be done by "building" a PrintArea made up of the various pages. The following code shows one method of doing that.

At the beginning of the code I have set up an Array that contains all of the cell ranges for the individual pages. In this case, I have hard coded the ranges from my workbook into the list. The ranges might be different for you, so you may have to modify the ranges to match your sheet.

It may be possible to have VBA find the page breaks and determine the ranges, but I didn't want to go to that trouble until I determined if the following code actually works for you.

OK, so after the array has been populated, the code loops though the sheet looking for the cells that do not contain "Onwaar". Each time it finds a cell that does not contain "Onwaar" it adds the range for that "page" to the temporary PrintArea string. Once it has looped through all 11 rows, it cleans up the temporary PrintArea string by removing the extra comma at the end.

The code now has a final PrintArea string made up of the ranges for the specified pages to use for the single print job. My testing shows that the code will create a single PDF containing only the specified sheets.

One more item:

You said: "it print is with the last used printer. and i need it to print to a PDF.
but if i select pdf..."

I didn't want to make the code any more complicated at this point, but it can be written such that it saves the name of the current system printer, sets the new "printer" to PDF and then changes the printer back to the original when it is done. That way you don't have to "select" the PDF printer each time, you can just run the macro. If that is something you are interested in, let me know. For now, check out the code below and let me know how it works for you.

Sub testenpdfprinten_V3()
'
' Print Specific Pages As One Print Job
Dim rw As Long
Dim pageNum As Long
Dim temp_PrintArea As String
Dim final_PrintArea As String
Dim pageRng(1 To 10) As String

'Create Array of Print Areas By Page Range

    pageRng(1) = "$A$1:$I$47"
    pageRng(2) = "$A$48:$I$94"
    pageRng(3) = "$A$95:$I$141"
    pageRng(4) = "$A$142:$I$188"
    pageRng(5) = "$A$189:$I$235"
    pageRng(6) = "$A$236:$I$282"
    pageRng(7) = "$A$283:$I$329"
    pageRng(8) = "$A$330:$I$376"
    pageRng(9) = "$A$377:$I$423"
    pageRng(10) = "$A$424:$I$470"


'Loop through Column R, Rows 2:11
'rw = Row
'pageNum = Page To Print

 For rw = 2 To 11
     pageNum = rw - 1

'Build temporary PrintArea string from page ranges
  If Range("R" & rw) <> "Onwaar" Then
   temp_PrintArea = temp_PrintArea & pageRng(pageNum) & ","
  End If
  
 Next
  
'Strip off extra comma at end of temporary PrintArea string
'Set PrintArea, Print pages

   final_PrintArea = Left(temp_PrintArea, Len(temp_PrintArea) - 1)
   With Sheets("Certificaat")
     .PageSetup.PrintArea = final_PrintArea
     .PrintOut
   End With
   
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question