Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

check to see if website exists with vba

I have an Excel workbook that pulls data from our intranet.  However, the URLs change and I am wondering if there is a way to loop through the URLS on the worksheet and return to the user an error message if any of them are broken - I want to do this before processing the data as there are 100's of URLs and if one fails, the user has to find it, fix it and start all over again.
Avatar of Christian de Bellefeuille
Christian de Bellefeuille
Flag of Canada image

There's plenty of applications doing this.   Any particular reason why you need to develop something for that?
Avatar of Sandra Smith

ASKER

Because this is a customized VBA program and has to be internal to it.
ASKER CERTIFIED SOLUTION
Avatar of Christian de Bellefeuille
Christian de Bellefeuille
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please note this question is NOT abandoned or forgotten, I got pulled to another project for an emergency, but will return to this question tomorrow!
This does work, next step is to figure out how to loop through the array, but progress!  Thank you for being patient as I know it took a while for me to get back to this question.
Well, not much informations were given about the array.
Let's say that you got your URL's into Column A of Sheet1.  You could have this piece of code to check for the broken links, and set the status for every sites in Column B...

If you can give me more precision, i'll help.

Public Sub VerifyAllUrls()
    Dim sURL As String
    
    For Each cel In Sheet1.UsedRange.Rows.EntireRow
        sURL = cel.Cells(1, 1)
        cel.Cells(1, 2).Value = IIf(GetUrlStatus(sURL) <> 9999, "OK", "BROKEN")
    Next
    Sheet1.Columns.AutoFit
End Sub

Open in new window