Solved Changing Printout in Loop

October 29, 2013 at 12:04:30
Specs: Windows 7
Hello everyone,

The below codes prints the column 'A' competely & then shifts to Column 'B' instead of that is it possible to shift alternate after every row. Starting Col A2 then shift to Col B2 next Col A3, B3 this will print consecutively/parallel at a time saving time as i have lot of printing.

Will Some one help me in adding this method?

Option Explicit
'~~> Printer names
Const Prin1 As String = "HP LaserJet Professional M1213nf MFP"
Const Prin2 As String = "HP LaserJet P1106"
Const AcroPath As String = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe"

Private Declare Function SetDefaultPrinter Lib "winspool.drv" _
"SetDefaultPrinterA" (ByVal pszPrinter As String) As Long

Sub Sample()
Dim ws As Worksheet
Dim lRow As Long, i As Long
Dim prt As String

'~~> Get current default printer
prt = Application.ActivePrinter

'~~> Set this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
'~~> Get last row of col A
lRow = .Range("A" & .Rows.Count).End(xlUp).Row

'~~> Set default printer
SetDefaultPrinter Prin1

'~~> Print from Col A first
For i = 1 To lRow
Shell """" & AcroPath & """/n /t """ & .Range("A" & i).Value & """"
DoEvents
Next i

'~~> Get last row of col B
lRow = .Range("B" & .Rows.Count).End(xlUp).Row

'~~> Set default printer
SetDefaultPrinter Prin2

'~~> Print from Col B Next
For i = 1 To lRow
Shell """" & AcroPath & """/n /t """ & .Range("B" & i).Value & """"
DoEvents
Next i
End With

SetDefaultPrinter prt
End Sub


See More: Changing Printout in Loop

Report •


#1
October 29, 2013 at 13:57:51
✔ Best Answer
First, a posting tip:

Before posting any more code in this forum, please click on the blue line at the end of this post and read the instructions on how to post VBA code and worksheet data in this forum.

As for your question, I can't actually test the code since I don't have your printers or data so I'll simply post what I think should work.

What I did was combine your 2 "printing loops" into 1 loop, so that each value of i is used first to print A & i and then B & i. Obviously the code has to keep changing the default printer to keep them both busy, so the SetDefaultPrinter command is also brought inside the loop.

Will this accomplish your goal of speeding up the printing process? Only you can tell us after you try it.

Sub Sample()
 Dim ws As Worksheet
 Dim lRow, lRow_A, lRow_B As Long, i As Long
 Dim prt As String

 '~~> Get current default printer
  prt = Application.ActivePrinter

 '~~> Set this to the relevant sheet
   Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
 '~~> Get last row of col A & B
      lRow_A = .Range("A" & .Rows.Count).End(xlUp).Row
      lRow_B = .Range("B" & .Rows.Count).End(xlUp).Row
 '~~> Set lRow to longest column
       If lRow_A > lRow_B Then lRow = lRow_A _
         Else lRow = lRow_B
 '~~> Print from Col A first
        For i = 1 To lRow
          
 '~~> Print from Column A if not empty
           If Range("A" & i).Value <> "" Then
 '~~> Set default printer for A
           SetDefaultPrinter Prin1
             Shell """" & AcroPath & """/n /t """ & .Range("A" & i).Value & """"
             DoEvents
           End If
        
          
 '~~> Print from Column B if not empty
           If Range("A" & i).Value <> "" Then
'~~> Set default printer for B
            SetDefaultPrinter Prin2
             Shell """" & AcroPath & """/n /t """ & .Range("B" & i).Value & """"
             DoEvents
           End If
        Next i

    End With

   SetDefaultPrinter prt
 End Sub


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


Report •
Related Solutions


Ask Question