Error in Printing links from IE

Purpose: To print 50 hyperlinks internal webpages.
Hyperlinks are stored in Excel sheet (Sheet1)
Links are to be opened using IE 8 and giving focus on screen for user a prompt to print before going to next link



Problem: Currently it opens the 1st 5 pages and than gives error.


Error: Object required-

http://www.cnn.com

424 1
Object required-

http://www.yahoo.com

424 1
Object required-

http://www.msn.com

424 1
Object required-

http://www.cnn.com

424 1

Links:

http://www.google.com

http://www.yahoo.com

http://www.msn.com

http://www.cnn.com

There are 50 hyperlink stored in excel file starting from A2
sCol and iRow is to provide column and row for the hyperlink

Call PrntHyperLink("A", 2)

Code:

Public Sub PrntHyperLink(sCol As String, iRow As Integer)

On Error GoTo ErrPrint

    'DIM oIE as Object
    Dim oIE As Variant
    Dim sPrintLink as String

    Set oIE = New InternetExplorer

    oIE.Visible = True
    
    'To print HyperLink
    For i = 1 To iRowEnd

        iRow = iRow + 1
    
        Range(sCol & iRow).Select
        
        sPrintLink = Range(sCol & iRow).Value
        
        oIE.Navigate sPrintLink
        
        Do Until oIE.ReadyState = READYSTATE_COMPLETE
            wscript.sleep 1000
        Loop
        

        oIE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSE
        

        ' To Exit Printing
        If iRow = 32 Then
            MsgBox iRow, vbOKCancel
            Range(sCol & iRow).Select
            Exit For
        End If
    Next i
    
ErrPrint:
    If iRow = iRowEnd Then
        Exit Sub
    ElseIf Err.Number = 424 Then
        Debug.Print Err.Description & "-" & sPrintLink & " " & Err.Number, vbOKCancel
        Resume Next
    End If
    
End Sub

 

[SkipVought]

hi,

Currently it opens the 1st 5 pages and than gives error.

What happens if you start you loop at the index just BEFORE or just AFTER this error occurred like...

    For i = [b]5[/b] To iRowEnd
'.....

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ken]

Hi SkipVought,

It takes 1st 5 links than goes to error mode.
One of the error is at wscript.sleep 1000 - straight it goes to error and loops.

Tech

 

[PHV]

There is no wscript object instantiated in your code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Replace this:
wscript.sleep 1000
with this:
DoEvents

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886