×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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=124031...


Also I am using Excel 2016 on windows 7.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
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

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

I avoid using GoTo in my code...

CODE

Private Sub CommandButton1_Click()
   PrintSelectedWebsites
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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
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?

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)

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

Put a BREAK in PrintSelectedWebsites on statement For Exch c...

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
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)."

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

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



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

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

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

In exactly what way is it “not working?”

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

My 2¢:
  • 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,
  • 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.

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close