Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Print External PDF Files from Excel Sheet

    Hi,
    Hope some can help me.
    I have a list of file locations of .pdf files in column A of my spreadsheet. All the path and files have info on Sheet1.
    i.e. Column A1 c:\test\packing_1.pdf A2 c:\test\country_1.pdf
    Can I use VBA to, when I run it, print all of the files in the list in column A1 to D20 ?
    Thanks in advance.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi foncesa

    Welcome to the Lounge!

    Yes, you can use vba to do what you want. Probably.
    I tried this code on my laptop, and it printed my test pdf files to my default printer.
    It uses the Adobe Reader to do the printing:
    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"
    
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each cell In Range(temp)                'loop through all entries in range
    zFile = cell.Value                          'fetch filename from cell
    
    If zFile Like "*.pdf" Then                  'check it is a pdf file type
    Shell (zProg & " /n /h /t " & zFile)        'execute command to print  the pdf document
    End If                                      'end of test for pdf file type
    
    Next                                        'process nect file in list
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    End Sub
    Note that after printing all the files, it leaves the Adobe Reader open.
    I don't know how to close it.
    Perhaps someone here can tell us that bit.
    Meanwhile, you can close the Adobe session manually.

    Hope this helps.
    I have attached a sample file containing this code.

    Let me know if this works for you.

    zeddy
    Attached Files Attached Files

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

    TR4235 (2015-06-24)

  4. #3
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post

    Let me know if this works for you.

    zeddy
    Thanks zeddy for reply, I tried on network printer but was unsuccessful to print, I have Windows 7, Office 2013.

    Then i tried to test it from the command prompt "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe" / n/ h/ t "C:\1.pdf" "HP LaserJet Professional M1213nf MFP" and it printed the document.

    The printer is HP LaserJet Professional M1213nf MFP with IP_192.168.1.40
    Does it require to add any references in VBA Editor.
    Please help to sort it, Thanks in advance.

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi foncesa

    Thanks for getting back.

    ..if you look at my NOTE: in post#1:
    '/t=print to default printer; or use /t <filename> <printername> <drivername> <portname>

    So, now that I know the name of your network printer, I amended the vba code to include this.
    See attached file.

    Please let me know if that works. I think it should!

    zeddy
    Attached Files Attached Files

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

    TR4235 (2015-06-24)

  7. #5
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    I tried & finally with this [ Shell """" & zProg & """/n /t """ & zFile & """" ] i got the success, Credit goes to you. Thankyou.
    I want to trouble you, Is there any way to have a popup asking which printer do you want to print, we have 2 printers on network.

    Once again thanks a lot.

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi foncesa

    In my previous attached file rz2-print-pdf-files.xlsm I think I should have left a space between the filename and printername in the Shell command.
    see attached new file.

    Note: When you use the Shell command, you can either use it like this with brackets Shell (xxx)
    e.g.
    Shell (command)
    ..or like this
    Shell (zProg & " /n /h /t " & zFile)
    or like this without brackets Shell "command"
    ..so that is why you had to use the double sets of quotes etc etc.

    If you let me know the name of your second network printer, I could give another update.

    zeddy
    Attached Files Attached Files

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

    TR4235 (2015-06-24)

  10. #7
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks Zeddy for explantion, you are very helpful person, God Bless You.

    The second printer on network HP LaserJet P1106.

    Thanks

  11. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi foncesa

    See attached file.

    This file, when opened with macros enabled, will put the name of your default Excel printer into a named cell [chosenPrinter]
    This is always done on startup, via the Workbook_Open event.

    The default Excel printer may vary, depending on what computer you are on.
    The names of available printers will also vary, depending on what network computer you are on.

    A Printer Selection button has been provided, which will allow you to select from the available printers for that computer.
    The selected printer name will be placed in the named cell [chosenPrinter]

    A button has been provided to start printing the pdf files listed in column [A]
    A message box is displayed telling you how many files will be printed, and what printer will be used.
    You can cancel this, and change the required Printer if you want to.

    I have modifed the code to allow for any spaces in the folder names for the pdf files.
    (For example, C:\My Documents\My folder\sample1.pdf)
    The folders containing the pdf files are assumed to be on drive C:

    Please let me know if this works.

    zeddy
    Attached Files Attached Files

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

    TR4235 (2015-06-24)

  13. #9
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks Zeddy, I appreciate your efforts for me, Thanks a lot.
    A small request to you, I have lots of printing and if send all of them to printer in one shot as the script does the printers stops printing because of lots of job or the job is lost over network. I want an pause of 2 minutes after 5 rows of column A, and continue for next 5 then pause to continue till end.

    With folded hands thankyou very much.

  14. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi foncesa

    Did the last attached file work OK?

    Yes, a pause can be put in the routine.
    Perhaps one of our helpers in this forum would like to have a go at this?

    I will check back if you don't get a response.

    zeddy

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

    TR4235 (2015-06-24)

  16. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi foncesa

    ..I see there hasn't been any other replies.

    So, in the attached file, I have added a two-minute pause after every five pdf files have been sent to the printer.
    I also included a progress message (in the bottom statusbar) and a completion message when finished.

    Please let me know if this works OK.

    zeddy
    Attached Files Attached Files

  17. #12
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks Zeddy, I have tested it and its 100%, perfect.
    Thankyou so much for your efforts. God Bless you.

  18. #13
    New Lounger
    Join Date
    Nov 2014
    Posts
    2
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by zeddy View Post
    Hi foncesa

    Welcome to the Lounge!

    Yes, you can use vba to do what you want. Probably.
    I tried this code on my laptop, and it printed my test pdf files to my default printer.
    It uses the Adobe Reader to do the printing:
    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"
    
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each cell In Range(temp)                'loop through all entries in range
    zFile = cell.Value                          'fetch filename from cell
    
    If zFile Like "*.pdf" Then                  'check it is a pdf file type
    Shell (zProg & " /n /h /t " & zFile)        'execute command to print  the pdf document
    End If                                      'end of test for pdf file type
    
    Next                                        'process nect file in list
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    End Sub
    Note that after printing all the files, it leaves the Adobe Reader open.
    I don't know how to close it.
    Perhaps someone here can tell us that bit.
    Meanwhile, you can close the Adobe session manually.

    Hope this helps.
    I have attached a sample file containing this code.

    Let me know if this works for you.

    zeddy
    Thanks zeddy.
    I tested and it does print the file. However it uses the default print settings and I need to use different settings. Normally with adobe once I change the settings for the first file, it then works for all files until adobe is closed and reopened. So I tried printing a file with the settings I want and then using the macro to print a file. However the file printed by the macro still used the default settings. Also after running the macro adobe was set back to the default settings.

    I tried removing the new instance flag from the macro (/n) but it didn't make any difference. Is there any way to solve this?

    Thanks in advance

  19. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Warna

    Welcome to the lounge.
    What sort of settings are you trying to change?
    Paper size??

    zeddy

  20. #15
    New Lounger
    Join Date
    Nov 2014
    Posts
    2
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi zeddy.
    Thanks for the quick reply.
    The settings are Quality, Colour & Collation. I think the settings are printer specific but the printer lets me save pre-sets which it then what I select.

    Thank you

  21. The Following User Says Thank You to warna For This Useful Post:

    TR4235 (2015-06-24)

Page 1 of 3 123 LastLast

Posting Permissions

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