asked on

Follow up Q to incremental printing in excel

Please see my answered question here:
https://www.experts-exchange.com/questions/28862199/incremental-printing-in-excel-2010.html

I have a follow up question.

Using the code i've posted in the solution reply, when the end user goes to print for example 50 pages, the printer actually outputs the pages in a random order.

Before we get into things like the printer model, driver, etc - lets look at what we can do in the code (because the printer may not always be the same)

The way forward, i think, is to insert some kind of pause in the script to give the print spooler a chance to take in each iteration... or, some kind of check to make sure it actually finished the print send before going on to the next loop

here's the original code below, and the end user reports pages outputting randomly like:
1+2
5-10
3-4
15-20
11-14
31-36
21-30
31-50

so when printing box labels, this becomes very annoying to re-sort the print job before applying labels

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
Cancel = True
Application.EnableEvents = False
Application.Dialogs(xlDialogPrinterSetup).Show
With Sheets("Sheet1")
    For i = 1 To .Range("M11").Value Step 2
        .Range("B8").Value = i
        .Range("B20").Value = .Range("B8").Value + 1
        Application.Calculate
        DoEvents
        .PrintOut
    Next i
End With
Application.EnableEvents = True
End Sub

Open in new window

crystal (strive4peace) - Microsoft MVP, Access

the proper way would be to find out the command for the spooler to release the job ... but short of that, here is code to pause for 2 seconds (2000 milliseconds)

at the top of the module, add these declaration statements:

#If VBA7 Then  
    Public Declare PtrSafe Sub Sleep Lib "kernel32" _
	(ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems  
#Else  
    Public Declare Sub Sleep Lib "kernel32" _
	(ByVal dwMilliseconds as Long) 'For 32 Bit Systems  
#End If

Open in new window

then here is the procedure you can call:

Public Sub WaitSeconds()

    'pause for 2 seconds
    Sleep 2000

End Sub

Open in new window

Saurabh Singh Teotia

I Believe this is what you are looking for..

Check Here

Saurabh...

crystal (strive4peace) - Microsoft MVP, Access

if you do incorporate the code Saurabh pointed you to, I would suggest sleeping then checking because continuously checking may consume unnecessary resources. Nice link though, for 32-bit. If you are running 64-bit, the declaration will need to be modified similar to the modification made above (#If VBA7 Then )

FocIS

ASKER

Hello, and thanks for the reply

So you know, i have virtually zero experience with VBA or macros in excel, so please excuse my ignorance here

When i add the declaration statements and the pause code, then go to print, i am told:
"compile error: constants, fixed-length strings, arrays, user-defined types and declare statements not allowed as Public members of object modules"

i've attached the actual file i'm working with, and here is the complete code i have:

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
Cancel = True
Application.EnableEvents = False
Application.Dialogs(xlDialogPrinterSetup).Show
With Sheets("Sheet1")
    For i = 1 To .Range("M11").Value Step 2
        .Range("B8").Value = i
        .Range("B20").Value = .Range("B8").Value + 1
        Application.Calculate
        DoEvents
        .PrintOut
        
Public Sub WaitSeconds()
    'pause for 2 seconds
    Sleep 2000

End Sub
        
    Next i
End With
Application.EnableEvents = True
End Sub

Open in new window

BOX-LABEL-PRINTER.xlsm

crystal (strive4peace) - Microsoft MVP, Access

Close ... but not quite right. The WaitSeconds procedure needs to be before or after the other one -- it cannot be in the middle.

to call it from the first procedure, use this statement:

CALL WaitSeconds

Open in new window

which can, of course, be in the middle ~

FocIS

ASKER

I see - so i moved the WaitSeconds code to before the printing loop, but what do i need to change to deal with the specified error message "compile error: constants, fixed-length strings, arrays, user-defined types and declare statements not allowed as Public members of object modules" which is still present after moving WaitSeconds (and using CALL)

what gets highlighted during that error is at the very top:
#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As LongPtr)

I have a 64bit operating system with 32bit excel, though i can't say it'll always be ran on a 64bit operating system, if that matters

ASKER CERTIFIED SOLUTION

FocIS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FocIS

ASKER

google searching resulted in the above code, which worked just as well. was unable to make the other suggestions work up to this point