How to print multiple linked PDF Files from query result?

[Ajz1971]

Hi There,
I’m not sure if I’m in the right section here? But what I’m looking for is a way to do the following…


I have a query that gives me a list of Linked PDF Files (C:/Policies/Policy1.PDF…. C:/Policies/Policy2.PDF etc…) what I would like to do is be able to print off all documents automatically using VBA code that I can attach to the on click of a button…Is this possible?

The query is named 'Staff Query'... The column of linked files is 'Feild2' ....I do not need to view the PDF's, and Adobe PDF reader path is "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"



Thanks in advance for any feedback

 

[Micron]

this looks simple enough:

Option Explicit 
 
Declare Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ 
ByVal hwnd As Long, _ 
ByVal lpOperation As String, _ 
ByVal lpFile As String, _ 
ByVal lpParameters As String, _ 
ByVal lpDirectory As String, _ 
ByVal nShowCmd As Long) _ 
As Long 
 
Public Sub PrintFile(ByVal strPathAndFilename As String) 
     Call apiShellExecute(Application.hwnd, "print", strPathAndFilename, vbNullString, vbNullString, 0) 
     End Sub 
 
Sub Test() 
     PrintFile ("C:\Test.pdf") 
     End Sub

which came from here

 

[Ajz1971]

Hi Micron,

thanks for the reply!

Will this print off multiple PDF's from a list populated by a query in access?

Thanks

 

[Micron]

Yes if that list contains the path to the file. You would use the query to create a recordset and loop through the field containing the file path, repeating the part where the call is made to print the file.

 

[Ajz1971]

Hi again Micron,

I have some code that works but the only problem is...

1- it opens acrobat reader in print view (you have to click print for each file)
2- Its not printing the PDF's off in sequence as listed in the query (row 1, row2, row3...etc)

I've tried to use your given code with no luck ...part of being a newbie i guess

Here is the code...


Here is the code ...

Private Sub Command61_Click()
Dim sAcrobatReaderExe As String ' full path to the Acrobat reader executable
sAcrobatReaderExe = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
With CurrentDb.OpenRecordset("select field2 from [staff query]")
Do Until .EOF
Shell sAcrobatReaderExe & " /P " & Chr(34) & .Fields(0) & Chr(34)
.MoveNext
Loop
End With
End Sub

Any thoughts? ..... and again! Big thanks!

 

[Micron]

I am dumbfounded as to what just happened to me. I spent hours digging through many old posts to work on this; finding that each one presented their own issues, which I then go off on a tangent trying to work those out. Stumbling from forum to forum, finally I found one that seemed to address all issues, including Acrobat Reader's failure to obey vba code to close the window. Next step is to adapt to your need to print multiple files without user interaction, plus I know that a pause is needed between printouts or else Access just blows through and you might get 50% of them printed. So I open an old db, find my pause function, paste it in and what do I find? It's already in this db I use to muck about when working out issues in the forums I post in AND IT'S BEING CALLED BY A FRIGGIN' PRINT PDF's PROCEDURE I'VE ALREADY USED IN SOME OTHER POST!!! Here is the code

Function OpenPrintPdf()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String, sfile As String, strAcrobat As String
Dim RetVal

sql = "SELECT tblMyTable.pdfLink FROM tblMyTable WHERE [Print]=True;"
strAcrobat = "C:\Program Files\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"

Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
rs.MoveFirst
Do While Not rs.EOF
  sfile = rs.Fields(0)
  RetVal = Shell(strAcrobat & " /P " & Chr(34) & sfile & Chr(34), 0)
  Pause (3)
  SendKeys "^p~", True
  Pause (3)
  rs.MoveNext
Loop
SendKeys "^q", True 'key combo to close Adobe Reader
rs.Close
Set db = Nothing
Set rs = Nothing

End Function

So you could adapt that to fit your sql statement but I don't see why you don't just use the query you appear to have instead of writing a sql statement that seems to refer to it:

Set rs = db.OpenRecordset("staff query")
rs.MoveFirst
   Do While Not rs.EOF
   'Debug.Print rs.Fields(0)
   Shell sAcrobatReaderExe & " /p /h " & rs.Fields(0), vbHide
   rs.MoveNext
   Pause 3
Loop

Don't forget to close the rs and destroy the variables for db and rs either way, and ensure you have a reference to DAO in your code project.

You also need this little pause function (put it in a standard module so you can call it from any procedure).

Public Function Pause(intSecs As Integer)
Dim Start As Variant

Start = Timer
Do While Timer < Start + intSecs
    DoEvents
Loop
End Function

If you want the MUCH more complicated code to do the same thing, let me know.

 

[Ajz1971]

WOW!! ... Did not expect you to go through sooo much truouble Micron :wink:...BIG THANKS!!!

I've just got into work so have not had the chance to put it to the test yet... I'm sure it's going to do the trick tho

I'll get back to you with a High Five in a bit...hope you have a good day/night...thanks again M8

 

[Ajz1971]

Grrrr!

I try to reference the DAO and i get this?


-- removed inline image ---


I'm kinda a newbie at VBA ...'think that shows lol!' Have you got a working sample that I could look at? Really dont want to be taking up more of your time with this little *#'x

 

[Ajz1971]

Sorry! it didnt show the snapshot i put in/

Whats happening is that I try to reference the 'Microsoft DAO 3.6 Object Library and it gives me an Error in Loading DLL dialog?

 

[Ajz1971]

... just out of curiosity! is there a way to take the query results (Linked PDF's Files), combine these files into one pdf and then print it off... I'm thinking this would give the same result and only have acrobat reader open once?...probably wrong? Just thinking out loud