Search HTML source code for text

[davegugg]

Hi folks.

I need to check a website daily to see if a link has been updated. If it has been updated, the beginning of the link changes to a different date. Example: today link is www.10212009dave.com and tomorrow link may be www.10222009dave.com. Lets say the link is on www.gugg.com. The link does not change everyday, but I think a good way to see if it has been updated is to search through the source code in the html for that link.

Thus I would put www.10212009dave.com into cell A1 and tell excel to search the source code on www.gugg.com, and if the contents of cell A1 is NOT found, I'd display a message box stating the link has been updated.

Could anyone help me find a way to do this?

OR

Can anyone suggest a better way to check if the link has been updated?

Thanks!

[Leith Ross]

Hello Dave,

This macro will tell you if the link has been updated using the contents of cell "A1". You can change the site to want you want. It is in red font.

Sub CheckLink()

  Dim IEapp As Object
  Dim IEdoc As Object
  Dim Href As String
  Dim Res As Variant
  Dim Site As String
  
    Site = "www.google.com"
    Href = Range("A1")
    
    Set IEapp = CreateObject("InternetExplorer.Application")
    IEapp.Navigate Site
    
      While IEapp.Busy
        DoEvents
      Wend
      
      Set IEdoc = IEapp.Document
      
      For Each Item In IEdoc.Links()
        Res = InStr(1, Item.outerhtml, Href)
        If Res Then
           MsgBox "Link has Not Changed"
           GoTo Finish
        End If
      Next Item
      
      MsgBox "Link has been Updated."
      
Finish:
    Set IEapp = Nothing
    Set IEdoc = Nothing
    
End Sub

[davegugg]

That seems like what I need, but it is not working. I tried it with the site I am accessing, and also with google and neither worked. I'll post a link of the google example. Not sure if it matters, but IEdoc is set to "[Object]" and Res ends up being 0 each time.

[Leith Ross]

Hello Dave,

The workbok now contains 2 links. One in "A1" and another in "A2". I checked "A1" and it is no longer on the Google page. The link in "A2" is valid. I added a button to call the macro. When the macro is run from the VBIDE, the message box appears while Internet Explorer is active. The message box appears behind Excel while Internet Explorer is active. The message box now is displayed after IE is closed. Here is the updated code. These changes have been made to the attached workbook,

Sub CheckLink()

  Dim IEapp As Object
  Dim IEdoc As Object
  Dim Href As String
  Dim Msg As String
  Dim Res As Variant
  Dim Site As String
  
    Site = "www.google.com"
    Href = Range("A2")
    
    Set IEapp = CreateObject("InternetExplorer.Application")
    IEapp.Navigate Site
    
      While IEapp.Busy
        DoEvents
      Wend
      
      Set IEdoc = IEapp.Document
      
      For Each Item In IEdoc.Links()
        Res = InStr(1, Item.outerhtml, Href)
        If Res Then
           Msg = "Link has Not Changed"
           GoTo Finish
        End If
      Next Item
      
      Msg = "Link has been Updated."
      
Finish:
    IEapp.Quit
    Set IEapp = Nothing
    Set IEdoc = Nothing
    MsgBox Msg
    
End Sub

[davegugg]

Does it matter that the link I'm searching for ends in .pdf? Try it with http://www.mgic.com/pdfs/71-6799_sifi_lpmi_oct09.pdf in A2 and http://www.mgic.com/rates/ratecards.html as the site being searched.

Example below:

[Leith Ross]

Hello Dave,

If the link address exists with in the web page HTML source code, the macro will find it. I have checked the page source code for the link the you provided and it doesn't exist.

[davegugg]

You are right. I searched through the source code manually and found instead of writing out http://www.mgic.com/pdfs/71-6799_sifi_lpmi_oct09.pdf, it just has ../pdfs/71-6799_sifi_lpmi_oct09.pfd.

I can make that work, I'll just have to look at the source code every time it changes to find the correct path. Since it only changes about twice a year, that shouldn't be a problem.

Thanks Leith you've been very helpful.
Props

[davegugg]

Leith, as discussed:

[Leith Ross]

Hello Dave,

I have run it several times and the results are consistent, even with searches being done with the wireless network running. My system is using Explorer 6.0, perhaps it is a version difference causing the problem.

[davegugg]

Ok, thanks so much for your help.