title: WinHttpRequest in VBA only works if preceded by a Browser call
body: <p>The following URL returns an XML with USD exchange rate:</p>
  
  <pre><code>http://www.boi.org.il/currency.xml?curr=01
  </code></pre>
  
  <p>I need to call and extract (by parsing the result) the returned rate from Excel VBA.</p>
  
  <p>When called in VBA after invoked manually in browser - it works fine. However, after a certain amount of time, it is not working anymore from VBA, unless called manually again in the browser first. Instead, it returns this string as a result: </p>
  
  <pre><code>&lt;html&gt;&lt;body&gt;&lt;script&gt;document.cookie='ddddddd=978a2f9dddddddd_978a2f9d; path=/';window.location.href=window.location.href;&lt;/script&gt;&lt;/body&gt;&lt;/html&gt;
  </code></pre>
  
  <p>The VBA I'm using to call is this:</p>
  
  <pre><code>Function GetExchangeRate(ByVal curr As Integer, Optional ByVal exDate As Date = 0) As Single
  
      Dim strCurrCode As String
      Dim strExDate As String
      Dim strDateParamURL As String
      Dim intStartPos As Integer
      Dim intEndPos As Integer
      Dim sngRate As Single
  
      sngRate = -1
  
      On Error GoTo FailedCurr
  
      strDateParamURL = ""
  
      strCurrCode = Format(curr, "00")
      If (exDate &gt; 0) Then
          strExDate = Format(exDate, "yyyymmdd")
          strDateParamURL = "&amp;rdate=" &amp; strExDate
      End If
  
  
      Dim result As String
      Dim myURL As String
      Dim winHttpReq As Object
  
      Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
  
      myURL = "http://www.boi.org.il/currency.xml"
      myURL = myURL &amp; "?curr=" &amp; strCurrCode &amp; strDateParamURL
  
      winHttpReq.Open "GET", myURL, False
      winHttpReq.Send
  
      result = winHttpReq.responseText
  
      intStartPos = InStr(1, result, "&lt;RATE&gt;") + 6
      intEndPos = InStr(1, result, "&lt;/RATE&gt;") - 1
  
      If (intEndPos &gt; 10) Then
          sngRate = CSng(Mid(result, intStartPos, intEndPos - intStartPos + 1))
      End If
  CloseSub:
      GetExchangeRate = sngRate
      Exit Function
  FailedCurr:
      GoTo CloseSub
  End Function
  </code></pre>
  
  <p><strong>EDIT:</strong>
  I tried this using the MSXML2 object - exactly the same behavior! works only after a browser activation. This is the XML code:</p>
  
  <pre><code>Function GetExchangeRateXML(ByVal curr As Integer, Optional ByVal exDate As Date = 0) As Single
  
      Dim strDateParamURL As String
      Dim intStartPos As Integer
      Dim intEndPos As Integer
      Dim sngRate As Single
      Dim myURL As String
  
      sngRate = -1
  
      ''On Error GoTo FailedCurr
  
      If (curr = 0) Then
          sngRate = 1
          GoTo CloseSub
      End If
  
      strDateParamURL = ""
  
      strCurrCode = Format(curr, "00")
      If (exDate &gt; 0) Then
          strExDate = Format(exDate, "yyyymmdd")
          strDateParamURL = "&amp;rdate=" &amp; strExDate
      End If
  
  
      myURL = "http://www.boi.org.il/currency.xml"
      myURL = myURL &amp; "?curr=" &amp; strCurrCode &amp; strDateParamURL
  
      Dim oXMLFile As Object
      Dim RateNode As Object
  
      Set oXMLFile = CreateObject("MSXML2.DOMDocument")
      oXMLFile.async = False
      oXMLFile.validateOnParse = False
      oXMLFile.Load (myURL)
  
      Set RateNode = oXMLFile.SelectNodes("//CURRENCIES/CURRENCY[0]/RATE")
  
  
      Debug.Print (RateNode(0).Text)
  
  CloseSub:
      GetExchangeRateXML = CSng(RateNode(0).Text)
      Set RateNode = Nothing
      Set oXMLFile = Nothing
  
      Exit Function
  FailedCurr:
      GoTo CloseSub
  End Function
  </code></pre>
  
  <p>Any ideas why this is not working initially from the VBA function?</p>