How to ensure the printing order of documents when printing via VBA

Posted on 2006-04-05
Last Modified: 2008-02-01

I have a list of Excel files and PDF files in an Excel workbook, and wrote a VBA macro to read from this list the path and filename and names of the sheets to be printed. I do want to print the files in the sequence as they appear in the list. However, that is now how it is send to the printed . As for instance,  I read and print the data for a certain worksheet, next that of a PDF file, and thereafter that of another worksheet. However, the two worksheets are printed first and thereafter the PDF file exits from the printer.

The VBA macro prints the sheets through the Excel printout method, Excel being active of course. However, the PDF file is printed via an API call like
  >>   Set myShell = CreateObject("WScript.Shell")  <<
  >>   myShell.Run ("AcroRd32.exe /t " & sFilename) <<

How can a force the printing order to follow that as given in the list?

Hope you can help me out. Many thanks ahead,


Question by:JGMS
    LVL 28

    Expert Comment

    Have you tried setting the bWaitOnReturn Parameter to True?

    myShell.Run ("AcroRd32.exe /t " & sFilename,1,True)
    LVL 28

    Expert Comment

    Just noticed where you mentioned an API call. Actually that is vbscript and uses the Windows Scripting Host.  I don't think adding True like I suggested will work because it will wait for acrobat to close. When acrobat prints a file does it stay open? You could try putting a sleep in after printing the pdf to give it time to hit the spooler/printer first.

    myShell.Run ("AcroRd32.exe /t " & sFilename)

    Wscript.sleep 5000 ' wait 5 sec

    ' print second excel file


    Author Comment

    Hi "Vinnyd79"

    thank you for your response. I tried the 'sleep' comment (on a Windows 2000 sp4 machine with Excel 2002) but it was not accepted: it was reported that the Wscript did not support the sleep command.

    Isn't it somehow possible to read information from the printer spooler within a VBA macro? It should in some way be possible to get a response that the file is received, isn't it?

    Please note that I will not be able to respond during the coming week, but I hope that will not stop you or anyone to find a solution for my problem.

    Many thanks ahead.


    LVL 28

    Accepted Solution

    I think the problem is that Acrobat is slower than excel and the second excel file is being received by the spooler first. I did a test and I got the same results. When I put in the wscript.sleep I was thinking vbscript. In VBA try adding this to the top of
    a userform or a module. If you put it into a module, then change "Private" to "Public"

    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Then use this instead of Wscript.sleep

    Sleep 5000


    Author Comment

    Hi "Vinnyd79"

    Thank you again for your comment and for your patience for me to respond. I have changed the code accordingly and it worked.

    I grant you the points.

    Do you have any clue what to do to get a single instance of Acrobat Reader (I use version 6.0) rather than an instance for each PDF file to be printed?

    Regards, JGMS

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now