Good morning -
I have a workbook that prints to pdf files using the Distiller. I now need
to open each file and print it. My issue is that my macro does not wait for
the print to happen and therefore does not print all files in the list. The
original code follows:

Sub Print_PDFs()
Dim intCount as integer
Dim person as string

intCount = 4
person = Sheets("Print Track").range("A" & intCount).value
if Dir("D:\Test\Test " & person & ".pdf")="" then
intCount = intCount + 1
person = Sheets("Print Track").range("A" & intCount).value
else
Do while person < ""
shell ("C:\Program Files\Adobe\Acrobat\Acrobat\Acrobat.exe" &
"D:\Test\Test " & person & ".pdf)
application.sendkeys "^p~"
application.sendkeys "%fx"
intCount = intCount + 1
person = Sheets("Print Track").range("A" & intCount).value
Loop
end if
end sub

I tried modifying the loop as follows but still throws the printing off:
Do while person < ""
shell ("C:\Program Files\Adobe\Acrobat\Acrobat\Acrobat.exe" &
"D:\Test\Test " & person & ".pdf")
application.wait now + timevalue("00:00:02")
application.sendkeys "^p~"
application.wait now + timevalue("00:00:05")
application.sendkeys "%fx"
intCount = intCount + 1
person = Sheets("Print Track").range("A" & intCount).value
Loop

The problem there is that because not all pdf files are the same number of
pages, the wait time could vary. Is there a way to make the macro wait for
Adobe to print the file before moving on to the next line of code? I also
tried this code to print the file but had no success (the code runs without
error but does not seem to do anything):

Public Sub AcrobatPrint()
Dim AcroExchApp As Acrobat.CAcroApp
Dim AcroExchAVDoc As Acrobat.CAcroAVDoc
Dim AcroExchPDDoc As Acrobat.CAcroPDDoc
Dim num, intCount As Integer
Dim filey, person as string
intCount = 4
person = Sheets("Print Track").range("A" & intCount).value
filey = "D:\Test\Test " & person & ".pdf"
Set AcroExchApp = CreateObject("AcroExch.App")
Set AcroExchAVDoc = CreateObject("AcroExch.AVDoc")
AcroExchAVDoc.Open filey, ""
Set AcroExchPDDoc = AcroExchAVDoc.GetPDDoc
num = AcroExchPDDoc.GetNumPages - 1
Call AcroExchAVDoc.PrintPages(0, num, 1, True, True)
AcroExchApp.Exit
AcroExchAVDoc.Close (True)
AcroExchPDDoc.Close
End Sub

Thanks in advance for your time.

I have a workbook that prints to pdf files using the Distiller

Why don't you print to both pdf and the printer in the initial go-round?

HTH,
Bernie
MS Excel MVP


"CodeCrazy" wrote in message
...
Good morning -
I have a workbook that prints to pdf files using the Distiller. I now need
to open each file and print it. My issue is that my macro does not wait for
the print to happen and therefore does not print all files in the list. The
original code follows:

Sub Print_PDFs()
Dim intCount as integer
Dim person as string

intCount = 4
person = Sheets("Print Track").range("A" & intCount).value
if Dir("D:\Test\Test " & person & ".pdf")="" then
intCount = intCount + 1
person = Sheets("Print Track").range("A" & intCount).value
else
Do while person < ""
shell ("C:\Program Files\Adobe\Acrobat\Acrobat\Acrobat.exe" &
"D:\Test\Test " & person & ".pdf)
application.sendkeys "^p~"
application.sendkeys "%fx"
intCount = intCount + 1
person = Sheets("Print Track").range("A" & intCount).value
Loop
end if
end sub

I tried modifying the loop as follows but still throws the printing off:
Do while person < ""
shell ("C:\Program Files\Adobe\Acrobat\Acrobat\Acrobat.exe" &
"D:\Test\Test " & person & ".pdf")
application.wait now + timevalue("00:00:02")
application.sendkeys "^p~"
application.wait now + timevalue("00:00:05")
application.sendkeys "%fx"
intCount = intCount + 1
person = Sheets("Print Track").range("A" & intCount).value
Loop

The problem there is that because not all pdf files are the same number of
pages, the wait time could vary. Is there a way to make the macro wait for
Adobe to print the file before moving on to the next line of code? I also
tried this code to print the file but had no success (the code runs without
error but does not seem to do anything):

Public Sub AcrobatPrint()
Dim AcroExchApp As Acrobat.CAcroApp
Dim AcroExchAVDoc As Acrobat.CAcroAVDoc
Dim AcroExchPDDoc As Acrobat.CAcroPDDoc
Dim num, intCount As Integer
Dim filey, person as string
intCount = 4
person = Sheets("Print Track").range("A" & intCount).value
filey = "D:\Test\Test " & person & ".pdf"
Set AcroExchApp = CreateObject("AcroExch.App")
Set AcroExchAVDoc = CreateObject("AcroExch.AVDoc")
AcroExchAVDoc.Open filey, ""
Set AcroExchPDDoc = AcroExchAVDoc.GetPDDoc
num = AcroExchPDDoc.GetNumPages - 1
Call AcroExchAVDoc.PrintPages(0, num, 1, True, True)
AcroExchApp.Exit
AcroExchAVDoc.Close (True)
AcroExchPDDoc.Close
End Sub

Thanks in advance for your time.

Hi Bernie -
I need it to be two separate processes. When printing to the distiller,
none of the files are opened and the distiller can handle multiple files
being sent to it. My macro is done running way before the distiller has
completed converting all of the files. I need this macro to open, then
print, then close each file then move on to the next file. Is there a way to
print it without opening it and closing it?

"Bernie Deitrick" wrote:

I have a workbook that prints to pdf files using the Distiller

Why don't you print to both pdf and the printer in the initial go-round?

HTH,
Bernie
MS Excel MVP


"CodeCrazy" wrote in message
...
Good morning -
I have a workbook that prints to pdf files using the Distiller. I now need
to open each file and print it. My issue is that my macro does not wait for
the print to happen and therefore does not print all files in the list. The
original code follows:

Sub Print_PDFs()
Dim intCount as integer
Dim person as string

intCount = 4
person = Sheets("Print Track").range("A" & intCount).value
if Dir("D:\Test\Test " & person & ".pdf")="" then
intCount = intCount + 1
person = Sheets("Print Track").range("A" & intCount).value
else
Do while person < ""
shell ("C:\Program Files\Adobe\Acrobat\Acrobat\Acrobat.exe" &
"D:\Test\Test " & person & ".pdf)
application.sendkeys "^p~"
application.sendkeys "%fx"
intCount = intCount + 1
person = Sheets("Print Track").range("A" & intCount).value
Loop
end if
end sub

I tried modifying the loop as follows but still throws the printing off:
Do while person < ""
shell ("C:\Program Files\Adobe\Acrobat\Acrobat\Acrobat.exe" &
"D:\Test\Test " & person & ".pdf")
application.wait now + timevalue("00:00:02")
application.sendkeys "^p~"
application.wait now + timevalue("00:00:05")
application.sendkeys "%fx"
intCount = intCount + 1
person = Sheets("Print Track").range("A" & intCount).value
Loop

The problem there is that because not all pdf files are the same number of
pages, the wait time could vary. Is there a way to make the macro wait for
Adobe to print the file before moving on to the next line of code? I also
tried this code to print the file but had no success (the code runs without
error but does not seem to do anything):

Public Sub AcrobatPrint()
Dim AcroExchApp As Acrobat.CAcroApp
Dim AcroExchAVDoc As Acrobat.CAcroAVDoc
Dim AcroExchPDDoc As Acrobat.CAcroPDDoc
Dim num, intCount As Integer
Dim filey, person as string
intCount = 4
person = Sheets("Print Track").range("A" & intCount).value
filey = "D:\Test\Test " & person & ".pdf"
Set AcroExchApp = CreateObject("AcroExch.App")
Set AcroExchAVDoc = CreateObject("AcroExch.AVDoc")
AcroExchAVDoc.Open filey, ""
Set AcroExchPDDoc = AcroExchAVDoc.GetPDDoc
num = AcroExchPDDoc.GetNumPages - 1
Call AcroExchAVDoc.PrintPages(0, num, 1, True, True)
AcroExchApp.Exit
AcroExchAVDoc.Close (True)
AcroExchPDDoc.Close
End Sub

Thanks in advance for your time.