Printing Many Hyperlinked PDFs in Order from an ActiveX Button - HELP

[jmunn]

Hello,

I found an awesome thread on this forum that references almost exactly what I am trying to accomplish. However, I am having difficulty setting up the code language properly.

I have a pricing spread that has three subcontractors per row and about 50 rows. Each row's subcontractors have a PDF proposal which I have uploaded to Sync Synergy and created a unique hyperlink to (the PDF opens in IE). I have placed a "Form Control" check box with an assigned cell to toggle "TRUE/FALSE" and hyperlink adjacent to my sub spread form. The goal is to use an "ActiveX button" to print the the range of hyperlinks next to the "checked boxes (TRUE)" through IE preferably without opening a window for every link.

The thread I read here shows a great way to do it but I am having trouble getting the whole thing set up correctly, can someone please give me some pointers? Here is a link to the original thread,

https://www.tek-tips.com/viewthread.cfm?qid=1240311

Also I am using Excel 2016 on windows 7.

 

[SkipVought]

Hi,

What code do you currently have with reference to your own workbook/sheet?

Where has your code failed?

What was the error message(s)?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jmunn]

Hi Skip,

The original code I was trying to work with is this one, see below. The problem I have is where to paste in the code for the button. A button code starts with "Private Sub CommandButton1_Click()" and when I paste in the code between this and "End Sub" I get an error message at the "Private Sub CommandButton1_Click()" line, "Compile Error: Invalid Procedure." Also when I try to paste in this code without a button the first five lines get delineated as a separate code and the "Sub PrintSelectedWebsites()" starts a new code.

I am familiar with formulas and data manipulation in excel but I am super new to VBA. I think this code I'm trying to use is for an older version of word. Let me send you the template I am trying to master this on so you can see my work space. Ideally I could adapt a code to be used on any range or sheet. Also the macro could either print from a hyper link or print from a folder. the folder in some ways would be much faster to configure. Either way its turning into a useful spread sheet tool and the extra bells and whistles would make it pro.

Code:

Option Explicit

Private Const OLECMDID_PRINT As Long = 6
Private Const OLECMDEXECOPT_DONTPROMPTUSER As Long = 2
Private Const READYSTATE_COMPLETE As Long = 4

Sub PrintSelectedWebsites()
Dim IE As InternetExplorer, c As Range
For Each c In Sheets("Sheet1").Range("A2:B7") 'Set range to desired
If IsEmpty(c.Offset(0, 1).Value) Then GoTo SkipIE
If c.Value = "FALSE" Then GoTo SkipIE
Set IE = New InternetExplorer
IE.Navigate c.Offset(0, 1).Value
Do
Loop Until IE.ReadyState = READYSTATE_COMPLETE
IE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER
IE.Quit
Set IE = Nothing
SkipIE:
Next c
End Sub

 

[SkipVought]

I avoid using GoTo in my code...

Private Sub CommandButton1_Click()
   [b]PrintSelectedWebsites[/b]
End Sub

Sub PrintSelectedWebsites()
   Dim IE As InternetExplorer, c As Range
   For Each c In Sheets("Sheet1").Range("A2:B7") 'Set range to desired
      If Not IsEmpty(c.Offset(0, 1).Value) Then 
         If c.Value Then 
            Set IE = New InternetExplorer
            IE.Navigate c.Offset(0, 1).Value
            Do
               DoEvents
            Loop Until IE.ReadyState = READYSTATE_COMPLETE
            IE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER
            IE.Quit
            Set IE = Nothing
         End If
      End If
   Next c
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jmunn]

I don't think I am entering it into the right place. I selected the command button 42, then view code, then entered the code above, changed only the number of the button, the sheet reference, the range and the column the code is looking up. and its not working, what am i missing?

 

[SkipVought]

Right-click on the sheet TAB of the sheet that contains your table and Forms Control Button. Then select View Code.

Please COPY ‘n’ PASTE here, what’s in the code window.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jmunn]

 

[SkipVought]

Put a BREAK in PrintSelectedWebsites on statement [tt]For Exch c...[/tt]

Then RUN again. When your code pauses on BREAK, use the STEP icon to observe how your code behaves.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jmunn]

Okay a few basics, is the "c" referring to the column that is to be looked up? or is it just part of the language? The column I have the hyperlink in is "j." Also for the sheet, am I writing in "subcontracts" or "Sheet15 (Subcontracts)."

 

[SkipVought]

is the "c" referring to the column that is to be looked up?

No, c is declared as a Range object and in your code represents a CELL in the range of A2:B7.

I’d be looping through I2:I7 looking for TRUE. And then c.Offset(0, 1).Value to open that link.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jmunn]

Its running with no error but it is not sending the link to the IE or printing it.

 

[jmunn]

I changed the range back to I6:J46 that was a typo. Still not working

 

[SkipVought]

In exactly what way is it “not working?”

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jmunn]

Skip,

Not trying to be a pain. The code runs with no errors and does not highlight any lines but it does not print the hyperlinks located next to the range.

 

[SkipVought]

Well lets try to determine what’s happening.

This statement, it seems to me, should OPEN the .pdf. Is that what happens? If not, then a different command must be required.

Put a BREAK in your code so it stops just after this statement executes.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

My 2¢:
[ul][li]make sure that constants declared for IE (OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER, READYSTATE_COMPLETE) are not out of scope. If you can't get their values in PrintSelectedWebsites(), either declare them as public or move to the sheet's module where they are used,[/li]
[li]understand the code you write. The TRUE/FALSE switches are in column I, links in column J. So you search in column I (I6:I46) and get link from c.Offset(0, 1). No need to search in J, as Skip pointed.[/li]
[/ul]

combo