Print PDF documents using hyperlink from excel from a dynamic list using VBA

sgvthm95

New Member
Joined
Mar 19, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi Guys,

I currently have an excel document that has a list of hyperlinked PDFs in column C with cell C1 being a filter to determine which PDF's from that folder I want to print.

Once I have filtered column A, I need to be able to print all the PDF's that are visible under that filter.

I have noticed some code using Shell however this is quite out of my league, my Adobe reader is at the following location:
C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe

I need it to also work on 32-bit and 64-bit version of excel.

Any help would be much appreciated.

Regards,

Sgvthm95
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Welcome to MrExcel forums.

Try this macro, which operates on the AutoFiltered active sheet, and loops through all the visible hyperlinks in column C starting at C2.
VBA Code:
Public Sub Print_Filtered_PDFs()

    Dim PDFcells As Range, PDFcell As Range
    
    With ActiveSheet
        If .AutoFilterMode Then
            Set PDFcells = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))        'column C hyperlinks to PDF files
        Else
            MsgBox .Name & " isn't AutoFiltered"
            Exit Sub
        End If
    End With
    
    For Each PDFcell In PDFcells.SpecialCells(xlCellTypeVisible)
        Print_File PDFcell.Hyperlinks(1).Address
    Next
    
End Sub

Private Sub Print_File(fileFullName As String)
    CreateObject("Shell.Application").Namespace(Left(fileFullName, InStrRev(fileFullName, "\"))).Items.Item(Mid(fileFullName, InStrRev(fileFullName, "\") + 1)).InvokeVerb "Print"
End Sub
The only problem is that the Acrobat/Adobe window is left open at the end. There is no way of printing the PDFs without opening the Acrobat/Adobe application, so you'll have to close it manually, or I guess Windows API code could be used to close it.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,733
Messages
5,638,048
Members
417,000
Latest member
JasonWilliam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top