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

[sgvthm95]

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

 

[John_w]

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.

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.

 

[phuongnguyenvpvn]

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.

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.

Dear Mr John
Thank for your posts
with this code i had an issue as picture below, could you help me to fix?
Thank you so much!!

 

[John_w]

Try this macro, which operates on the AutoFiltered active sheet

with this code i had an issue as picture below, could you help me to fix?

I can see that your sheet isn't AutoFiltered, hence the displayed message.

Please start your own thread with a full description of your requirements and your code.