problems using IE with VBA

[KarinaE]

Hi forum, I hope this is the right place for this post. I am trying to use IE with VBA and need the following to happen. If a page does not exists it should give me an error or some boolean value False. First I used the following code in my program which did exactly this: ThisWorkbook.FollowHyperlink url This opened the default browser (FF) and gave an error when the page does not exist. I need to do some more stuff later in the program, so I want to use IE for simplicity Set ie = CreateObject("InternetExplorer.Application") ie.navigate url ie.visible = True However, if url does not exists it still loads en error page in IE. Is there a way to do this properly, meaning that IE doesnt even open when the page does not exist? Thanks for your help guys.

[JP2112]

The way I do it is using MSXML/XMLHTTP. If the HTTP Status is 200, then the page loaded (see HTTP Status Code Definitions (http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html) for a full list of status codes).

Just call this function with your URL and if it returns True, the URL should be valid.


Function IsValidURL(URL As String) As Boolean
Dim result As String
Dim xml As Object ' MSXML2.XMLHTTP60
Set xml = GetMSXML
If xml Is Nothing Then
Exit Function
End If
With xml
.Open "GET", URL, False
.send
End With
IsValidURL = (xml.Status = 200)
End Function
Function GetMSXML() As Object ' MSXML2.XMLHTTP60
On Error Resume Next
Set GetMSXML = CreateObject("MSXML2.XMLHTTP.6.0")
End Function

[KarinaE]

Thank you so much!

[JP2112]

I failed to mention that the code requires the file MSXML6.DLL in your local system32 folder.

Since it's late bound, you can use whatever version of MSXML you like. Just change "6.0" to the version (2.0, 3.0, etc).

[Annagyijjk]

Well this is a good site