title: VBA script to to get URLs status code
body: <p>So, I have this Excel macro-enabled sheet with a list of about 500 URLs. (All pages on our corporate intranet.)</p>
  
  <p>Now we want to check how many URLs are working fine with the use of URL status code</p>
  
  <p>.</p>
  
  <p>However, there is some good news: The redirect script does not run from within Excel. If you click a bad link inside Excel, the redirect script does not execute and the HTTP response is reported back to Excel. I believe Excel should be able to identify the correct HTTP response code (404) - or at least whether the link was valid or not.</p>
  
  <p>Which brings me to my question:</p>
  
  <p>I have passed the Winhttp request object to send HTTP POST request and applied a request.status method to a getting status of the URLs.</p>
  
  <p>I have got the correct output but I found some of the URLs are working fine but it shows the 404 status in the macro.</p>
  
  <p>Ex:<a href="http://www.xroadscap.com" rel="nofollow noreferrer">http://www.xroadscap.com</a></p>
  
  <p>If anyone if familiar enough with VBA to suggest a solution to this problem, I would be eternally grateful!</p>
  
  <p>Here is my VBA Script.</p>
  
  <pre><code>Sub Macro1()
  
  Dim source As Range, req As Object, url$
  
  Dim rc As Integer
  
  Set req = CreateObject("Msxml2.ServerXMLHTTP.6.0")
   Sheets("Sheet1").Select
  
    Set source = Range("A1:A1000")
    source.Columns(2).Clear
  
  For i = 1 To source.Rows.Count
  
      url = source.Cells(i, 1)
  
  
      On Error Resume Next
  
      req.Open "HEAD", url, False
  
     req.SetRequestHeader "Accept", "image/webp,image/*,*/*;q=0.8"
  
      req.SetRequestHeader "Accept-Language", "en-GB,en-US;q=0.8,en;q=0.6"
  
      req.SetRequestHeader "Accept-Encoding", "gzip, deflate"
  
      req.SetRequestHeader "Cache-Control", "no-cache"
  
      req.SetRequestHeader "Content-Type", "text/xml; charset=utf-8"
  
      req.SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.111 Safari/537.36"
  
   req.Send
  
  source.Cells(i, 2) = req.Status
  
  
  
  
  
  On Error GoTo 0
    Next
  
  
  
  
    MsgBox "Finished!"
  End Sub
  </code></pre>