UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Link Checker Function Not Working, Office 2013    
 
   
EllenMarie
post Apr 30 2019, 08:50 AM
Post#1



Posts: 205
Joined: 4-August 10



The function below that checks for broken links should be determining if a cell in a selected cell contains a valid URL. When I call the function from GetURL subroutine, the function does not seem to be recognizing good URLs (i.e. www.google.com). The problem seems to occur when Excel executes the request.Open method.


CODE
Public Function IsURLGood(URL As String) As Boolean
    ' Test the URL to see if it is good
    Dim request As New WinHttpRequest

    On Error GoTo IsURLGoodError
    request.Open "GET", URL
    request.Send
    If request.Status = 200 Then
        IsURLGood = True
    Else
        IsURLGood = False
    End If
    Exit Function
IsURLGoodError:
        IsURLGood = False
End Function



CODE
Sub GetURL()
  MsgBox ActiveCell.Value
  MsgBox IsURLGood(ActiveCell.Value)
End Sub

Go to the top of the page
 
Doug Steele
post Apr 30 2019, 09:04 AM
Post#2


UtterAccess VIP
Posts: 22,162
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Works fine for me in Excel 2013, with a reference set to Microsoft WinHTTP Services, version 5.1

In fact, it works for me using Late Binding, thus avoiding having to set the reference:

CODE
Public Function IsURLGood(URL As String) As Boolean
    ' Test the URL to see if it is good
    Dim request As Object

    On Error GoTo IsURLGoodError
    Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
    request.Open "GET", URL
    request.Send
    If request.Status = 200 Then
        IsURLGood = True
    Else
        IsURLGood = False
    End If
    Exit Function
IsURLGoodError:
        IsURLGood = False
End Function




--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
EllenMarie
post Apr 30 2019, 10:53 AM
Post#3



Posts: 205
Joined: 4-August 10



thanks, Doug. I'm getting some false positives. For instance I get a true for https://www.fda.gov/system/404
Go to the top of the page
 
cheekybuddha
post Apr 30 2019, 11:54 AM
Post#4


UtterAccess VIP
Posts: 11,292
Joined: 6-December 03
From: Telegraph Hill


I think the function is working OK.

The URL you provided is where fda.gov redirects when a 404 occurs, but the actual 404 page loads properly (200). dazed.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
EllenMarie
post Apr 30 2019, 12:23 PM
Post#5



Posts: 205
Joined: 4-August 10



yes. I thought of that. so the function works after all.
Go to the top of the page
 
cheekybuddha
post Apr 30 2019, 12:38 PM
Post#6


UtterAccess VIP
Posts: 11,292
Joined: 6-December 03
From: Telegraph Hill


You could try with:
CODE
?IsURLGood("https://www.fda.gov/this/wont/exist/")

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th June 2019 - 02:12 PM