JGMS
asked on
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.Shel
>> 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
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.Shel
>> 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
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
myShell.Run ("AcroRd32.exe /t " & sFilename)
Wscript.sleep 5000 ' wait 5 sec
' print second excel file
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
myShell.Run ("AcroRd32.exe /t " & sFilename,1,True)