Solved: Creating success variable from FollowHyperlink

[Provoite]

I'd just like to start of for thanking everyone for all the other threads that have been answered. I just picked up VBA this week and finished my first macro this week as well. I couldn't have done it without the answers that have already been posted.

I've nailed down the basic operation of my macro, which is to use FollowHyperlink in a loop to go to a list of websites. Now i'm trying to create a section that responds to the outcome of FollowHyperlink i.e. if a site that was on the list doesn't actually exist or some other error was present I perform some function. Is there an easy way to do this?

And one more small question. Is there a site that explains the basic theory of VBA programming. All the sites i've found explain things that i already know like do loops and if statements, but i haven't found any that explain the fundementals of objects, events, methods etc.

[Oorang]

Easy? Not really. But here is quick and dirty way to do it:Option Explicit
Sub Test()
If ConfirmHyperlink("http://www.iswbr.com/") = True Then
MsgBox "URL is good.", vbInformation + vbMsgBoxSetForeground
Else
MsgBox "URL is not good.", vbExclamation + vbMsgBoxSetForeground
End If
End Sub
Function ConfirmHyperlink(sURL As String, Optional bVisible As Boolean = True) As Boolean
Dim oIE As Object
Const READYSTATE_COMPLETE As Long = 4
Set oIE = VBA.CreateObject("InternetExplorer.Application")
oIE.Visible = bVisible
oIE.Navigate sURL
Do: Loop Until oIE.ReadyState = READYSTATE_COMPLETE
ConfirmHyperlink = (oIE.Document.Title <> "Internet Explorer cannot display the webpage")
If Not bVisible Then oIE.Quit
End Function

[Provoite]

Your code looks good and i understand the basic concept of what's going on. When i try to run it it gives me an error. The first was within IE that says "Permission Denied". I tried fiddling around with some settings in IE options but i couldn't get rid of that error. I'm assuming there's some option that allows outside programs to control IE that i can't find.

There was also some unexpected behavior, a tab appeared in my current IE window with the first site in my list and another window with a new tab appeared that said "connecting...". I figure there must be something that in my code that's causing it. This is what i have.


Private Sub Test_Click()
Dim i As Integer
Dim answer As Integer
i = 2
Do While Sheets("Sheet2").Cells(i, "C").Value <> ""
Sheets("Sheet2").Select
If ConfirmHyperlink(Cells(i, "C").Value) = True Then
Cells(i, "D").Value = "Done"
Else
Cells(i, "D").Value = "Error"
End If
i = i + 1
'Stops every ten URLs Continues on Yes
If IsInteger((i - 1) / 10) Then _
answer = MsgBox("Do you want to continue ?", vbYesNo + vbQuestion, "PBX Nuke Status")
If answer = vbNo Then Exit Sub
Loop
End Sub

[Oorang]

Hmm not sure about permission denied. But the creating a tab within the current instance behaviour usually only happens when you use GetObject instead of Create Object.

[stanl]

IMHO - :think: - Using IE can get into some serious timing issues; why not use Microsoft.XMLHTTP and analyze the response(s). Stan

[Oorang]

Example Code?

[stanl]

Example Code?

sorry for the late response, something like



Function ConfirmHyperlink(sURL As String) As Boolean
oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
oXMLHTTP.Open "GET" ,cURL, False
oXMLHTTP.Send ""
If (oXMLHTTP.statusText = "OK") Then
ConfirmHyperlink=True
Else
ConfirmHyperlink=False
End If
oXMLHTTP = Nothing
End Function


Stan

[Oorang]

Hey that it pretty sweet. I learned something:) I didn't know that library exposed itself to COM. Not to nitpick but your example code wouldn't run at first. But it was more than enough to point the way. After I got digging around though, I wonder if a HEAD request would run faster then a GET? And shouldn't the routine verify the request is complete?

Function ConfirmHyperlink(sURL As String) As Boolean
'Requires Reference to Microsoft XML
On Error GoTo Exit_Function
Dim oXMLHTTP As MSXML2.XMLHTTP
Const HEAD As String = "HEAD"
Const COMPLETED As Long = 4
Set oXMLHTTP = New MSXML2.XMLHTTP
oXMLHTTP.Open HEAD, sURL, False
oXMLHTTP.send
Do: Loop Until oXMLHTTP.readyState = COMPLETED
ConfirmHyperlink = True
Exit_Function:
Set oXMLHTTP = Nothing
End Function

[Provoite]

Hey thanks guys. It looks like it works. I didn't understand a lot of what you said but i'm figuring it out slowly but surely.