Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Module to print a series of PDF files from within Excel 2010

    I am hopeful that there is someone skilled in Excel 2010 able to help solve my problem.

    This would run from within Excel. I need a macro/VBA to print a series of .PDF files based on values in a spreadsheet, then stop once it reaches a blank cell. For example;

    Column a
    100
    135
    146
    277


    The module would look at the first cell in column A (100) and print a PDF file named 100.pdf. It would then look at the next row (135) and print 135.pdf and so on until it reaches the blank cell after 277 / Joe. It would then stop.

    Any ideas as to a simple (or not) way of accomplishing this? Any help would be greatly appreciated.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Bruce

    Welcome to the Lounge!

    This code should do the trick:
    Code:
    Sub printPDFfiles()
    
    'ChDrive "E"                                '<< if your files are on a different drive!
    
    'NOTE:Example below uses Adobe Reader, but apply to Acrobat as well.
    'If you are using Acrobat, substitute Acrobat.exe in place of AcroRd32.exe
    'on the command line.
    
    'NOTE:
    '/s=don't show splash screen
    '/n=new instance
    '/h=minimised window
    '/t=print to default printer; or use /t <filename> <printername> <drivername> <portname>
    
    'CHECK YOUR ADOBE READER VERSION, AND USE CORRECT PATH..
    'zProg = "C:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe"
    zProg = "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe"
    
    zLastRow = [a65536].End(xlUp).Row           'find last row in column [A]; e.g. 15
    temp = "a1:a" & zLastRow                    'e.g. "a1:a15"
    
    zFolder = ThisWorkbook.Path & "\"           '<< or specify source folder here
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each cell In Range(temp)                'loop through all entries in range
    zFile = cell.Value & ".pdf"                 'create filename using cell contents
    zFetch = zFolder & zFile                    'full filepath and name to pdf file
    If Dir(Fetch) <> "" Then                    'check file exists
    Shell (zProg & " /n /h /t " & zFile)        'execute command to print  the pdf document
    End If                                      'end of test for pdf file type
    
    Next                                        'process next file in list
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    End Sub
    ..or search this forum for posts dealing with this issue.
    ..or have a look at this attached file

    zeddy
    Salvation Mission Planner
    Attached Files Attached Files
    Last edited by zeddy; 2015-07-15 at 14:39.

  3. The Following User Says Thank You to zeddy For This Useful Post:

    bruce.gaelick@gmail.com (2015-07-18)

  4. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    zeddy
    I very much appreciate your reply to my question, and for your recommendation. I will give the work you have done a try and let you know the results, but at first glance this is exactly what I am looking for.

    Again, thanks for the work you have done and I will get back to you as soon as I give it a try.

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Bruce

    ..we always appreciate the feedback, and especially good manners for the 'Thank you', and especially from a first-time poster too! It means we are much more inclined to give further help when asked.

    zeddy
    •Medical Research Volunteer

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, this should work for whatever your default PDF application is:
    Code:
    Option Explicit
    
    Private 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
    Const SW_HIDE            As Long = 0&
    
    Sub PrintPDFs()
        Dim sPath As String
        Dim rgCell As Excel.Range
        
        ' adjust path as required
        sPath = "C:\testing\"
        If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
        
        For Each rgCell In Range("A1").CurrentRegion.Columns(1).Cells
            If Len(rgCell.Value) <> 0 Then
                If Dir(sPath & rgCell.Value & ".pdf") <> vbNullString Then
                    PrintFile sPath & rgCell.Value & ".pdf"
                End If
            End If
        Next rgCell
        
    End Sub
    
    Sub PrintFile(strFilePath As String)
       ShellExecute Application.hWnd, "Print", strFilePath, 0&, 0&, SW_HIDE
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •