Excel URL Check Help

[udt123]

Hey Guys,

I have been searching for the last 2 days and couldn't find anything so I wanted to see if anybody knew how to go about doing this on the mrexcel forums.

I have a list of URLS in my column A of my excel 2007 workbook and I wanted a macro that can

1)Copy the first URL
2)Paste it into the webbrowser (mozilla or IE)
3)scan the website for the words "product not found"
4)If product is not found, to put an X next to the URL.
5)Repeat until all the URLS in the column are checked

Is this possble? Thanks for the help in advance!

 

[AlphaFrog]

Do you have some example URLs as they appear in column A of both "product not found" and found?

Are the URLs in column A already recognized by Excel as hyperlinks to URLs? In other words, can you click on the URLs and link to the web page.

 

[udt123]

Hey AlphaFrog,

Thanks for the reply!

An example of a nonworking link:

http://search.totalhockey.com/search?dom=totalhockey.com&w=bauer supreme one 05

When this link gets copied into a web browser,there is a line on the page that says

"There were no documents that contained all of the words in your query. These results contain some of the words."

An example of a working link:

<TABLE style="WIDTH: 203pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=271><COLGROUP><COL style="WIDTH: 203pt; mso-width-source: userset; mso-width-alt: 9910" width=271><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 203pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=271>http://search.totalhockey.com/search?dom=totalhockey.com&w=bauer roller blades</TD></TR></TBODY></TABLE>

They are not recognized as hyperlinks at the moment.

Thanks for the help Alpha.

 

[AlphaFrog]

Sub Check_URLs()

    Dim cell As Range
    Dim IE As Object
    
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True

    For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        
        If Left(cell.Value, 4) = "http" Then
            
            ' Goto web page
            IE.Navigate cell.Text
            
            Application.StatusBar = cell.Text & "  is loading. Please wait..."
            
            ' Wait until web page is loaded
            Do Until IE.ReadyState = 4 'READYSTATE_COMPLETE
            Loop
            
            Application.StatusBar = ""
            
            ' Look for text on web page
            If InStr(IE.Document.body.innerText, _
               "There were no documents that contained all of the words in your query.") > 0 Then
               
               cell.Offset(, 1).Value = "X"
               
            End If
        End If
    Next cell
    
    IE.Quit
    Set IE = Nothing
    
End Sub

 

[udt123]

Wow thank you very much AlphaFrog! That worked perfectly. What if there was another term I wanted to search for? For example "doesn't exist today" or something, how do I add that into the macro?

Also, where did you learn how to program vba so well? Was it from taking courses or did you just learn by programing things yourself? Again, Thank for the help!

 

[AlphaFrog]

I'm glad it works for you.

To search another term, change the text in red...

            If InStr(IE.Document.body.innerText, _
               [COLOR="Red"]"There were no documents that contained all of the words in your query."[/COLOR]) > 0 Then

I already had a rudimentary understanding of Visual Basic. I learned VBA mostly by doing web searches for example code e.g "Excel VBA Internet Explorer"

 

[udt123]

Thanks for the help Alpha but I am getting an error on random terms =(

I am getting the Run-time error '91' with the If statement highlighted in yellow.

It happens randomly. Would you know what the problem is?


So you basically self-learned VBA? I tried learning on my own but it is very hard =(

 

[AlphaFrog]

Perhaps showing me the terms that error and some example URLs would be best instead of me guessing at all the infinite possibilities?

 

[udt123]

Hey Alpha,

Sorry for not sending you the links earlier. I was testing it out and It works now, it was just random times where it didn't. The urls were the same and sometimes it would work and sometimes it wouldnt. For example,

http://search.totalhockey.com/search?dom=totalhockey.com&w=apparel hat

Sometimes it would stop on that url and then when I cut it out of the bunch and make the selection smaller, it would work.

Again, Thanks for all your help!

 

[udt123]

Hey Alpha,

So it's weird and I can't pinpoint why it's happening. I have been running it for about an hour and it sometimes happens where as other times it doesnt. It can error on the same URL that went through a minute ago. For example:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=64>http://search.totalhockey.com/search?dom=totalhockey.com&w=nike+forearm+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=nike+goalie+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=orange+goalie+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=performance+goalie+chest+&+arms</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=pro+goalie+chest+&+arms</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=rbk+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=reflex+rx6+goalie+leg+pad</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=rx10+re-flex+pro+goalie+leg+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=rx8+re-flex+goalie+leg+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=senior+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=senior+roller+hockey+goalie+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=sherwood+goalie+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=sizing+goalie+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=tour+fireman+youth+series+roller+hockey+goalie+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=vapor+goalie+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=vapor+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=youth+pads</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=bauer+goalie+pants</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=bauer+supreme+hockey+pants</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=junior+goalie+pants</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=nike+youth+pants</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=pro+goalie+pants</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=bauer+1000+goalie+skates</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=bauer+1000+goalie+skates+junior</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=bauer+1000+goalie+skates+senior</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=bauer+one95+goalie+skates</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=brians+goalie+gear</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>http://search.totalhockey.com/search?dom=totalhockey.com&w=custom+hockey+goalie+mask</TD></TR></TBODY></TABLE>

If I ran these 10 times, 2 outta 10 times it wouldn't work whereas the other 8 it would run perfectly. Any ideas if you are free?

Thanks!