• 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...

  • 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...

Brian S

New Member
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
 
Hi Brian,
I found this code by Siddharth Rout on MSDN:
Code:
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
 
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:
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
 
I see Stevie's come up with a solution, so a tweak to Sample to accept a parameter:
Code:
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:
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.
 

Attachments

  • Chandoo39471test workbook.xlsm
    17.6 KB · Views: 6
Last edited:
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?
 
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?
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.
 
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.
 
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:
Hi both,
I had a play, and this opens up the print dialogue to the default printer for me:
Code:
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
 
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.
 
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:
[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]
 
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:
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?
 
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...
 
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
 
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...
 
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...
 
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?
 
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
 
Back
Top