print multiple hyperlinked pdf files from excel vba-VBForums
Results 1 to 15 of 15

Thread: print multiple hyperlinked pdf files from excel vba

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    print multiple hyperlinked pdf files from excel vba

    I have a list of pdf files (hyperlinked) and I am trying to print them all when the user selects a 'print all' button. I do not want them to view the pdf document. They would just need to print them. I have also added individual command buttons that need to print each pdf document in the list or 'select some' from the list.
    I have exhausted all methods. The Shell command works in opening the document but does not print. I would also like to bring up the print dialog box so the user can select their printer or options of duplex printing before the print job starts. (the printer dialog needs to come only once before the batch printing starts). I can do this for word documents (.doc) but not for pdf. All my users have pdf reader in their machines. Any help will be greatly appreciated. Thanks.

  2. #2

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Re: print multiple hyperlinked pdf files from excel vba

    Yes, please. I am able to open and print them without the viewer viewing them if they are Microsoft word documents. But we would like to convert them to pdf files. I am,however able to add the Shell Execute command and get it to open (the pdf document - in the internet) but when I susbsitute the verb 'print' for 'open', it doesn't do anything.
    i.e. ShellExecute 0, "print", sFIle, vbNullString, vbNullString, 0 or
    ShellExecute 0&, "print", cAction, "", "", 2 where sFIle and cAction are the full path of the file starting with "http.....pdf".
    I have declared the Shell execute function though.

  4. #4

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Re: print multiple hyperlinked pdf files from excel vba

    I am placing all these codes in the VB project within the workbook. Another thing I would like to get around is for the entire Excel Application to close when the user closes the workbook. It closes the workbook but the Excel app remains open. I have added the Application.Quit, savechanges = false but nothing seems to work.

  5. #5

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Re: print multiple hyperlinked pdf files from excel vba

    thanks.

  7. #7
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: print multiple hyperlinked pdf files from excel vba

    Welcome to the forums

    See if this helps?

    http://www.vb-helper.com/howto_print_pdf.html

    The code is in vb6. Try and amend it for vba
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,096

    Re: print multiple hyperlinked pdf files from excel vba

    assuming you are using a pdf printer driver, which one?
    does it display a dialog for each?
    is the pdf driver your default printer?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Re: print multiple hyperlinked pdf files from excel vba

    To westconn: I do not understand about the pdf print drive; I am trying to print it using our regular print method. Do I have to invoke a pdf printer?
    The users eventually would need to print all these in our network drive printer, except the locations vary and hence the need to bring up the dialog box to change default printer. thnx.

  10. #10

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Re: print multiple hyperlinked pdf files from excel vba

    To koolsid: I have looked at this example but this code does not work. Believe me, I have searched the web for the past week or so and have tried everything. I have referenced all available Adobe libraries, Shell libraries and whatnots (being a newbee). But I am stumped as to how the code works to open the pdf document in the internet but is not printing. And I have not been able to invoke the printer dialog box with pdf documents at all. The dialog box comes each time durning printing word docs; even then, if I decide to stick to word docs, how do I get the user to choose one printer in the beginning before it does the printing of various documents, retains the selected printer name and prints all the documents in the same printer. Even though they print multiple files, they should see the print dialog box only once, in the beginning. Thanks.

  11. #11
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: print multiple hyperlinked pdf files from excel vba

    To koolsid: I have looked at this example but this code does not work.
    1) Do you have VB6?
    2) Can you explain what do you mean that this code does not work.?
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  12. #12

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Re: print multiple hyperlinked pdf files from excel vba

    It gives me a compile error as the App variable not defined. Also, my PDF links are not in an array. They are all in consecutive cells in a column. How do I save them in an array? It has been a decade since I coded in vb (except simple vb) and hence I am lost.
    thanks.

  13. #13

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Re: print multiple hyperlinked pdf files from excel vba

    to koolsid: also, I have vb 6.5 in my machine. thnx.

  14. #14

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Question Re: print multiple hyperlinked pdf files from excel vba

    the code:
    ShellExecute 0, "open", sFile, vbNullString, vbNullString, 0
    where sFile is the full path of the pdf is working
    sFile = "http://inside.xxx.com/pr_office/pr_tools/xxx.pdf"

    This opens up the hyperlinked document but when I do:
    ShellExecute 0, "print", sFIle, vbNullString, vbNullString, 0
    it does absolutely nothing.

    from what I have read, it should work, correct me if I am wrong.
    Thanks.:

  15. #15

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Question Re: print multiple hyperlinked pdf files from excel vba

    oops. i had a duplicate entry here (same subject matter as the one above).

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.