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

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


JGMSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

myShell.Run ("AcroRd32.exe /t " & sFilename,1,True)
0
vinnyd79Commented:
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


0
JGMSAuthor 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

0
vinnyd79Commented:
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


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JGMSAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.