Excel VBA Macro for Printing URLs in Spreadsheet by Page Number

Question

What I have is an excel spreadsheet with hyperlinks to documents online, I want to print a certain range of these depending on requirements. Most of the time the whole document needs printed, but sometimes we only want certain pages printed, ideally this range of pages would be printed stapled as well.

I have a macro that somewhat does what I'm after:

Option Explicit

Sub PrintHyperlinkedPDFs()

Dim PDFrng As Range, PDF As Range
Dim AdobeReader As String, pdfLINK As String
                                        'there is an extra space at the end of this string
AdobeReader = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe "
Set PDFrng = Selection                  'change this to whatever method you want for setting
                                        'the range of PDF link cells to process and print
For Each PDF In PDFrng
    If PDF.Hyperlinks.Count > 0 Then pdfLINK = PDF.Hyperlinks(1).Address
    Shell """" & AdobeReader & """/n /t """ & pdfLINK & """"
Next PDF

End Sub

You highlight what cells containing the links you want to print then run and it sends some of them (if its more than around 4 documents it freezes and doesn't send them all).

Ideally, I would like to set-up some buttons to print pre-defined combinations of these documents but I'm not exactly a VBA professional and therefore this may be out of my skill range..

Any help would be much appreciated.

user3514930 user3514930 · Accepted Answer · 2014-06-18T13:15:01

You can add a Button directly on the Sheet and assign the macro SetupBtn already stored in the sheet:

Sub SetupBtn()
    ActiveSheet.Range("B2,B4").Select
    PrintHyperlinkedPDFs
End Sub

This macro use your Sub, selecting the cells you want before the Sub.
Creating the combination of cells, you create combination of print.
The sequence of cells are the sequence of print. If you want two copy of a document (for example a separator of pages), write something like that:

    ActiveSheet.Range("B2,B4,B2").Select

It's strange, but print 3 doc with 2 cells selected...

Excel VBA Macro for Printing URLs in Spreadsheet by Page Number

1 Answers