We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

JGMS
JGMS asked
on
Medium Priority
437 Views
Last Modified: 2008-02-01
L.S.,

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,

JGMS


Comment
Watch Question

Commented:
Have you tried setting the bWaitOnReturn Parameter to True?

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

Commented:
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

Commented:
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.

JGMS

Commented:
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


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.