1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

There's gotta be a way...

Discussion in 'VBA Macros' started by Brian S, Aug 13, 2018.

  1. Brian S

    Brian S New Member

    Messages:
    12
    I've found a TON of great posts on here so I thought I'd see if anyone here can figure this one out...
    I have a userform created that has a dropdown box. This is a list of products that are defined on sheet 1 as "SKU". Sheet 1's sku numbers are hyperlinks to an online PDF form. When the user clicks "Print" on the userform, I need it to find that specific sku and print the PDF that results from the hyperlink.
    I would prefer that the user never sees the sheet, it only opens the userform. I would also prefer the user never actually see the PDF sheet, it simply prints to their default printer.

    Am I chasing rainbows here? No one has found an answer for me as yet.

    Would help to attach a sample sheet. The hyperlinks don't go to a PDF but same concept
    https://www.dropbox.com/s/3sp4uupk7jsxjqx/test%20workbook.xlsm?dl=0
  2. Stevie

    Stevie Active Member

    Messages:
    116
    Hi Brian,
    I found this code by Siddharth Rout on MSDN:
    Code (vb):
    Option Explicit

    Const OLECMDID_PRINT = 6
    Const OLECMDEXECOPT_DONTPROMPTUSER = 1
    Const PRINT_WAITFORCOMPLETION = 2
    Sub Sample()
    Dim objIE
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Navigate "http://www.Google.com/"
    objIE.Visible = 1
    Do While objIE.ReadyState <> 4
      DoEvents
    Loop
    objIE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER
    End Sub
    It looks like it should be easy enough to adapt for your purposes, so I would start there.

    Link to the post:
    https://social.msdn.microsoft.com/F...90e169b89/print-an-ie-web-page?forum=exceldev

    If this was helpful, please click 'Like'!
    Stevie
  3. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    I don't know how to print the pdf form (how is it displayed? If it's online, will it be in a browser? Do you know which browser?) but the following may be some use.
    Choose any one or more of the commented-out lines:
    Code (vb):
    Private Sub CommandButton2_Click()
    'Application.Goto Range("SKU").Cells(ComboBox2.ListIndex + 1) 'selects that cell on the sheet.
    'Range("SKU").Cells(ComboBox2.ListIndex + 1).Hyperlinks(1).Follow 'follows the hyperlink in that cell.
    'Debug.Print Range("SKU").Cells(ComboBox2.ListIndex + 1).Hyperlinks(1).Address 'displays the hyperlink address in the Immediate pane.
    End Sub
  4. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    I see Stevie's come up with a solution, so a tweak to Sample to accept a parameter:
    Code (vb):
    Const OLECMDID_PRINT = 6
    Const OLECMDEXECOPT_DONTPROMPTUSER = 1
    Const PRINT_WAITFORCOMPLETION = 2

    Sub Sample(addr As String)
    Dim objIE
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Navigate addr
    objIE.Visible = 1
    Do While objIE.ReadyState <> 4
      DoEvents
    Loop
    objIE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER
    End Sub
    and in your userform button_click:
    Code (vb):
    Private Sub CommandButton2_Click()
    Sample Range("SKU").Cells(ComboBox2.ListIndex + 1).Hyperlinks(1).Address
    End Sub
    Regarding the user not seeing the pdf file, research this line:
    objIE.Visible = 1
    and see if another value makes it invisible (you might only have to miss that line out altogether) BUT, make sure you Quit the browser application with the likes of:
    objIE.Quit
    at the end of the Sample sub, otherwise you'll end up with a bunch of invisible IE browsers left open. I haven't done this in the attached.

    No checks for missing hyperlinks, nothing selected in the combobox etc., I leave that to you.

    Attached Files:

    Last edited: Aug 13, 2018
    Stevie likes this.
  5. Brian S

    Brian S New Member

    Messages:
    12
    Closest one so far! That takes me to the page that contains the PDF. Those links open up in Explorer. The actual link is to a PDF displayed within explorer.
    When I click on the link in the actual spreadsheet, it immediately opens the PDF (not just the page that has the link to the PDF). When I run the code that you've provided, it takes me to the page. The user would then have to select the appropriate link and manually print the PDF that's displayed.
    Any ideas?
  6. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    Apart from an update to msg#4 above, could you supply a workbook with a sample of one or two hyperlinks so we can examine them in situ?
    Do you know of any such sites/hyperlinks available to the general public which behave in the same way as your hyperlinks do?

    It might also help to record a macro of you clicking on one of the hyperlinks and post the result here.
  7. Brian S

    Brian S New Member

    Messages:
    12
    I've attached another copy of the sample workbook. The new link is for product "1". It goes directly to a publicly available PDF that opens in much the same manner as my original.
  8. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    Can't see the attachment.
    Yes I can, it's at the original link. Just a mo…
  9. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    When I click on the cell A8 it opens the pdf document in my default browser, Firefox, as a pdf document.
    When I use Stevie's code, it starts Internet Explorer on my machine, but the process gets interrupted by a dialogue box to View and Track my downloads, when I say Open, I get another dialogue box A website wants to open web content using this program on your computer and it cites TWINUI and because MS Edge is also installed it opens the pdf file in that. When I get back to Excel I get another vba debug error dialogue box: The object invoked has disconnected from its clients.

    I'm guessing my default application for pdf files is MS Edge and that may need changing, but I'm not going to do it on my computer.

    Perhaps Stevie knows of some other code?
    Last edited: Aug 13, 2018
  10. Stevie

    Stevie Active Member

    Messages:
    116
    Hi both,
    I had a play, and this opens up the print dialogue to the default printer for me:
    Code (vb):
    Const OLECMDID_PRINT = 6
    Const OLECMDEXECOPT_DONTPROMPTUSER = 2
    Sub Sample()
    Dim addr As String
    Dim objIE
    addr = "https://www.adobe.com/support/products/enterprise/knowledgecenter/media/c4611_sample_explain.pdf"
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Navigate addr
    objIE.Visible = FALSE
    Do While objIE.ReadyState <> 4
      DoEvents
    Loop
    objIE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER
    objIE.Quit
    End Sub
    I added my own PDF link to the sub for security, but using p45cal's example, you can easily connect this to your own PDFs using the userform and the links on the page.
    I didn't have much luck in printing without showing the dialogue, I hypothesise that this is down to the security settings on my machine.
    It makes sense, as otherwise a webpage could auto print documents, which wouldn't be best.
    You may be able to change these settings if you want to, I would suggest looking up the OLECMDEXECOPT_DONTPROMPTUSER proprty on MSDN or similar.

    Hopefully this gets you closer to your goal.

    If this was helpful, please click 'Like'!

    Stevie
    Chirag R Raval likes this.
  11. Brian S

    Brian S New Member

    Messages:
    12
    Stevie
    Where would I place the code that you just showed? The link that it's supposed to follow should be whatever hyperlink is attached to the part number referenced in the combobox (ComboBox finds the part number on sheet 1, that part number is hyperlinked to a specific online PDF). Would this mean that I'd need to create the hyperlink in the coding for each and every part number that requires it?

    But regardless, there will be plenty of likes, you two have helped me a LOT so far, I'm a lot closer than I have been for some time.
  12. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    FYI, I tried that too and got the same dialogue boxes and palaver with TWINUI and MS Edge as before.
  13. Brian S

    Brian S New Member

    Messages:
    12
    I did some more searching and found something. Problem is, I'm not this adept at VBA, so the code doesn't really make a lot of sense (plus I believe it's only looking at local files instead of hyperlinked on the web). What do you two think?
    Code (vb):
    [Code]—–Copy this code and place in a new module

    Public 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

    Sub sbPrintPDFs()
    Dim tempPrint
    Dim strFile As String

    strFile = “C:\Test.pdf” ‘ Your PDF File Path
    To pen a PDF
    ‘tempPrint = fnOpenPDF(0, strFile)

    To Print PDF
    tempPrint = fnPrintPDF(0, strFile)
    End Sub

    Public Function fnPrintPDF(lngHw As Long, strFileName As String)
    On Error Resume Next
    Dim X As Long
    X = ShellExecute(lngHw, “Print”, strFileName, 0&, 0&, 3)
    End Function

    Public Function fnOpenPDF(lngHw As Long, strFileName As String)
    On Error Resume Next
    Dim X As Long
    X = ShellExecute(lngHw, “Open”, strFileName, 0&, 0&, 3)
    End Function

    And I am not sure about printing only specific pages![/CODE]
  14. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    Some of the quote marks are wrong for VBA as are the line continuations. Trying this (opening) out on a local file results in the file being opened in MS Edge, no question asked. On an online file it opened pdf document in my default browser.
    Code (vb):
    Public 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

    Sub sbPrintPDFs()
    Dim tempPrint
    Dim strFile As String

    'strFile = "C:\Test.pdf" ' Your PDF File Path"
    'strFile = "C:\Users\Public\Documents\MES Report.pdf"
    'strFile = "C:\Users\Public\Documents\Book2.pdf"
    strFile = "https://www.adobe.com/support/products/enterprise/knowledgecenter/media/c4611_sample_explain.pdf"
    'To open a PDF
    tempPrint = fnOpenPDF(0, strFile)
    'To Print PDF
    'tempPrint = fnPrintPDF(0, strFile)
    End Sub

    Public Function fnPrintPDF(lngHw As Long, strFileName As String)
    On Error Resume Next
    Dim X As Long
    X = ShellExecute(lngHw, "Print", strFileName, 0&, 0&, 3)
    End Function

    Public Function fnOpenPDF(lngHw As Long, strFileName As String)
    On Error Resume Next
    Dim X As Long
    X = ShellExecute(lngHw, "Open", strFileName, 0&, 0&, 3)
    End Function
    Trying to print did nothing.

    Could you link to where you found this code?
  15. Brian S

    Brian S New Member

    Messages:
    12
  16. Brian S

    Brian S New Member

    Messages:
    12
    So question, this code points to an online pdf, is there a way to make it point to the pdf that is hyperlinked instead of specifying each individual link? I could, most likely do it this way with a lot of "if" statements but I think that would get pretty muddy...
  17. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    Have you managed to use this new code to print it anything?!
    Last edited: Aug 14, 2018
  18. Brian S

    Brian S New Member

    Messages:
    12
    We're getting there! I played around with the code and adapted it to the commandbutton1. I directed the addr = to the specific pdf (cut/pasted the hyperlink from sheet1). It sent the browser directly to the actual pdf. So we're getting closer. Now, how to tell it to direct to whatever part number is selected in the dropdown...
    Also, while it DOES go directly to the PDF (as it should), it throws out an error
    Run-time error '-2147417848 (80010108)'" Automation error
    The object invoked has disconnected from its clients. The debug shows the "Do While objIE.ReadyState <> 4" function
  19. Brian S

    Brian S New Member

    Messages:
    12
  20. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    I did this in the attachment to msg#4
  21. Brian S

    Brian S New Member

    Messages:
    12
    OK, for the most part this is working now. It pulls up the appropriate PDF just like it should, still getting the annoying run-time error. Now to figure out removing the error and printing the thing...
  22. Brian S

    Brian S New Member

    Messages:
    12
    so, hard drive crashed. Lost everything. But with the attachments and the code that y'all have entered, I can pretty easily recreate the wheel. Don't be alarmed by the loud screaming, that was me as soon as the drive crashed!
    Anyway, I was constantly getting the run-time error, downloaded the attachments on my other work computer (running Excel 2013 instead of 2016), and the error vanished. Apparently, it's not an issue with the code, it's an issue with Excel 2016...
    Anyway. The userform opens the associated PDF flawlessly, but the code isn't permitting me to print automatically...
  23. Brian S

    Brian S New Member

    Messages:
    12
    Found this code on StackOverflow. Can this be modified to print the PDF that opens up?
    Option Explicit

    DeclareFunction apiShellExecute Lib"shell32.dll"Alias"ShellExecuteA"( _ByVal hwnd AsLong, _ByVal lpOperation AsString, _ByVal lpFile AsString, _ByVal lpParameters AsString, _ByVal lpDirectory AsString, _ByVal nShowCmd AsLong) _AsLong

    PublicSub PrintFile(ByVal strPathAndFilename AsString)Call apiShellExecute(Application.hwnd,"print", strPathAndFilename, vbNullString, vbNullString,0)EndSub

    Function PrintPDF(PartNum AsString)Dim DirFile AsString
    DirFile ="\\SERVER5\hpfiles\Company\Drawings\PDF-SL8\"& PartNum &".pdf"

    If Dir(DirFile)=""ThenExitFunction

    PrintFile (DirFile)EndFunction

    If so, would I place this in a new module?
  24. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    I can't make that code do anything here.
  25. Stevie

    Stevie Active Member

    Messages:
    116
    Brian, I think it is a good idea to take stock of what parts of the issue still remain.
    Printing from the embedded hyperlinks is trivial, and was well explained and demonstrated by P45CAL.
    Opening the pdf in the default browser is trivial, and has been shown multiple times in this post.
    Doing this in a hidden manner has been demonstrated above.
    Triggering a print has been demonstrated above.
    The only piece of the puzzle remaining is printing without asking for final permission from the user.
    I am unable to help here as I believe that the code I posted will do this, but requires more relaxed security settings.
    The code snippets you have posted from other sites are related to printing a file on your machine, which was not the original query.
    If you are happy with downloading the file and then opening and printing it, this makes the hyperlinks redundant in the first place as you may as well just have a folder with all the pdfs in and print them, or even put the data from the pdfs into word and write some VBA there.

    I would suggest that if you want to go down this route, you should start a new thread and change the request.

    Stevie

Share This Page