Avatar of shieldsco
shieldsco
Flag for United States of America asked on

Access VBA Print All Files in Folder

I would like to print all files (PDFs) in a folder on my disk. I need to prompt the user for the location of the files and then print to the default printer continuously. What would the code look like?
Microsoft AccessVBA

Avatar of undefined
Last Comment
shieldsco

8/22/2022 - Mon
Nick67

Lots of fun.
You need a Reference to the MS Office xx.0 Object library
That'll get you the FileDialog box

So this is the start, code to select a folder

'first we have to select the desired folder
'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

TryAgain:
'Use a With...End With block to reference the FileDialog object.
With fd
    .InitialView = msoFileDialogViewDetails ' msoFileDialogViewThumbnail
    .Title = "Folder Selector"
    '.InitialFileName = theFile
    .ButtonName = "Use this folder"
    .AllowMultiSelect = False

    'The user pressed the action button.
    If .Show = True Then
        If .SelectedItems.count = 0 Then
            MsgBox "You didn't make a valid selection.  Try again!"
            GoTo TryAgain
        End If
        msgbox .SelectedItems(1)

'...more code to actually do stuff with the files and subfolder(s) found here
    Else
        Exit Sub
    End If
End With

Open in new window


Now to flesh out this
'...more code to actually do stuff with the files and subfolder(s) found here

What will be used to handle your PDFs?
Acrobat?
Acrobat Reader?
what version(s)
Something else?
A lot depends on the answer.

VBA can't deal with PDFs straight-up.
Something external has to be invoked/shelled and so paths to the executable can become important
shieldsco

ASKER
Adobe Reader XI 11.0.13 or Adobe Acrobat XI
Nick67

You can try this first.
Put this code in a module
Option Compare Database
Option Explicit
Public Declare Function ShellExecute 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 Function PrintThisDoc(formname As Long, FileName As String)
On Error Resume Next
Dim X As Long
X = ShellExecute(formname, "Print", FileName, 0&, 0&, 3)
End Function
 
Sub PrintPDF(FullPath As String)
Dim printThis
printThis = PrintThisDoc(FullPath)
End Sub

Open in new window


A little WinAPI stuff.

Be warned.
It strands an open, without document, instance of Arobat Reader when it is done, and I've never found a nice way to clean that up.

Call it like so

PrintPDF("c:\temp\somePDFILike.pdf")

If that flies, next you'll need to construct code to look at all the files and subfolders in the selected folder and, in a loop send the PDFs to PrintPDF(somefolder\somepdf.pdf)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
shieldsco

ASKER
compile error: By ref argument type mismatch in line printThis = PrintThisDoc(FullPath)........FullPath
SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
shieldsco

ASKER
Scott - compile error....syntax error

  Call Shell ("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe" /N /T folder & "\" & file & ".pdf" "WOC4FL-4177", vbNormalFocus)
Scott McDaniel (EE MVE )

I'd suspect there's a problem with the quotes. Without being able to test, you might try this:

  Call Shell (Chr(34) & "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe" & chr(34) /N /T & chr(34) folder & "\" & file & ".pdf" & chr(34) & chr(34) "WOC4FL-4177", vbNormalFocus)

The goal is to enclose the entire call to acrord32 in double quotes, including the switches, and to also enclose the entire folder + file + pdf in double quotes, and then enclose the whole command in double quotes (up to the printer name).

If that doesn't work, then debug.print the command you're sending to shell and post it back here:

Debug.Print Chr(34) & "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe" & chr(34) /N /T & chr(34) folder & "\" & file & ".pdf" & chr(34) & chr(34)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
shieldsco

ASKER
New compile error: next without for

 Do Until Len(file) = 0
Call Shell("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe" / N / T / folder & "\" & file & ".pdf" / "WOC4FL-4177", vbNormalFocus)
   file = Dir
 Next
shieldsco

ASKER
Here's the complete code:
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Dim vrtSelectedItem As Variant

TryAgain:
With fd
    .InitialView = msoFileDialogViewDetails ' msoFileDialogViewThumbnail
    .Title = "Folder Selector"
    '.InitialFileName = theFile
    .ButtonName = "Use this folder"
    .AllowMultiSelect = False

    'The user pressed the action button.
    If .Show = True Then
        If .SelectedItems.Count = 0 Then
            MsgBox "You didn't make a valid selection.  Try again!"
            GoTo TryAgain
        End If
        MsgBox .SelectedItems(1)

'...more code to actually do stuff with the files and subfolder(s) found here

Dim file As String
 Dim folder As String
 folder = fd.SelectedItems(1)
 file = Dir(folder & "\*.pdf")

 Do Until Len(file) = 0
Call Shell("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe" / N / T / folder & "\" & file & ".pdf" / "WOC4FL-4177", vbNormalFocus)
   file = Dir
Next




    Else
        Exit Sub
    End If
End With
shieldsco

ASKER
Runtime error 13 type mismatch in line: Call Shell("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe" / N / T / folder & "\" & file & ".pdf" / "WOC4FL-4177", vbNormalFocus)

Complete Code:
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Dim vrtSelectedItem As Variant

TryAgain:
With fd
    .InitialView = msoFileDialogViewDetails ' msoFileDialogViewThumbnail
    .Title = "Folder Selector"
    '.InitialFileName = theFile
    .ButtonName = "Use this folder"
    .AllowMultiSelect = False

    'The user pressed the action button.
    If .Show = True Then
        If .SelectedItems.Count = 0 Then
            MsgBox "You didn't make a valid selection.  Try again!"
            GoTo TryAgain
        End If
        MsgBox .SelectedItems(1)

'...more code to actually do stuff with the files and subfolder(s) found here

Dim file As String
 Dim folder As String
 folder = fd.SelectedItems(1)
 file = Dir(folder & "\*.pdf")
 Do Until Len(file) = 0
Call Shell("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe" / N / T / folder & "\" & file & ".pdf" / "WOC4FL-4177", vbNormalFocus)
   file = Dir
   For i = 1 To 100

Next

Loop


   ' Else
        Exit Sub
    End If
End With
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
shieldsco

ASKER
Cleaned up code:

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Dim vrtSelectedItem As Variant

TryAgain:
With fd
    .InitialView = msoFileDialogViewDetails ' msoFileDialogViewThumbnail
    .Title = "Folder Selector"
    '.InitialFileName = theFile
    .ButtonName = "Use this folder"
    .AllowMultiSelect = False

    'The user pressed the action button.
    If .Show = True Then
        If .SelectedItems.Count = 0 Then
            MsgBox "You didn't make a valid selection.  Try again!"
            GoTo TryAgain
        End If
        MsgBox .SelectedItems(1)

'...more code to actually do stuff with the files and subfolder(s) found here

Dim file As String
 Dim folder As String
 folder = fd.SelectedItems(1)
 file = Dir(folder & "\*.pdf")
 Do Until Len(file) = 0
Call Shell("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe" / N / T / folder & "\" & file & ".pdf" / "WOC4FL-4177", vbNormalFocus)
   file = Dir
   'For i = 1 To 100

'Next

Loop


   ' Else
        Exit Sub
    End If
End With
shieldsco

ASKER
Anybody else have any thoughts on the posts above.... I'm under a deadline. Thanks
Jeffrey Coachman

If you are under an immediate deadline, then just open explorer to the target folder.
Hit:  Ctrl+A  (to select all the files)
Then right-click and select Print...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
shieldsco

ASKER
That's not the right answer Jeffrey. I did not say immediate... I said deadline
shieldsco

ASKER
Thanks anyway Jeffery
Scott McDaniel (EE MVE )

As mentioned previously, check your double quotes and such in the Shell call. You have to be sure those are in place correctly.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jeffrey Coachman

shieldsco,

Please, first work with Scott to get the basic "Print" code working with *One* pdf file first.

Then worry about looping...
Nick67

I am thoroughly buried today.
The API code as follows should compile and run
Others have supplied the Dir() looping necessary
You can Shell() and I have, but it's a pain to syntax and breaks as Acrobat Reader gets updated.

Option Compare Database
Option Explicit
Public Declare Function ShellExecute 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 Function PrintThisDoc(formname As Long, Filename As String)
On Error Resume Next
Dim X As Long
X = ShellExecute(formname, "Print", Filename, 0&, 0&, 3)
End Function
 
Sub PrintPDF(FullPath As String)
Dim printThis
printThis = PrintThisDoc(0, FullPath)
End Sub

Open in new window

shieldsco

ASKER
Ok Scott I fixed the type mismatch and Adobe Opens with this error message : there was an error opening this document ... the specified path is invalid
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
shieldsco

ASKER
Here's the code:

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Dim vrtSelectedItem As Variant

TryAgain:
With fd
    .InitialView = msoFileDialogViewDetails ' msoFileDialogViewThumbnail
    .Title = "Folder Selector"
    '.InitialFileName = theFile
    .ButtonName = "Use this folder"
    .AllowMultiSelect = False

    'The user pressed the action button.
    If .Show = True Then
        If .SelectedItems.Count = 0 Then
            MsgBox "You didn't make a valid selection.  Try again!"
            GoTo TryAgain
        End If
        MsgBox .SelectedItems(1)

'...more code to actually do stuff with the files and subfolder(s) found here

Dim file As String
 Dim folder As String
  folder = fd.SelectedItems(1)
 file = Dir(folder & "\*.pdf")
 Do Until Len(file) = 0
   
 
Call Shell("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe /N /T /" & folder & "\" & file & ".pdf /WOC4FL-4177", vbNormalFocus)
   file = Dir

Loop


        Exit Sub
    End If
End With
shieldsco

ASKER
Ok Scott after more work  Adobe opens with this error message : there was an error opening this document ... the file cannot be found

Here's the code:

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Dim vrtSelectedItem As Variant

TryAgain:
With fd
    .InitialView = msoFileDialogViewDetails ' msoFileDialogViewThumbnail
    .Title = "Folder Selector"
    '.InitialFileName = theFile
    .ButtonName = "Use this folder"
    .AllowMultiSelect = False

    'The user pressed the action button.
    If .Show = True Then
        If .SelectedItems.Count = 0 Then
            MsgBox "You didn't make a valid selection.  Try again!"
            GoTo TryAgain
        End If
        MsgBox .SelectedItems(1)

'...more code to actually do stuff with the files and subfolder(s) found here

Dim file As String
 Dim folder As String
   folder = fd.SelectedItems(1)
 file = Dir(folder & "\*.pdf")
 Do Until Len(file) = 0
   
 
Call Shell("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acrord32.exe /N /T " & folder & "\" & file & "WOC4FL-4177", vbNormalFocus)
   file = Dir

Loop


        Exit Sub
    End If
End With
Nick67

Ok
Here it is with a sample
Reference  to the MS Office xx.0 Object library required
Reference to the Windows script Host Object model required

Here's the module
Option Compare Database
Option Explicit
Public Declare Function ShellExecute 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 Function PrintThisDoc(formname As Long, Filename As String)
On Error Resume Next
Dim X As Long
X = ShellExecute(formname, "Print", Filename, 0&, 0&, 3)
End Function
 
Sub PrintPDF(FullPath As String)
Dim printThis
printThis = PrintThisDoc(0, FullPath)
End Sub

Open in new window


Here's the command button sub

Private Sub cmdPrintThem_Click()
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Dim vrtSelectedItem As Variant

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dim myfolder As folder
Dim myfile As file
    
TryAgain:
With fd
    .InitialView = msoFileDialogViewDetails ' msoFileDialogViewThumbnail
    .Title = "Folder Selector"
    '.InitialFileName = theFile
    .ButtonName = "Use this folder"
    .AllowMultiSelect = False

    'The user pressed the action button.
    If .Show = True Then
        If .SelectedItems.Count = 0 Then
            MsgBox "You didn't make a valid selection.  Try again!"
            GoTo TryAgain
        End If
        MsgBox .SelectedItems(1)

    '...more code to actually do stuff with the files and subfolder(s) found here
    
       Set myfolder = fs.GetFolder(.SelectedItems(1))
       For Each myfile In myfolder.Files
           If fs.getextensionname(myfile.Name) = "pdf" Then
               MsgBox myfile.Name
               PrintPDF (myfile.Name)
           End If
       Next myfile
    Else
        'bailed
    End If
End With

End Sub

Open in new window

PrintPDFs.mdb
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
shieldsco

ASKER
I get a runtime error 13 type mismatch......I'm using access 2013...Does that make a difference?

       Set myfolder = fs.GetFolder(.SelectedItems(1))
Nick67

I'll check on a 2013 box tomorrow morning.
It shouldn't make a difference

Error using the sample or error flanging the code into your app?
shieldsco

ASKER
error in my app....also I imported the objects in sample app to my app and I got the same error. I think we are almost there. Thanks
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
shieldsco

ASKER
Nick - how do I send the files to the printer. I get a popup message for each file however nothing prints...
Nick67

Set myfolder = fs.GetFolder(.SelectedItems(1))
       For Each myfile In myfolder.Files
           If fs.getextensionname(myfile.Name) = "pdf" Then
              MsgBox myfile.Name
               PrintPDF (myfile.Name)
           End If
       Next myfile

In bold is the msgbox
in italics is the line that causes the print.

I don't know why it fails on Access 2013 on Windows 10 x64--and fails silently.
It works for Access 2003 on Windows 7 x64 swimmingly

I really don't know.
@Scott and @Jeff can you test the sample on A2010/A2013 and see if you have the same silent failure?
Jeffrey Coachman

@Nick,
Yep fails for me on Win 7 64 bit/ Office 2013

FileDialog, user-defined type not defined
error
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Nick67

I have the same working on A2013 and A2003 now
Turns out A2003 was willing to print from this
PrintPDF (myfile.Name)
Which isn't the fully qualified path, but just the filename
A2013 needed the full path
PrintPDF (myfile.Path)

Sewed  things up a little tighter, commented out the msgbox on each file and got new API code that will msgbox any error codes.

New sample

What was interesting was the References
It needs one to MS Office xx.0 Object Library and
Windows Script Host Object Model

Usually, touching with a high version breaks references on the lower version.
The app moved back and forth seamlessly

@boag2000, no MS Office xx.0 Object Library breaks the FileDialog unless you late-bind it.
PrintPDFs-v1.mdb
shieldsco

ASKER
Printing works ok now but I still get error 13 type mismatch in line        Set myfolder = fs.GetFolder(.SelectedItems(1)).

I don't see the new sample code...
ASKER CERTIFIED SOLUTION
Nick67

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
shieldsco

ASKER
I still get an error 13 type mismatch
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
shieldsco

ASKER
References attached
References.PNG
Nick67

Is this from the sample?
I doubt it, since Outlook isn't referenced.

Does the sample work?
Tell me that FIRST

If it does, move the Outlook 15.0 reference below the Office 15.0  reference in your app, and test.
shieldsco

ASKER
I moved the Outlook 15.0 to below Windows Script Hosts and it seems to work.  I'm on a Citrix server right now at home and not mapped to any printers so I will test tomorrow in the office.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Nick67

Good enough.

Apparently the stranded Reader windows is a 'security features' added by Adobe.
https://forums.adobe.com/thread/1042132

Every one of us would consider it a bug, but apparently Adobe wants you to know when Reader is invoked to print a document.
shieldsco

ASKER
Nick - great job... I awarded Scott 50 points for his contributions