Printing Hyperlinks in Excel 1

[McWhorter]

We have an Excel sheet that has 100s of hyperlinks that (when clicked) open up in IE6.

I have a checkbox next to each one. I want the users to be able to check several hyperlinks they want to print, then click a command button to print them all (preferably without opening them all up in IE).

I don't know how to print them using VBA.

 

[firefytr]

Hi there,

You are looking to print the browser page that is brought up when the links are clicked? How are the checkboxes created? Are they ActiveX or Forms checkboxes? How are the checkboxes related to the hyperlinks? One per row? Please post as many details about your spreadsheet as possible, along with your XL version.

-----------
Regards,
Zack Barresse

 

[McWhorter]

"You are looking to print the browser page that is brought up when the links are clicked?"

Yes. I'm looking to print them from an Excel VBA script. I would prefer the browser page not even be opened. The links currently open in IE6.

I have messed around with checkboxs (embedded and on a Form). Either of these options will work. The part I can't figure out is how to print the link using IE6 from Excel.

For example, if one of the Hyperlinks were

http://www.tek-tips.com.

I need the code that will print the browser page from excel.

Thanks.
Jeff

 

[firefytr]

I think this may work for you ...

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

Set the range to desired. This assumes that column A (starting at row 2) and going down has a boolean TRUE/FALSE value in it (could be from a linked ActiveX checkbox) and the corresponding website in column B of the same row.

HTH

-----------
Regards,
Zack Barresse

 

[firefytr]

Oh, and btw, you need to set a reference (VBE | Tools | References) to Microsoft Internet Controls.

-----------
Regards,
Zack Barresse

 

[McWhorter]

Wow... Thanks Zack. That is exactly what I need. Works like a charm....

 

[firefytr]

Great, glad it works for you. Remember, the IE instances are still there, just not visible. That's why it may take a little longer to run as it still has to navigate to the website listed.

Take care!

-----------
Regards,
Zack Barresse