Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: check if url exists

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default check if url exists

    Is it possible to have a macro to look urls in colA and respond back in colB if the url "found" or "Not Found / #404"

    Sheet1

    AB
    1http://marketheist.com/2010/09/02/Found
    2http://marketheist.com/2010/01/02/Not Found / #404
    3http://marketheist.com/2010/09/05/Not Found / #404
    4

    Excel 2007



    thanks

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,740
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check if url exists

    Hello congokin,

    Here are two macros for you. The first, a function, returns the status a given URL and the second will check all the URLs you have in column "A" and place the status in column "B". Copy all this code to a new VBA module in your workbook. You can then call the macro "ValidateURLs" to list the status of the URLs on the Active Sheet.
    Code:
    ' Written: March 15, 2011
    ' Updated: April 29, 2012
    ' Author:  Leith Ross
    ' Summary: Returns the status for a URL along with the Page Source HTML text.
    
    Public PageSource As String
    Public httpRequest As Object
    
    Function GetURLStatus(ByVal URL As String, Optional AllowRedirects As Boolean)
    
        Const WinHttpRequestOption_EnableRedirects = 6
      
      
            If httpRequest Is Nothing Then
                On Error Resume Next
                    Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
                    If httpRequest Is Nothing Then
                        Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5")
                    End If
                Err.Clear
                On Error GoTo 0
            End If
    
            ' Control if the URL being queried is allowed to redirect.
              httpRequest.Option(WinHttpRequestOption_EnableRedirects) = AllowRedirects
    
            ' Clear any pervious web page source information
              PageSource = ""
        
            ' Add protocol if missing
              If InStr(1, URL, "://") = 0 Then
                 URL = "http://" & URL
              End If
    
                 ' Launch the HTTP httpRequest synchronously
                   On Error Resume Next
                      httpRequest.Open "GET", URL, False
                      If Err.Number <> 0 Then
                       ' Handle connection errors
                         GetURLStatus = Err.Description
                         Err.Clear
                         Exit Function
                      End If
                   On Error GoTo 0
               
                 ' Send the http httpRequest for server status
                   On Error Resume Next
                      httpRequest.Send
                      httpRequest.WaitForResponse
                      If Err.Number <> 0 Then
                       ' Handle server errors
                         PageSource = "Error"
                         GetURLStatus = Err.Description
                         Err.Clear
                      Else
                       ' Show HTTP response info
                         GetURLStatus = httpRequest.Status & " - " & httpRequest.StatusText
                       ' Save the web page text
                         PageSource = httpRequest.ResponseText
                      End If
                   On Error GoTo 0
                
    End Function
    
    Sub ValidateURLs()
    
        Dim Cell As Range
        Dim Rng As Range
        Dim RngEnd As Range
        Dim Status As String
        Dim Wks As Worksheet
        
            Set Wks = ActiveSheet
            Set Rng = Wks.Range("A1")
            
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
            
                For Each Cell In Rng
                    Status = GetURLStatus(Cell)
                    Cell.Offset(0, 1) = Status
                Next Cell
            
    End Sub
    Sincerely,
    Leith Ross

  3. #3
    Board Regular
    Join Date
    Oct 2010
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check if url exists

    wow...this is the fastest reply ever..

    Thanks it works wonderfully

  4. #4
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,740
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check if url exists

    Hello congokin,

    You're welcome. Surprisingly, this question comes up frequently. I keep these macros in my library to provide a quick answer when people need them.
    Sincerely,
    Leith Ross

  5. #5
    New Member
    Join Date
    May 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check if url exists

    I know this is a little old, but thought I would point out something.

    By using httpRequest.Open "GET", URL, False you might end up with a very slow macro. In my case I'm using this macro to detect if a image exists in the server. Some of the images could be several Kb. So fo each call the macro was downloading the whole image.
    If you change that like to httpRequest.Open "HEAD", URL, False you are basically telling the server to send you back the basic info about the file (the header), which will still give you the 200 or 404 status, file size, etc, but it won't try to download the whole file It makes a much quicker macro, depends on how many rows you are processing and what you are processing you can increase the speed to about 95%. Also, I've add a "DoEvents" inside the for so excel doesn't get frozen.

    Hope this helps someone else... =)

    Cheers
    Luis Dal Bello

    Quote Originally Posted by Leith Ross View Post
    Hello congokin,

    Here are two macros for you. The first, a function, returns the status a given URL and the second will check all the URLs you have in column "A" and place the status in column "B". Copy all this code to a new VBA module in your workbook. You can then call the macro "ValidateURLs" to list the status of the URLs on the Active Sheet.
    Code:
    ' Written: March 15, 2011
    ' Updated: April 29, 2012
    ' Author:  Leith Ross
    ' Summary: Returns the status for a URL along with the Page Source HTML text.
    
    Public PageSource As String
    Public httpRequest As Object
    
    Function GetURLStatus(ByVal URL As String, Optional AllowRedirects As Boolean)
    
        Const WinHttpRequestOption_EnableRedirects = 6
      
      
            If httpRequest Is Nothing Then
                On Error Resume Next
                    Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
                    If httpRequest Is Nothing Then
                        Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5")
                    End If
                Err.Clear
                On Error GoTo 0
            End If
    
            ' Control if the URL being queried is allowed to redirect.
              httpRequest.Option(WinHttpRequestOption_EnableRedirects) = AllowRedirects
    
            ' Clear any pervious web page source information
              PageSource = ""
        
            ' Add protocol if missing
              If InStr(1, URL, "://") = 0 Then
                 URL = "http://" & URL
              End If
    
                 ' Launch the HTTP httpRequest synchronously
                   On Error Resume Next
                      httpRequest.Open "GET", URL, False
                      If Err.Number <> 0 Then
                       ' Handle connection errors
                         GetURLStatus = Err.Description
                         Err.Clear
                         Exit Function
                      End If
                   On Error GoTo 0
               
                 ' Send the http httpRequest for server status
                   On Error Resume Next
                      httpRequest.Send
                      httpRequest.WaitForResponse
                      If Err.Number <> 0 Then
                       ' Handle server errors
                         PageSource = "Error"
                         GetURLStatus = Err.Description
                         Err.Clear
                      Else
                       ' Show HTTP response info
                         GetURLStatus = httpRequest.Status & " - " & httpRequest.StatusText
                       ' Save the web page text
                         PageSource = httpRequest.ResponseText
                      End If
                   On Error GoTo 0
                
    End Function
    
    Sub ValidateURLs()
    
        Dim Cell As Range
        Dim Rng As Range
        Dim RngEnd As Range
        Dim Status As String
        Dim Wks As Worksheet
        
            Set Wks = ActiveSheet
            Set Rng = Wks.Range("A1")
            
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
            
                For Each Cell In Rng
                    Status = GetURLStatus(Cell)
                    Cell.Offset(0, 1) = Status
                Next Cell
            
    End Sub

  6. #6
    New Member
    Join Date
    Jul 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check if url exists

    Hello All,

    Any idea how to run this on Mac OS X Excel?
    Thanks much for help!

    Sam

  7. #7
    New Member
    Join Date
    May 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check if url exists

    You can't. There is no way to run a macro on excel for Mac.

    Quote Originally Posted by samsam123 View Post
    Hello All,

    Any idea how to run this on Mac OS X Excel?
    Thanks much for help!

    Sam

  8. #8
    New Member
    Join Date
    Sep 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check if url exists

    Hi, I have to run macro to check if the image file exists in website However, when the image file not there, the site would redirect me to another image, "imagenotfound.png". I've tried using your code to set redirect as FALSE, but the output seems not work and prompted meURL exist. Pls help further. Thx.

  9. #9
    New Member
    Join Date
    Sep 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check if url exists

    Not Sure if anyone could help further, I am a bit rush to get it fixed. Thx.

  10. #10
    New Member
    Join Date
    Feb 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check if url exists

    Hello All,

    I used Leith's code and followed his instructions. However, for all the urls in my "A" column, I am getting the following message "The server name or address could not be resolved". I am using MS Excel 2013. I plan to use this code for checking websites internal to my company. However, for now I used the following urls which I can access without any issue otherwise:
    Google
    Bing

    Can somebody help me figure out what could be going wrong? Any help is much appreciated! Thanks in advance for your time!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •