• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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


0
JGMS
Asked:
JGMS
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now